Export to Excel, Lotus notes domino

Question

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

score 1 · Answer 1 · edited May 23 '17 at 12:04

1

I see you're using Excel automation. Excel automation is cumbersome at times.

I'd try NPOI for Excel XLS files. Take a look at it. Really straightforward to work with:

Create Excel (.XLS and .XLSX) file from C#

edited May 23 '17 at 12:04

Community

1
1

answered Oct 25 '10 at 14:47

Leniel Maccaferri

100,159
46
371
480

score 1 · Answer 2 · answered Jul 27 '11 at 14:07

There is something very wrong with the code you have uploaded. You must have removed or added an If loop because the first If loop closes before you close the While loop it contains. That being said, this should work, although I haven't tested it.

Option Public
Option Declare

Sub Initialize
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim doccoll As NotesDocumentCollection
    Dim view As NotesView 
    Dim doc As NotesDocument
    Dim resp As Integer, row As Integer, offset As Integer, nextrow As Integer, col As Integer
    Dim oExcel As Variant
    Dim oWorkbook As Variant
    Dim oWorkSheet As Variant

    On Error GoTo olecleanup

    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

    offset% = 0
    nextrow% = 3

    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
        If doccoll.count = 1 Then
            Print " >1 doc should be selected"
        End If
    Else
        Set doc =view.GetFirstDocument
    End if

    While Not doc Is Nothing
        row% = nextrow%
        col% = 0 'Reset the Columns
        nextrow% = row% + 1

        ForAll colval In doc.ColumnValues
            col% = col% + 1
            If IsArray(colval) Then
                offset% = row%
                ForAll cv In colval
                    If CStr(cv) <> "" Then
                        oWorkSheet.Cells(offset%,col%).value = cv
                        offset% = offset% + 1
                    End If
                End ForAll
                If nextrow% < offset% Then nextrow% = offset% 
            Else
                oWorkSheet.Cells(row%, col%).value = colval  
            End If
        End ForAll

        If resp=6 Then 'selected documents
            Set doc = doccoll.Getnextdocument(doc)
        Else
            Set doc =view.Getnextdocument(doc)
        End If
    Wend

    oExcel.activeworkbook.close
    oExcel.quit
    Set oExcel = Nothing

Finish :
    Print "Done"
    Exit Sub

olecleanup :
'   Call LogError() 'Enable to use OpenLog
    If Not(IsEmpty(oExcel)) Then
        oExcel.activeworkbook.close
        oExcel.quit
        Set oExcel = Nothing
    End If
    Resume Finish
End Sub

score 0 · Answer 3 · edited Dec 04 '11 at 05:18

0

Uh, this code definitely needs to be more readable, I bet there's a simpler way to do what you want.
OK, can you explain what do you use "CRMOpenIssue" view for?

I suggest you forget about number of lines each document is represented by in your view and use document fields as your data source, instead of data displayed directly in the view columns.

edited Dec 04 '11 at 05:18

Brock Adams

90,639
22
233
295

answered Mar 05 '11 at 18:12

Marko Bonaci

5,622
2
34
55

1

I applaud what you are trying to do, but this would be better as a comment, not an answer. – ire_and_curses Mar 18 '11 at 02:21

Export to Excel, Lotus notes domino

3 Answers3