Stuck with Unprocessed document in Lotus notes.

Question

Hi Folks, I am using unprocessed document so that it will run on selected document only , but after selection its looking for column values in a view to export document to excel . I am using the below code its working fine but my first document is always displaying in last after all other exported documents in excel sheet . Any idea what can be done with the code so that Exported document display perfectly in order as in the view.

Look at the below code which I used for exporting –

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:J1").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
 Dim i As Integer
 If resp=6 Then 'selected documents
  Set doc = doccoll.GetFirstDocument   
  While Not doc Is Nothing
   If resp=6 Then  

    'row% = offset% + 2
    If y="" Then
     row% = row% + 2
    Else 
     row%=row%+y+1
    End If
    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
     ''If col%=10 Then   ''''''''
     columnVal=Fulltrim(colval)
     For y = 0 To Ubound(columnVal)
      offset% = row% + y +lastOffset%   
      'offset% = row% + y  
      oWorkSheet.Cells(offset%,col%).value = columnVal(y) 
      'i=offset%
     Next
    Else
     oWorkSheet.Cells(row%, col%).value = colval 
     'offset% = offset% + 1
    End If
    '''' oWorkSheet.Cells(row%, col%).value = colval '''''
    '''''''End If''''''''
   End Forall

   Set doc = doccoll.GetNextDocument(doc)       
  Wend
 End if

Answer 1

I think you can remove this whole section:

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

I'm not sure what you were going for, but it appears you were testing that more than one document is selected. You can do that easily by testing the doccoll.count property:

If doccoll.count <=1 Then  'etc...

Then just change your Forall statement to work on doc instead of otherdoc:

Forall colval In doc.ColumnValues ...

Answer 2

I also don't understand completely what this loop is for. UnprocessedDocuments is just a NotesDocumentsCollection. You navigate through it using GetFirstDocument/GetNextDocument in a while loop or similar. Warning indicating that no documents were selected should come in first place, not in the loop.

Set db = session.CurrentDatabase
Set doccoll = db.UnprocessedDocuments
If doccoll.Count = 0 Then
  Print " >1 doc should be selected"
  Exit Sub
End If

after that you do your Excel initialization and loop

Set doc = doccoll.GetFirstDocument
While Not doc Is Nothing
  ' enter code here'

  Set doc = doccoll.GetNextDocument(doc)
Wend

Regarding columns I would double check. NotesDocument is just an object that should behave the same way always. But Notes is Notes... I will check also and give you a feedback.