• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Multi value in export.


Have some code I have used for a variety of applications to export data into excel, the code is below. The new issue I have is the last item in the export is a checkbox field but it is only bringing over the first selection if both are selected, there are 2 items in the checkbox. What adjustments are needed to handle the multi value situation.

Joe

Sub Click(Source As Button)
      
      Dim session As New NotesSession
      Dim workspace As New NotesUIWorkspace
      Dim uidoc As NotesUIDocument
      Dim db As NotesDatabase
      Dim view As NotesView
      
      Dim Mgrdc As NotesDocumentcollection
      Dim MgrDoc As notesDocument
      
      Dim Repdc As NotesDocumentCollection    
      Dim RepDoc As NotesDocument
      Dim mgrview As NotesView    
      Dim RepView As NotesView
      Dim txt_Agency As String  
      Dim tmp_NotesName As NotesName
      Dim MgrKey, RepKey, ADKey As String
      Dim ParticipantsItem As NotesItem
      
      Dim xlApp As Variant
      Dim xlSheet As Variant
      Dim i As Integer
      Dim x As Integer
      Set db = session.CurrentDatabase
      Set Mgrdc = db.UnprocessedDocuments
      Set Mgrdoc = Mgrdc.GetFirstDocument
      On Error Resume Next
      
      RepKey = "blackberry_order_form"
      
      Set Repview = db.GetView( "(bb_export_all)")  
      Set RepDoc  = Repview.GetDocumentByKey( RepKey )    ' form name must be first colum and sorted
      
      Print "Creating Excel Workbook..."
      Set xlApp = CreateObject("Excel.application")
      Print "Creating Excel Worksheet for BlackBerry Data.."
      xlApp.Workbooks.Add
      Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
      
      xlSheet.Cells(1,1).Value = "Name (Last, First)"     ' change your column headings
      xlSheet.Cells(1,2).Value = "Cost Center"
      xlSheet.Cells(1,3).Value = "Employee#"
      xlSheet.Cells(1,4).Value = "Product(Bundle)"
      xlSheet.Cells(1,5).Value = "Optional Add-Ons"
      
      
      x = 3
      While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following
            If  RepDoc.Form(0) = "blackberry_order_form" Then               
                  
                  xlSheet.Rows("1:1").Select                  
                  xlSheet.Rows(1).WrapText =True
                  xlsheet.Columns(1).Columnwidth = 22
                  xlsheet.Columns(2).Columnwidth = 10
                  xlsheet.Columns(3).Columnwidth = 10
                  xlsheet.Columns(4).Columnwidth = 51
                  xlsheet.Columns(5).Columnwidth = 35
                  
                  
                  
                  xlSheet.Cells(x,1).Value = RepDoc.name_imported
                  xlSheet.Cells(x,2).Value = RepDoc.ccc
                  xlSheet.Cells(x,3).Value = RepDoc.cert_adjusted
                  xlSheet.Cells(x,4).Value = RepDoc.bundle
                  xlSheet.Cells(x,5).Value = RepDoc.addons
                  
                  
                  RecNum = x - 3 ' optional
                  Print "Gathering Data.  Record Number: " + RecNum  ' optional
                  x = x + 1                    
            Else                                    
                  Set RepDoc = Repview.GetLastDocument                  ' While not (RepDoc) is nothing
            End If                  
            Set RepDoc = Repview.GetNextDocument(RepDoc)
      Wend
      
      Print "Data Collection Complete."
      
      Set xlSheet = xlApp.Workbooks(1).Worksheets("Sheet1")
      Set range1 = xlsheet.Range("A1: Z2000")
      Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)
      
      
      Msgbox "Your BlackBerry Report is ready in Excel. Click OK and then open the Excel file flashing near the bottom of your screen.  ", 64, "Excel Export"
      
      xlApp.Visible = True
      xlApp.UserControl = True
End Sub
0
jforget1
Asked:
jforget1
  • 2
  • 2
1 Solution
 
Sjef BosmanGroupware ConsultantCommented:
You didn't supply the name of the field in question. Assuming it is field X, and field X is of type string, you would need a value separator to separate two string values.

Something like:
    xlSheet.Cells(x,5).Value = Join(RepDoc.addons, ";")
0
 
jforget1Author Commented:
Was typing too fast, thought I had it in there, but you got the right one, it was the addons field. I will try now.
0
 
jforget1Author Commented:
That did it perfectly, as always thanks for the super fast help.
0
 
Sjef BosmanGroupware ConsultantCommented:
:) Welcome...
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now