Solved

Multi value in export.

Posted on 2006-11-06
4
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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