Parent page: Service Functions – Excel (QTP, VBScript)

Answers

Description

Connect to Excel through COM - Open workbook - Loop through worksheets – close and save the workbook.

1. If you know Excel Worksheet name and pretty sure it exists

Set XLHandle =  CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False
Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")
On Error Resume Next
Set objWorksheet = XLBook.Sheets.Item("My")
boolRC = Err.Number <> 0
On Error GoTo 0
If boolRC Then
 Reporter.ReportEvent micFail, "Get sheet", "Failed to retrieve worksheet"
Else
 Reporter.ReportEvent micDone, "Get sheet", "Successfully retrieved object's instance"
End If
XLBook.Save
XLBook.Close
XLHandle.Quit
Set XLBook = Nothing
Set XLHandle = Nothing

2. If you don’t know Excel Worksheet name exactly or not sure if it exists

Set XLHandle =  CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False
Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")
If isNumeric(sSheetName) Then
  intSheetName= CInt(sSheetName)
  If (intSheetName>0) AND (intSheetName<=XLBook.Worksheets.Count) Then
   Set XLSheet = XLBook.Sheets.Item(intSheetName)
  Else
   Set XLSheet = Nothing
  End If
Else
  Set XLSheet = Nothing
  For Iter = 1 To XLBook.Worksheets.Count
   If XLBook.Worksheets(Iter).Name = sSheetName Then
   'Note. You can use InStr or RegEx.Test functions if you don't have exact string
    Set XLSheet = XLBook.Worksheets(Iter)
    Exit For
   End If
  Next
Enf If
If XLSheet is Nothing Then
   Reporter.ReportEvent micFail, "Get sheet", "Failed to retrieve worksheet"
Else
   Reporter.ReportEvent micDonel, "Get sheet", "Successfully retrieved object's instance"
End If
XLBook.Save
XLBook.Close
XLHandle.Quit
Set XLSheet = Nothing
Set XLBook = Nothing
Set XLHandle = Nothing