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