Users would like to export only selected documents in a view to Excel.
I tried to create a view action button with the following code but when I try to run it I get “Object Variable not set”
Sub Click(Source As Button)
Dim s As New notessession
Dim db As notesdatabase
Dim view As notesview
Dim dc As notesdocumentcollection
Dim doc As notesdocument
Dim vcols As Variant
Dim Uvcols As Integer
Set db = s.currentdatabase
Set dc = db.unprocesseddocuments
Set view = db.getview("PC\Lease Requests\Master")
Uvcols = Ubound(view.Columns)
Dim xlApp As Variant
Dim xlsheet As Variant
Set xlApp = CreateObject("Excel.Application")
xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
xlApp.Visible = True
xlApp.Workbooks.Add
xlApp.ReferenceStyle = 2
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlsheet.Name = "PC Lease "
Dim rows As Integer
rows = 1
Dim cols As Integer
cols = 1
Dim maxcols As Integer
For x=0 To Ubound(view.Columns)
If view.Columns(x).IsHidden = False Then
If view.Columns(x).Title <> "" Then
xlsheet.Cells(rows,cols).Value = view.Columns(x).Title
cols = cols + 1
End If
End If
Next
maxcols = cols - 1
Set doc = dc.getfirstdocument
Dim fieldname As String
Dim fitem As notesitem
rows=2
cols=1
Do While Not (doc Is Nothing)
For x=0 To Ubound(view.Columns)
If view.Columns(x).IsHidden = False Then
If view.Columns(x).Title <> "" Then
fieldname = view.Columns(x).Itemname
Set fitem = doc.getFirstItem(fieldname)
xlsheet.Cells(rows,cols).Value = fitem.Text
cols = cols+1
End If
End If
Next
rows = rows+1
cols =1
Set doc = dc.getnextdocument(doc)
Loop
xlApp.Rows("1:1").Select
xlApp.Selection.Font.Bold = True
xlApp.Range(xlsheet.Cells(1,1), xlsheet.Cells(rows,maxcols)).Select
xlApp.Selection.Font.Name = "Arial"
xlApp.Selection.Font.Size = 9
xlApp.Selection.Columns.AutoFit
With xlApp.Worksheets(1)
.PageSetup.Orientation = 2
.PageSetup.centerheader = "Report - Confidential"
.Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
.Pagesetup.CenterFooter = ""
End With
xlApp.ReferenceStyle = 1
xlApp.Range("A1").Select
End Sub
Any help would be greatly appreciated. Thanks!