Simple export from Notes view to Excel
|
This LotusScript agent exports all documents from view to Excel. It exports every column separately and is much slower than the advanced Export agent but you have more control over export results than in the advanced version.
Sub Initialize
'Copyright Botstation (www.botstation.com)
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim wks As New NotesUIWorkspace
Dim uiView As NotesUIView
Dim column As NotesViewColumn
Dim doc As NotesDocument
Dim row As Long
Dim counter As Long
Dim colcounter As Integer
Dim filename As String
Dim xlApp As Variant
Dim xlsheet As Variant
Dim xlswb As Variant
Dim xlrange As Variant
Set db=session.currentdatabase
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True 'Excel program is visible (to avoid errors and see what is happening)
Set xlwb=xlApp.Workbooks.Add
Set xlsheet =xlwb.Worksheets(1)
Set uiView = wks.currentview
Set view = db.GetView( uiView.ViewName ) ' get the view currently open in UI
row=1
colcounter=0
Forall c In view.Columns
If c.isIcon<>True Then ' do not include icon columns
If c.Formula<>"""1""" And c.Formula<>"1" Then 'do not include columns which are counting docs (Total)
xlsheet.Cells(row,colcounter+1).Value = c.Title 'set Excel column titles
colcounter=colcounter+1
End If
End If
End Forall
row=2
counter = 0
x=0
Set doc = view.GetFirstDocument
While Not ( doc Is Nothing )
Forall c In view.Columns
If c.isIcon<>True Then ' do not include icon columns
If c.Formula<>"""1""" And c.Formula<>"1" Then 'do not include columns which are counting docs (Total)
currentvalue= doc.ColumnValues(counter)
xlsheet.Cells(row, x+1).Value = currentvalue 'set values to Excel table
x=x+1
End If
End If
counter = counter +1
End Forall
counter = 0
x=0
row=row+1
Set doc = view.GetNextDocument (doc)
Wend
Msgbox "Done"
End Sub
|
|