Lotus Script Action to export

Question

Please take a loot at this code:

I would like to export the my current view from Lotus Notes to Excel. However I am not able to. I have never worked with LotusScript before

Sub Click(Source As Button)

    Dim session As New NotesSession
    Dim wks As New NotesUIWorkspace 
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim uiView As NotesUIView
    Dim doc As NotesDocument
    Dim column As NotesViewColumn 

    Dim row As Long,colcounter As Long,arrcnt As Long,arrcounter As Long, x As Long
    Dim filename As String, currentvalue As String
    Dim rowsatonce As Integer,cn As Integer 
    Dim xlApp As Variant, xlsheet As Variant,xlwb As Variant, xlrange As Variant, tempval As Variant
    Dim DataArray
    Dim VColumns List As String

    Redim DataArray(0, 80) As String 
'80 columns is our expected max number of columns in the view. It's dynamically recomputed below to actual (lower) number. Change if the number of columns is larger. 

    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 
    arrcnt=0
    row=1
    colcounter=0
    rowsatonce=20
    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 used for counting docs (Total)
                colcounter=colcounter+1
                DataArray(row-1, colcounter-1) =c.Title
                VColumns(Cstr(cn))=Cstr(cn)
            End If
        End If
        cn=cn+1
    End Forall
    Redim Preserve DataArray(0, colcounter-1) As String
    xlsheet.Range("A1").Resize(1, colcounter).Value = DataArray ' set column names
    Redim DataArray(rowsatonce-1, colcounter-1) As String
    row=2
    x=0
    Set doc = view.GetFirstDocument 
    While Not ( doc Is Nothing ) 
        Forall col In VColumns
            currentvalue=""
            tempval= doc.ColumnValues(Val(col))
            If Isarray(tempval) Then
                Forall v In tempval
                    If currentvalue="" Then
                        currentvalue=v
                    Else
                        currentvalue=currentvalue+","+v
                    End If
                End Forall
            Else
                currentvalue=tempval
            End If
            x=x+1
            DataArray(arrcounter, x-1) =currentvalue 
        End Forall
        x=0 
        row=row+1
        arrcounter=arrcounter+1
        If arrcounter/rowsatonce=arrcounter\rowsatonce And arrcounter<>0 Then 
            xlsheet.Range("A"+Cstr(arrcnt*rowsatonce+2)).Resize(rowsatonce, colcounter).Value = DataArray
            arrcnt=arrcnt+1
            arrcounter=0
            Redim DataArray(rowsatonce-1, colcounter-1) As String
        End If
        Set doc = view.GetNextDocument (doc)
    Wend 

    If arrcounter/rowsatonce<>arrcounter\rowsatonce And arrcounter>0 Then
' Redim Preserve DataArray(arrcounter, colcounter-1) As String 
        xlsheet.Range("A"+Cstr(arrcnt*rowsatonce+2)).Resize(arrcounter, colcounter).Value = DataArray
    End If
    Msgbox "Done"


End Sub

I am trying to create an action in Lotus Notes to export my current view into excel. It is giving me the error: "Object Variable not Set"

Answer 1

You might want to use my export script: from here

I haven't changed it for over 10 years, but it still works. As of Notes8 I prefer using the 'View Copy as Table' feature in Notes...

Regards

Daniel

Answer 2

I suggest that you add at least a basic error handling. add at the beginning a "on error goto handler"

and at the bottom something like

exit_point:
   exit sub
handler:
  print error$ & " in line " & cstr(erl)
resume exit_point