Solved

Multi value in export.

Posted on 2006-11-06
4
298 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

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

Suggested Solutions

Title # Comments Views Activity
Lotus Domino 9 web server 6 97
Lotus notes - Follow up notes mail missing 15 80
lotus notes, exchange 7 112
LotusScript: Merge Data to Word File 22 112
  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…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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