Results 1 to 10 of 10
  1. #1
    Bertrand82 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    48

    VBA code multiple export to xml with filter

    Hi guys,

    I have a query, would like to export the query with 10 different filters and to 10 different locations in XML format.

    Suggestion of Vba code?

    thanks.



    br Olof

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Could use QueryDefs to modify the query filter criteria.

    Then maybe open the query and run:
    Docmd.RunCommand acCmdExportXML

    or

    Create an Export definition and run:
    Docmd.RunSavedImportExport

    Repeat the code for each of the 10 criteria. Could probably loop the procedure like:

    For i = 1 to 10

    Select Case i
    Case 1
    "filter string 1"
    ...
    Case 10
    "filter string 10"
    End Select

    'code to set query filter

    'code to export the query

    Next i
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are asking us to guess some sample data. Show us data, show us what input you have and what you want to output.
    We only know what you tell us. And so far, you've shown nothing.

  4. #4
    Bertrand82 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    48
    Thank you June, Though I have tride without success.

    Therefor an example attached.

    I would like to export the table to two xml-files to my desktop with two filters for column (Department)

    Filter 1 "HR"
    Filter 2 "Finance"

    Thanks br Bertrand
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    What have you tried? That database does not have any code. Provide the exact code you attempted.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Bertrand82 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    48
    I think i need code from scratch

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    You have made no attempt to adapt the code outlined in earlier post?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Bertrand82 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    48
    Hi June, my VBA knowledge is limited to the higheste extent.

    I have solved it with:

    Sub ExportToXmlFile()

    Application.ExportXML ObjectType:=acExportTable, DataSource:="Table1", DataTarget:="C:\Table1.xml", WhereCondition:="Department ='Finance' "
    Application.ExportXML ObjectType:=acExportTable, DataSource:="Table1", DataTarget:="C:\Table2.xml", WhereCondition:="Department =HR"
    Application.ExportXML ObjectType:=acExportTable, DataSource:="Table1", DataTarget:="C:\Table3.xml", WhereCondition:="Department ='HR'AND Name='Harry'OR Name='Michael ' "
    etc...
    etc...

    End Sub

    Does it make sence?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    I have never used the ExportXML method so I checked your syntax. Congratulations, it looks good but for couple little errors.

    Need apostrophes around HR in the second line.

    Need parentheses around the the Name criteria. Don't lose spaces that separate text - in front of AND and in front of OR
    WhereCondition:="Department='HR' AND (Name='Harry' OR Name='Michael')"

    Seems do not need to use QueryDefs after all if the WhereCondition argument works.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Bertrand82 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    48
    Thank you! Thread Solved!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 12-17-2012, 12:47 PM
  2. Replies: 7
    Last Post: 10-13-2012, 10:53 AM
  3. Export multiple query's to multiple sheet excel
    By vaikz in forum Import/Export Data
    Replies: 4
    Last Post: 08-15-2012, 08:53 AM
  4. How to filter dates using an apply filter code.
    By Jgreenfield in forum Reports
    Replies: 4
    Last Post: 11-15-2011, 01:38 PM
  5. Using code to set export specs
    By SSylvesterSYC in forum Programming
    Replies: 0
    Last Post: 06-08-2009, 10:29 AM

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