export selected documents to excel

HCL Notes/Domino 8.5 Forum (includes Notes Traveler)

Subject:

This is the code that I found...

when I try to run it, it stops and says it "Could not open the view..."

If I change this line from

Set otherview = db.GetView(currentviewname)

to

Set otherview = db.GetView("vw_Default")

the alias of the view, then the code runs, but nothing happens. Excel doesn't open and no error messages are posted. I've tried to debug it, but the interface never opens. The code just runs. Maybe you can't debug script in an action button?

On Error Resume Next ' < don't like this
Dim s As New notessession
Dim db As notesdatabase
Set db= s.currentdatabase
Dim uiw As New NotesUIWorkspace
Dim otherdoc As NotesDocument
Dim otherview As NotesView
Dim othercol As NotesDocumentCollection

Dim tempdoc As notesdocument

'Work out the current view's name
Dim uiv As notesuiview
Set uiv = uiw.currentview

'if it is R4 then viewalias doesn't work so use
'environment variable stashed in the post open event
If Instr(s.Notesversion, "Release 4") Then
currentviewname = s.getenvironmentstring("CurrentView")
If currentviewname="" Then
Msgbox "Notes R4, code is not set up properly. Contact developer."
End
End If
Call s.setenvironmentvar("CurrentView","")
Elseif uiv.viewalias <> "" Then 'use alias if it isn't blank
currentviewname = uiv.viewalias
Else ' use name
currentviewname = uiv.viewname
End If

'Get the view
Set otherview = db.GetView(currentviewname)
If otherview Is Nothing Then
Messagebox "Could not open the view. """ & currentviewname & """"
Exit Sub
End If

'Check if it is for all documents or only selected
Set othercol = db.unprocesseddocuments
If othercol.count >1 Then 'if more than one doc selected then confirm
resp = Messagebox("Do you want to export only the " & _
"selected " & othercol.count & " documents?", 36, "Selected only?" )
Else
Messagebox "Exporting all rows. (To export only selected " & _
"rows tick those required in the left margin first.)"
End If '6= yes

Dim object As NotesEmbeddedObject
Dim xlApp As Variant
Dim oWorkbook As Variant

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True 'set to visible, this can be moved to the end if you wish

Set oworkbook = xlApp.Workbooks 'handle to Workbook
oworkbook.Add

'Stick out the column headers
hcolmn=1
Forall c In otherview.Columns
xlApp.cells(1,hcolmn) = c.title
hcolmn=hcolmn+1
End Forall

row=2

If resp=6 Then 'selected documents
Dim seldoc As notesdocument
Set seldoc = othercol.GetFirstDocument
While Not seldoc Is Nothing
If resp=6 Then
Set otherdoc = otherview.getnextdocument(seldoc)
If otherdoc Is Nothing Then
Set otherdoc = otherview.getprevdocument(seldoc)
If otherdoc Is Nothing Then
Print " >1 doc should be selected"
End
Else
Set otherdoc = otherview.getnextdocument(otherdoc)
End If
Else 'got next doc
Set otherdoc = otherview.getprevdocument(otherdoc)
End If
End If
For colmn = 0 To Ubound(otherview.Columns)
xlApp.cells(row,colmn+1) = otherdoc.columnvalues(colmn)
Next
row=row+1
Set seldoc = othercol.GetNextDocument(seldoc)
Wend
Else ' all documents
Set otherdoc = otherview.GetFirstDocument
While Not otherdoc Is Nothing
For colmn = 0 To Ubound(otherview.Columns)
xlApp.cells(row,colmn+1) = otherdoc.columnvalues(colmn)
Next
row=row+1
Set otherdoc = otherview.GetNextDocument(otherdoc)
Wend
End If

'this highlights the headings
xlApp.application.Rows("1:1").Select
With xlApp.application.Selection.Font
.bold = True
.ColorIndex = 48
.Name = "Arial"
.Size = 12
End With

'this freezes the panes
xlApp.application.Rows("2:2").Select
xlApp.application.ActiveWindow.FreezePanes = True

'this autofits the columns
xlApp.cells.select
xlApp.selection.Columns.AutoFit
xlApp.application.rows("1:1").Select

Feedback response number WEBB8DRTGR created by ~Kirk Bubtooplopakol on 02/04/2011