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