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

Answers

Description

Connect to Excel through COM - Open workbook - Open worksheet - Loop through data range – close and save the workbook.

Note. In the example below script finds empty cells and assigns zero to them.

Make sure you targeted the worksheet that you need. You can find it by index or by name.

Set XLHandle =  CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False
Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")
Set objUsedRange = XLBook.Worksheets.Item(1).UsedRange
For RowIter = 1 To objUsedRange.Rows.Count
 For ColIter = 1 To objUsedRange.Columns.Count
  If Trim(objUsedRange.Cells(RowIter, ColIter)) = "" Then
   objUsedRange.Cells(RowIter, ColIter) = 0
  End If
 Next
Next
XLBook.Save
XLBook.Close
XLHandle.Quit
Set XLBook = Nothing
Set XLHandle = Nothing