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?
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.