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