Wednesday, January 20, 2010

Sample Lotusscript code to export data from a view to Microsoft Excel document

The following sample code exports all selected documents from a view into an excel file. I am not sure if it works under excel 2003, but i think it's worth a try...

Make sure that you define your own field list before starting the export (*),(**) and (***).
 
'code fragment starts here....
On Error Goto errlbl

Dim count As Double
count = 0
Dim i As Double
i = 1
Dim j As Double
j = 1
Dim lngRow As Double
lngRow = 1
Dim lngCol As Double
lngCol = 1
Dim varApp As Variant

Dim session As New NotesSession
Dim dbCurr As NotesDatabase
Set dbCurr = session.CurrentDatabase

Dim coll As NotesDocumentCollection
Set coll = dbCurr.UnprocessedDocuments

If coll.Count <= 0 Then
Msgbox "No documents found!"
Exit Sub
End If

If coll.Count > 65535 Then
Msgbox ( "Too many documents found!" )
Exit Sub
End If

Dim antwort As Integer
antwort = Msgbox ( "You are going to export " & coll.Count & " documents to excel. Continue?" , 4 , "Start export?" )

If antwort <> 6 Then
Exit Sub
End If

Set varApp = CreateObject("Excel.Application")
If varApp Is Nothing Then
Msgbox "An excel-object could not be created." , 16 , "Error"
Exit Sub
End If

varApp.Workbooks.Add
varApp.ReferenceStyle = 2
Set varSheet = varApp.Workbooks(1).Worksheets(1)
varSheet.Name = "Title of Excel sheet"

'*

Dim liste (0 To 2) As String

liste ( 1 ) = "Fieldname 1"
liste ( 2 ) = "Fieldname 2"

Dim element As Integer

'**

For element = 1 To 2

lngRow = 1
lngCol = i
varSheet.Cells(lngRow,lngCol).Value = liste ( element )

i = i +1

Next

i = 2

Dim feldname As String
Dim feldwert As String
Dim docCurr As NotesDocument
Set docCurr = coll.GetFirstDocument
While Not docCurr Is Nothing

'***

For element = 1 To 2

lngRow = i
lngCol = j

feldname = liste ( element )
feldwert = docCurr.GetItemValue ( feldname ) ( 0 )
varSheet.Cells(lngRow,lngCol).Value = feldwert

j = j +1

Next

count = count + 1
Print count & " documents = "& Cint ( count / coll.Count * 100 ) & " % processed"
Set docCurr = coll.GetNextDocument ( docCurr )

i = i +1
j = 1

Wend

Ende:
varApp.Visible = True
varApp.Rows("1:1").Select
varApp.Selection.Font.Bold = True
varApp.Selection.Font.Underline = True
varApp.Selection.Columns.AutoFit
varApp.Range("A2").Select
varApp.ActiveWindow.FreezePanes = True
varApp.Cells.Select
varApp.Cells.EntireColumn.AutoFit

varApp.Range("A2").Select

Exit Sub

errlbl:

Print ( "Error in ExportData: " & Error & " in line " & Erl & ", fieldname: " & liste ( element ) )

Exit Sub

'Hope this helps!

2 comments:

  1. Thanks to blog this code,It is very useful to developers to know how to Export the data to Excel.
    I think it will be very useful if this code contain answer for how to give names to worksheets at runtime.

    ReplyDelete
  2. @Syed: Naming a work sheet is kind of simple. Look at the following code,

    Set varSheet = varApp.Workbooks(1).Worksheets(1)

    Now to name the worksheet you may have to do the following,

    varSheet.Name="your Sheet Name"

    Hope this helps... :) and your comments are always welcome.. feel free to ask any such technical question and I will reply to them ASAP...

    ReplyDelete