Very fast import from MS Excel to Lotus Notes using LotusScript

Original location: Fast import from Excel to Notes

This LotusScript agent has very high performance. It can read 10000 cells in just 1 second, compared to reading cell-by-cell which would take several minutes.



Sub Initialize
'This agent imports records from excel to Notes. It uses Range method which makes it very fast.
'Copyright Botstation (www.botstation.com)

Dim session As New NotesSession
Dim db As NotesDatabase
Dim xlApp As Variant, xlsheet As Variant, xlwb As Variant, xlrange As Variant
Dim x As Integer
Dim y As Integer

Dim filename As String, currentvalue As String
Dim rowsatonce As Integer, cnt As Integer
Dim DataArray

Set db=session.CurrentDatabase
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True 'Excel program is visible to see what is happening

Set xlwb=xlApp.Workbooks.Open("C:\test.xls")
Set xlsheet =xlwb.Worksheets(1)

actRows=1000 'process 1000 rows
actColumns=5 'process 5 columns

Redim DataArray(actRows, actColumns) As String
DataArray=xlsheet.Range("A1").Resize(actRows, actColumns).Value 'get sheet area of 5*1000 size

For x=1 To actRows
For y=1 To actColumns
currentvalue=Cstr(DataArray(x,y))
tmp=tmp+" "+currentvalue
Next
tmp=tmp+" "+Chr(10)
Next

Msgbox tmp

End Sub



Implementation of putting array values into notes documents is left as an exercise to the reader :)