Notes Data to Excel: the fast way
Posted by Theo Heselmans on June 21st, 2007
I do a lot of Excel reporting using Notes data, lately.
This is pretty easy stuff, because you just have to create an Excel object and put a value into a cell.
Look at the following straightforward LotusScript code, which, put into an agent, loops over the current selected documents, and puts the form-name, creation, and modification date into an Excel spreadsheet:
Sub Initialize
Dim session As New notessession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim i As Integer
Dim ObjExcel As Variant 'Excel Object
Set db=session.CurrentDatabase
Set dc=db.UnprocessedDocuments
If dc.Count=0 Then Exit Sub 'no documents selected in the view
Set ObjExcel = CreateObject("Excel.Application")
ObjExcel.Visible = True 'make sure the Excel sheet is visible
Call ObjExcel.Workbooks.add 'to create new worksheet
Set doc=dc.GetFirstDocument
i=1
While Not (doc Is Nothing)
ObjExcel.Cells(i,1).value=doc.form(0)
ObjExcel.Cells(i,2).value=doc.Created
ObjExcel.Cells(i,3).value=doc.LastModified
Set doc=dc.GetNextDocument(doc)
i=i+1
Wend
End Sub
Dim session As New notessession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim i As Integer
Dim ObjExcel As Variant 'Excel Object
Set db=session.CurrentDatabase
Set dc=db.UnprocessedDocuments
If dc.Count=0 Then Exit Sub 'no documents selected in the view
Set ObjExcel = CreateObject("Excel.Application")
ObjExcel.Visible = True 'make sure the Excel sheet is visible
Call ObjExcel.Workbooks.add 'to create new worksheet
Set doc=dc.GetFirstDocument
i=1
While Not (doc Is Nothing)
ObjExcel.Cells(i,1).value=doc.form(0)
ObjExcel.Cells(i,2).value=doc.Created
ObjExcel.Cells(i,3).value=doc.LastModified
Set doc=dc.GetNextDocument(doc)
i=i+1
Wend
End Sub
Now this works pretty good, but it's very slow, as it puts the data into each cell one by one.
If you got a report to make that is e.g. 1000 lines by 10 columns, that's 10.000 cells to fill one by one !
Here's what I found out: If you put all your values into an array, you can put the data into Excel all at once.
Replace the bold code at the top with the following:
Dim Report() As Variant
Redim Report(dc.count,2)
Set doc=dc.GetFirstDocument
i=0
While Not (doc Is Nothing)
Report(i,0)=doc.form(0)
Report(i,1)=doc.Created
Report(i,2)=doc.LastModified
Set doc=dc.GetNextDocument(doc)
i=i+1
Wend
ObjExcel.Range(ObjExcel.Cells(1, 1),ObjExcel.Cells(dc.count, 3)).Value=Report
Redim Report(dc.count,2)
Set doc=dc.GetFirstDocument
i=0
While Not (doc Is Nothing)
Report(i,0)=doc.form(0)
Report(i,1)=doc.Created
Report(i,2)=doc.LastModified
Set doc=dc.GetNextDocument(doc)
i=i+1
Wend
ObjExcel.Range(ObjExcel.Cells(1, 1),ObjExcel.Cells(dc.count, 3)).Value=Report
Very easy to do, and extremely fast.
The key element here is the Range-function, in this case with 2 arguments: the first cell (1,1) and the last cell (dc.count by 3 columns).
These are the basics.
In an upcoming post, I'll talk about how I use this technique to enter Notes data in a predefined Excel template (where all formatting, pivots, charts,... are already in place).
Category: Domino/Notes Microsoft Excel | 
Technorati: Show-n-Tell Thursday, SnTT
Comments (10)
great tipp!! Looking forward to reading the next tipp on Notes and Excel :-)
I'm going to be looking at putting this lesson and that from your upcoming post into the SuperNTF ({ Link } ) excel export routine. Cool stuff!
Here's a simple way to export a view to excel.
{ Link }
Thanks! Great tip!
That's very usefull. Now I can focus on the performance creating charts.
Great Tip. Thanks a lot
Theo,
Very useful tip, much faster than filling the cells one by one.
But I experienced problems when filling the spreadsheet range with the Report array: I tried this with a report of 1600 x 12 cells, and I got an OLE automation object error. Apparently, the spreadsheet was complete until record 1522... The remainder was missing. I guess the chunck of data was too be to place it at once in an excel range.
Then I used this work around: I collect the Report data from Notes per 1000 records (view entries). Each time my Report array is filled with another 1000 entries, I place them in the appropriate excel range. This works fine!
iCountTotal = myNotesDocumentCollection.Count
'define how many chuncks of max 1000 entries I will have:
iNrOfGroups = iCountTotal\1000 + 1 'integer division
'iteration for each chunck:
For iGroup = 1 To iNrOfGroups
If iGroup < iNrOfGroups Then
iCount = 1000
Else
iCount = iCountTotal - ((iGroup-1)*1000)
End If
Redim vReport(1 To iCount,2 To 12)
'fill report with column values
....
'define starting row for the next chunck in excel:
iBase = ((iGroup-1)*1000)
'place the chunck in excel:
excelSheet.Range(excelSheet.Cells(iBase+7, 2),excelSheet.Cells(iBase + iCount+6, 12)).Value=vReport
Next
Love this method !!!
How can I access the other worksheet tabs using this method ?
I usually use:
Set excelWs = ObjExcel.Workbooks(1).Worksheets(2)
and to go back to Worksheet 1:
Set excelWs = ObjExcel.Workbooks(1).Worksheets(1)
@Ice Cold Bier
e.g. to go to the 'Data' sheet:
objExcel.Sheets("Data").Select
Thank you. Brilliant bit of code. I've reduced an export of 80 fields in 60 documents from 2.5 minutes to 7 seconds!