Monday, April 24, 2006

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 (

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
tmp=tmp+" "+currentvalue
tmp=tmp+" "+Chr(10)

Msgbox tmp

End Sub

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


Bertil Reiling said...

I recently used this code to import an Axcel worksheet into Lotus Notes. Indeed it is very fast.

I just modified the code a bit by reading the usedrange object so I know in front howmany rows and columns there are.

lngRows = xlSheet.usedRange.Rows.Count
lngCols = xlSheet.usedRange.Columns.Count

Thx for the code!

Thelay said...

Hi, I am using it to import from web. But it doesnt work. Pls help.