Solved

Multi value in export.

Posted on 2006-11-06
4
297 Views
Last Modified: 2013-12-18

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
Comment
Question by:jforget1
  • 2
  • 2
4 Comments
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
ID: 17883927
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
 

Author Comment

by:jforget1
ID: 17884012
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
 

Author Comment

by:jforget1
ID: 17884039
That did it perfectly, as always thanks for the super fast help.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 17884239
:) Welcome...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question