Microsoft Excel Operations

1) Create an Excel File and write into a cell :

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.name = "Subrat"
objSheet.Cells(1,1) = "Column A"
objExcel.ActiveWorkbook.SaveAs "E:\TestXL.xls"
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

2) Overwrite an Existing Excel File by suppressing the alerts :


Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.name = "Subrat"
objSheet.Cells(1,1) = "Column B"
objExcel.Visible=True
objExcel.DisplayAlerts=False
objExcel.ActiveWorkbook.SaveAs "E:\TestXL.xls"
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

3) Open an Existing Excel File and write values to it in the following format :

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible=True
objExcel.DisplayAlerts=False
Set objWorkBook=objExcel.Workbooks.open( "E:\TestXL.xls")
Set objSheet= objWorkBook.Worksheets(1)
objSheet.Cells(1,1).value="ColumnA"
objSheet.Cells(1,2).value="ColumnB"
objSheet.Cells(1,3).value="ColumnC" 

For i= 0 to 2
     For j= 0 to 2
         objSheet.Cells(i+2,j+1).value=j+1
     Next
Next

objWorkBook.SaveAs "E:\TestXL.xls"
objWorkBook.Close
objExcel.Quit

VB Script And File Operations

This page contains the link to all the the file operations using VB Script and QTP

1) Microsoft Word File Operations

2) Microsoft Excel File Operations

3) PDF File Operations

4) Microsoft Outlook Operations

5) Text File Operations

Microsoft Word Operations

1 ) Check the existence of  a Microsoft Word file and if the file is present delete it and add a new file....

' Declare the variables

Dim objFso,objMyFile,strFileName,objWord,objDoc,objSelection

  strFileName="Subrat.doc"
 
  'Create an object of class FileSystemObject
   
    Set objFso = CreateObject("Scripting.FileSystemObject")
 
  'Check if the folder structure exists, if not, create it

    Dim sFolder : sFolder = "c:\MyFile"
    If (objFso.FolderExists(sFolder)) Then
        'do nothing
   Else
        Set objFolder= objFso.CreateFolder(sFolder)
   End If
  
    Dim FileName : FileName = "c:\MyFile\"&strFileName
   
    If objFso.FileExists(FileName) Then
            Set objMyFile = objFso.GetFile(FileName)
            On error resume next
                objMyFile.Delete
                If err.number <> 0 Then
                    LogWarn "Could not delete the file : >>> ", "c:\MyFile\"&strFileName
                    err.clear
                End If
    End If
       
    'Add a ms word document
 
  Set objWord = CreateObject("Word.Application")
  Set objDoc = objWord.documents.add
  Set objSelection = objWord.selection
  objSelection.typetext "My new document"
  objDoc.saveas(FileName)
  objWord.Quit
 
  'Clean up
 
  set objWord = nothing
  Set objDoc= nothing
  Set objSelection = nothing
  Set objFso= nothing
  Set objMyFile= nothing