0

If i loop through 10 documents and fill an excel sheet with data from that documents then sometimes the export is slow and sometimes it is fast. How is this possible, this is how I export data:

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = True
Call objExcel.Application.Workbooks.Open(CorDos.CorBestandsnaam)
Set xlSheet = objExcel.Application.ActiveWorkbook.Activesheet

and then it fills the cells..

The documents are in a database which is on another server, this server also sometimes has some issues with I/O could that also be the problem?

Tode
  • 11,795
  • 18
  • 34
Nuri Ensing
  • 1,899
  • 1
  • 19
  • 42

2 Answers2

1

This is NOT a problem of Lotus Notes but a typical excel- automation problem. Just search for "excel vba slow", and you will find tons of articles how to make this fast again. The fastest way to write something to excel is: create a 2 dimensional array in LotusScript and assign the document values to that array. THEN write the whole array at once. This looks like this:

...
Set dc = db.UnprocessedDocuments
...
Redim varArray( dc.Count - 1, NumberOfFields ) as String

Set doc = dc.getFirstDocument()
While not doc is Nothing
  varArray(i , 0 ) = doc.GetitemValue( "FirstField" )(0)
  varArray(i , 1 ) = doc.GetitemValue( "SecondField" )(0)
  varArray(i , 2 ) = doc.GetitemValue( "ThirdField" )(0)
  ....
  i = i + 1
  Set doc = dc.GetNextDocument(doc)
Wend 
...
xlSheet.Range( xlSheet.Cells(1,1), xlSheet.Cells(dc.Count,NumberOfFields) ) = varArray

This code is not tested and partly taken from a response to this excel- vba- question, but should show you the way to go.

Community
  • 1
  • 1
Tode
  • 11,795
  • 18
  • 34
0

I like @Torsten-Link's answer, but it might not be the bottleneck you're experiencing. You might want to consider a pattern where the building of the excel file, or at least collating the data to be inserted into it, is done on the actual machine that has the data and then passes the information on in one terse communication. Much of the issues involved with speed are related to local processing and sifting of remote data.

Also, you really should set Visible to false, then make sure it turns true even if it errors. Having that Visible property to True really slows things down.

JSmart523
  • 2,069
  • 1
  • 7
  • 17