How to find Excel worksheet by name (QTP, VBScript)
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