Hi Folks, I have a view which is displaying 9 lines of information per every document. In this view I have Export to Excel functionality using the below code to Export document to excel. Data isn’t exporting properly for first two documents , for example if I have 7 lines for the first document then it should export 7 lines but its exporting 2 lines only . It is happening for the first 2 documents only, from the 3rd document irrespective of any line no.of information it is exporting to excel perfectly. I tried to modify the code for row% from row% = row%+2 to row% = row%+3 , 4 or 5, but its unnecessary creating rows in the excel sheet its not the dynamic one and looks odd as well. Any idea what should I do so that rows should increase dynamically.
Sub Initialize 'On Error Goto errhandler On Error Resume Next Dim session As New NotesSession Dim db As NotesDatabase Dim doccoll As NotesDocumentCollection Dim view As NotesView Dim doc As NotesDocument Dim otherdoc As NotesDocument
Set db = session.CurrentDatabase Set view = db.GetView("CRMOpenIssue") Set doccoll=db.UnprocessedDocuments
Set oExcel = CreateObject ( "Excel.Application" ) Set oWorkbook = oExcel.Workbooks.Add Set oWorkSheet= oWorkbook.Sheets ( 1 )
oWorkSheet.Cells(1,1).value="Quote# " oWorkSheet.Cells(1,2).value="Quote Line#" oWorkSheet.Cells(1,3).value="Customer - fab" oWorkSheet.Cells(1,4).value="OppNum" oWorkSheet.Cells(1,5).value="OppLine#" oWorkSheet.Cells(1,6).value="Open Issue#" oWorkSheet.Cells(1,7).value="Open Issue" oWorkSheet.Cells(1,8).value="Category" oWorkSheet.Cells(1,9).value="Due date" oWorkSheet.Cells(1,10).value="Owner to resolve issue" oWorkSheet.Cells(1,11).value="Owner/PME Verify when closed" oExcel.Worksheets(1).Range("A1:K1").Font.Bold = True
oExcel.columns("A:A").ColumnWidth=15.00 oExcel.columns("B:B").ColumnWidth=8.00 oExcel.columns("C:C").ColumnWidth=15.00 oExcel.columns("D:D").ColumnWidth=10.00 oExcel.columns("E:E").ColumnWidth=8.00 oExcel.columns("F:F").ColumnWidth=8.00 oExcel.columns("G:G").ColumnWidth=30.00 oExcel.columns("H:H").ColumnWidth=30.00 oExcel.columns("I:I").ColumnWidth=15.00 oExcel.columns("J:J").ColumnWidth=15.00 oExcel.columns("K:K").ColumnWidth=30.00
row% = 1 offset% = 0 lastOffset% = 0
If doccoll.count >1 Then 'if more than one doc selected then confirm resp = Messagebox("Do you want to export only the " & _ "selected " & doccoll.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
oExcel.visible=True
If resp=6 Then 'selected documents
Set doc = doccoll.GetFirstDocument
While Not doc Is Nothing
If resp=6 Then
row% = row%+2
col% = 0 'Reset the Columns
Set otherdoc = view.getnextdocument(doc)
If otherdoc Is Nothing Then
Set otherdoc = view.getprevdocument(doc)
If otherdoc Is Nothing Then
Print " >1 doc should be selected"
End
Else
Set otherdoc = view.getnextdocument(otherdoc)
End If
Else 'got next doc
Set otherdoc = view.getprevdocument(otherdoc)
End If
End If
Forall colval In otherdoc.ColumnValues
col% = col% + 1
If Isarray(colval) Then
columnVal=Fulltrim(colval)
For y = 0 To Ubound(columnVal)
offset% = row% + y +lastOffset%
oWorkSheet.Cells(offset%,col%).value = columnVal(y)
Next
Else
oWorkSheet.Cells(row%, col%).value = colval
End If
End Forall
Set doc = doccoll.GetNextDocument(doc)
Wend
Else 'all documents
Set otherdoc =view.GetFirstDocument
While Not otherdoc Is Nothing
row% = row% + 2
col% = 0 'Reset the Columns
'Loop through all the column entries
'Forall colval In entry.ColumnValues
Forall colval In otherdoc.ColumnValues
col% = col% + 1
If Isarray(colval) Then
columnVal=Fulltrim(colval)
For y = 0 To Ubound(columnVal)
offset% = row% + y +lastOffset%
oWorkSheet.Cells(offset%,col%).value = columnVal(y)
Next
Else
oWorkSheet.Cells(row%, col%).value = colval
End If
End Forall
row%=offset%
Set otherdoc=view.GetNextDocument(otherdoc)
Wend
End If
'errhandler:
Call oExcel.quit()
Set oWorkSheet= Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing
Print "Done"
End Sub