Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118

    Problem Exporting Multivalued Field to Excel

    Hi everyone. My VBA exports multiple queries (as determined by the user's selection on a form) to a new Excel workbook, with each query copied into its own worksheet.

    The code works well, but with one problem. It screws up multivalued fields. (I know, I know, multivalued fields are terrible- but it's too late for me to change them.) For example, "Rifle, Shotgun" is displayed only as "Ri", and "Barbara England, Amy Stengel, Francine Gordon" is displayed as "27".

    Any idea how I can change my exporting process to make it work right?

    I suspect it can be done because when I open and export a query manually, it works fine as long as I select the "Export data with formatting and layout" option. So I guess my question is, how can I adjust my VBA procedure to do that?

    The code is below. It's quite long, so I've bolded the parts that show the method I'm using to export the queries. Thanks very much for your help! -Matt


    [code]
    Private Sub ExportData_Click()

    'Error Message if valid selection not made:
    If ExportOptions <> 1 And ExportOptions <> 2 And ExportOptions <> 3 And _
    ExportOptions <> 4 And ExportOptions <> 5 And ExportOptions <> 6 Then
    Dim dResponse As Integer
    dResponse = MsgBox("Please make a valid selection.", vbExclamation, "Error")
    Exit Sub
    End If

    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset

    Dim i As Integer
    Dim strSQL As String
    Dim SheetName As String
    Dim QNumber As Integer
    Dim QMax As Integer
    Dim QName As String

    'DEFINE QUERY:

    Set MyDatabase = CurrentDb

    QMax = Switch(ExportOptions = 1, 3, _
    ExportOptions = 2, 9, _
    ExportOptions = 3, 3, _
    ExportOptions = 4, 6, _
    ExportOptions = 5, 3, _
    ExportOptions = 6, 24)

    For QNumber = 1 To QMax

    On Error Resume Next


    MyDatabase.QueryDefs.Delete "ExportToExcel" 'Deleting prior definition of query
    On Error GoTo 0

    If ExportOptions = 1 Then
    QName = Switch(QNumber = 1, "qryExportIncidentDetails", _
    QNumber = 2, "qryExportComplaintInfo", _
    QNumber = 3, "qryExportEventInfo")

    SheetName = Switch(QNumber = 1, "Incident Details", _
    QNumber = 2, "Complaint Info", _
    QNumber = 3, "Event Info")
    End If

    If ExportOptions = 2 Then
    QName = Switch(QNumber = 1, "qryExportVictimPerps", _
    QNumber = 2, "qryExportDemographicInfo", _
    QNumber = 3, "qryExportFatalityandInjury", _
    QNumber = 4, "qryExportCriminalHistory", _
    QNumber = 5, "qryExportSAMH", _
    QNumber = 6, "qryExportInjunctionHistory", _
    QNumber = 7, "qryExportServices", _
    QNumber = 8, "qryExportVictimQuestions", _
    QNumber = 9, "qryExportPerpQuestions")

    SheetName = Switch(QNumber = 1, "Victims + Perps", _
    QNumber = 2, "Demographic Info", _
    QNumber = 3, "Injuries + Fatalities", _
    QNumber = 4, "Criminal History", _
    QNumber = 5, "Drug Abuse + Mental Health", _
    QNumber = 6, "Injunction History", _
    QNumber = 7, "Services Requested", _
    QNumber = 8, "Additional Victim Qs", _
    QNumber = 9, "Additional Perp Qs")
    End If

    If ExportOptions = 3 Then
    QName = Switch(QNumber = 1, "qryExportRelationships", _
    QNumber = 2, "qryExportRelationshipInfo", _
    QNumber = 3, "qryExportCustody")

    SheetName = Switch(QNumber = 1, "Relationships", _
    QNumber = 2, "Relationship Info", _
    QNumber = 3, "Custody Info")
    End If

    If ExportOptions = 4 Then
    QName = Switch(QNumber = 1, "qryExportMeetingInfo", _
    QNumber = 2, "qryExportTimeline", _
    QNumber = 3, "qryExportRedFlags", _
    QNumber = 4, "qryExportAgencyInvolvement", _
    QNumber = 5, "qryExportAdditionalQuestions", _
    QNumber = 6, "qryExportRecommendations")

    SheetName = Switch(QNumber = 1, "Meeting Info", _
    QNumber = 2, "Timeline", _
    QNumber = 3, "Red Flags", _
    QNumber = 4, "Agency Involvement", _
    QNumber = 5, "AdditionalQs", _
    QNumber = 6, "Recommendations")
    End If

    If ExportOptions = 5 Then
    QName = Switch(QNumber = 1, "qryExportContributors", _
    QNumber = 2, "qryExportWitnesses", _
    QNumber = 3, "qryExportDocuments")

    SheetName = Switch(QNumber = 1, "Contributors", _
    QNumber = 2, "Witnesses", _
    QNumber = 3, "Documents")
    End If

    If ExportOptions = 6 Then
    QName = Switch(QNumber = 1, "qryExportIncidentDetails", _
    QNumber = 2, "qryExportComplaintInfo", _
    QNumber = 3, "qryExportEventInfo", _
    QNumber = 4, "qryExportVictimPerps", _
    QNumber = 5, "qryExportDemographicInfo", _
    QNumber = 6, "qryExportFatalityandInjury", _
    QNumber = 7, "qryExportCriminalHistory", _
    QNumber = 8, "qryExportSAMH", _
    QNumber = 9, "qryExportInjunctionHistory", _
    QNumber = 10, "qryExportServices", _
    QNumber = 11, "qryExportVictimQuestions", _
    QNumber = 12, "qryExportPerpQuestions", _
    QNumber = 13, "qryExportRelationships", _
    QNumber = 14, "qryExportRelationshipInfo", _
    QNumber = 15, "qryExportCustody", _
    QNumber = 16, "qryExportMeetingInfo", _
    QNumber = 17, "qryExportTimeline", _
    QNumber = 18, "qryExportRedFlags", _
    QNumber = 19, "qryExportAgencyInvolvement", _
    QNumber = 20, "qryExportAdditionalQuestions", _
    QNumber = 21, "qryExportRecommendations", _
    QNumber = 22, "qryExportContributors", _
    QNumber = 23, "qryExportWitnesses", _
    QNumber = 24, "qryExportDocuments")

    SheetName = Switch(QNumber = 1, "Incident Details", _
    QNumber = 2, "Complaint Info", _
    QNumber = 3, "Event Info", _
    QNumber = 4, "Victims + Perps", _
    QNumber = 5, "Demographic Info", _
    QNumber = 6, "Injuries + Fatalities", _
    QNumber = 7, "Criminal History", _
    QNumber = 8, "Drug Abuse + Mental Health", _
    QNumber = 9, "Injunction History", _
    QNumber = 10, "Services Requested", _
    QNumber = 11, "Additional Victim Qs", _
    QNumber = 12, "Additional Perp Qs", _
    QNumber = 13, "Relationships", _
    QNumber = 14, "Relationship Info", _
    QNumber = 15, "Custody Info", _
    QNumber = 16, "Meeting Info", _
    QNumber = 17, "Timeline", _
    QNumber = 18, "Red Flags", _
    QNumber = 19, "Agency Involvement", _
    QNumber = 20, "AdditionalQs", _
    QNumber = 21, "Recommendations", _
    QNumber = 22, "Contributors", _
    QNumber = 23, "Witnesses", _
    QNumber = 24, "Documents")
    End If

    strSQL = "SELECT " & QName & ".* FROM " & QName

    Set MyQueryDef = MyDatabase.CreateQueryDef("ExportToExcel", strSQL) 'Defining Query

    Set MyRecordset = MyQueryDef.OpenRecordset
    'Open the query
    'EXPORT TO EXCEL:

    If QNumber = 1 Then

    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")

    With xlApp
    .Visible = True
    .Workbooks.Add 'Open New Excel Workbook

    On Error Resume Next
    .Sheets("Sheet2").Select
    .ActiveWindow.SelectedSheets.Delete
    .Sheets("Sheet3").Select
    .ActiveWindow.SelectedSheets.Delete
    On Error GoTo 0

    .Sheets("Sheet1").Select
    End With
    End If

    With xlApp

    If QNumber <> 1 Then
    .Sheets.Add After:=.Sheets(.Sheets.Count)
    End If

    .ActiveSheet.Range("A2").CopyFromRecordset MyRecordset 'Copy the recordset to Excel

    'FORMAT SPREADSHEET:

    For i = 1 To MyRecordset.Fields.Count
    xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
    Next i 'Add column heading names to the spreadsheet

    .Rows("1:1").Select 'Format Headers
    .Selection.Font.Bold = True
    .Selection.Interior.Color = 14993882

    .Columns("A:A").Select 'Format ID Numbers (1st Column)
    .Selection.NumberFormat = "0.00"

    .Cells.Select
    .Selection.ColumnWidth = 60
    .Selection.WrapText = True
    .Cells.EntireColumn.AutoFit
    .Selection.HorizontalAlignment = xlLeft
    .Selection.VerticalAlignment = xlTop

    .ActiveSheet.UsedRange.Borders.Weight = xlThin

    .ActiveSheet.Name = SheetName

    End With

    'DELETE QUERY:

    On Error Resume Next
    MyDatabase.QueryDefs.Delete "ExportToExcel"
    On Error GoTo 0

    Next QNumber

    xlApp.Sheets(1).Select
    xlApp.Range("A1").Select

    End Sub
    [\code]

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,126
    I guess the downside of never using something like a mvf means one has little experience with it (which might be problematic in this forum). If no one steps in with a better suggestion, Google child recordset. I've seen how you can pull out the individual values using that. It might not help in this situation, but since you're stuck with the mvf, you might need the info later.
    As an aside, if the message box presents no options (OK only) you don't need to dim or use the result:
    If...Then
    MsgBox("Please make a valid selection.", vbExclamation, "Error")
    Exit Sub
    End If
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  4. #4
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,269
    I don't use mvf because, as you are finding, they have limitations

    I think you can solve your problem by modifying your sql

    strSQL = "SELECT " & QName & ".* FROM " & QName

    this might work

    strSQL = "SELECT " & QName & ".*, " & Qname.mvfname.value & " FROM " & QName

    or you'll need to identify each field but slap .value at the end of multivalue field names

    but I guess either method messes up an automated export function

  5. #5
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Thanks so much for the replies, everyone.

    But if I understand it correctly, it sounds like if I do what you're suggesting, I'll end up with something like this...

    MEETING | ATTENDEES
    3/4/2015 | John Smith
    3/4/2015 | Jane Doe
    3/4/2015 | Bob White

    Whereas this is I actually need (which is how the query looks prior to exporting)...

    MEETING | ATTENDEES
    3/4/2015 | John Smith, Jane Doe, Bob White

    So, I'm hoping there's another way. I mean, if I can keep the query's formatting and layout with the export wizard, there must be a way to do it with VBA, no?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,126
    Quote Originally Posted by MatthewR View Post
    ...there must be a way to do it with VBA, no?
    Ja, I already mentioned it.

  7. #7
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Sorry Micron, I don't get it. You suggested pulling out individual values using the child recordset. As I understand it, that's what Ajax is telling me to do. But that results in a format I don't want.

    Would you be able to clarify, please?

  8. #8
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,269
    Whereas this is I actually need (which is how the query looks prior to exporting)...

    MEETING | ATTENDEES
    3/4/2015 | John Smith, Jane Doe, Bob White
    take a look at this link

    http://allenbrowne.com/func-concat.html

    seem to recall it has been modified for multivalue fields, but if not google 'concatrelated' and you might find a link to one that has been

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,803
    Check out this code from Allen Browne
    Concatenate values from related records
    http://www.allenbrowne.com/func-concat.html


    And FWIW, if you start running into "out of memory problems" or other strange problems, you might look at closing and destroying the objects you created. I understand that Access is supposed to do that, but I have seen cases when explicitly closing and destroying objects solved the problem.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Thanks, Ajax and ssanfu. I actually tried that, and it worked... but it made the export go really, unbearably slow.

    Does that have to do with closing and destroying objects?

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,803
    but it made the export go really, unbearably slow.
    No, the extreme slowness is a benefit of using a MVF.
    (actually, it is the result of having to use more code to do what you would easily do without the MVF)



    Does that have to do with closing and destroying objects?
    No. Closing and destroying objects is just good programming. Goes along with not using spaces in object names and using a naming convention.

    I was taught :
    "If you open it, close it"
    "If you create it, destroy it"

    So, with code like this:
    Code:
    Dim db As DAO.Database
    Set db = Currentdb
    you would only use:
    Code:
    Set db = Nothing
    because you didn't open it, only created it.

    Never do this: "db.Close"



    In this code
    Code:
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset ("MyQuery")
    you have to close the recordset AND destroy the reference to it
    Code:
    rs.Close
    Set rs = Nothing
    because you created a reference and opened it.




    Disclaimer:
    This is my only opinion, your opinions may vary. Valid only as long as your computer is on. Prices may vary. Objects may be closer that you realize.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,269
    but it made the export go really, unbearably slow.
    it might be to do with lack of indexing

  13. #13
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Thanks for the replies. Ajax, what should I be indexing?

    Ssanfu, yeah I think I've learned my lesson on MVF's. I will avoid them in the future, if I can. And I'll make sure to close my objects from hereon in.

    I'm working on an alternative method right now using DoCmd.OutputTo acOutputQuery, since that method exports the MVF's without any problem. I've hit a snag getting it to do multiple queries, though. But I think the problem is fixable. I'm going to start a new thread on it.

    Matt

  14. #14
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,269
    whatever fields you regularly use for criteria, linking and/or sorting. Note it is not worth indexing fields containing lots of nulls or limited range of data (e.g. yes/no)

    Unfortunately, MVF can't be indexed and I don't think Access does some sort of indexing in the background

  15. #15
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Okay, cool. Thanks for the info. I will give that a shot.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem exporting multi-line addresses to Excel
    By mjwillyone in forum Import/Export Data
    Replies: 4
    Last Post: 01-27-2016, 11:01 AM
  2. Exporting a Table to MS Excel problem
    By nosec in forum Import/Export Data
    Replies: 9
    Last Post: 11-05-2013, 11:28 AM
  3. Multivalued Look-Up Field Problem
    By Mike Wood in forum Access
    Replies: 4
    Last Post: 02-27-2012, 01:26 PM
  4. Replies: 1
    Last Post: 10-19-2011, 07:51 PM
  5. Exporting to Excel Problem
    By octu in forum Import/Export Data
    Replies: 1
    Last Post: 04-14-2010, 11:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums