OLE LotusScript Class to read and write to Excel

A very simple little LotusScript class which will open an existing spreadsheet and allow you to read and set individual cells, save and close the spreadsheet.

Class ExcelReport
  Private xlApp As Variant
  Private xlSheet1 As Variant
  Private xlSheet2 As Variant
  Private xlSheet3 As Variant
  Private strFilePath As String
  Sub new( xlFilename , isVisable )
  
    Set xlApp = CreateObject(“Excel.application”)
    xlApp.Workbooks.Open xlFilename
    xlApp.Visible = isVisable
    strFilePath = xlFilename
  End Sub
  Function saveFile
    xlApp.ActiveWorkbook.SaveAs( strFilePath )
  End Function
  Function insertData( intSheet As Integer , row As Integer , column As Integer , value As String )
    xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column ).Value = value
  End Function
  Function getData( intSheet As Integer , row As Integer , column As Integer ) As String
    On Error Goto err_hdl
    getData = xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column ).Value
    Exit Function
err_hdl:
    Print Error$ + “in cls: ExcelReport , method: getData , at line ” + Cstr( Erl )
    getData = “”
    Exit Function
  End Function
  Function doQuit
    xlApp.Quit
    Set xlApp = Nothing
  End Function
  Function makeVisable
    xlApp.Visible = True
  End Function
End Class

An example of how this can be used follows:

Sub Initialize
  Dim report as ExcelReport
  Dim iCol As Integer
  Dim iRow as Integer
  Dim strData as String

  Set report = New ExcelReport(“C:\mysheet.xls” , True )

  
  For iRow = 1 To 10
    strData = report.getData(1, iRow , 1)
    If strData = “” Then
      report.insertData(1, iRow, 2, “Null”)
    Else
      report.insertData(1, iRow, 2, “Not Null”)
    End If
  Next iRow
  report.saveFile
  report.doQuit
End Sub

Update Fixed thanks to Matthias.