?
Solved

Multi value in export.

Posted on 2006-11-06
4
Medium Priority
?
308 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 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

765 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