Solved

Export XML via VBA code

Posted on 2014-10-04
7
1,299 Views
Last Modified: 2014-10-04
I am trying to use the following code to export specific table data from an Access 2010 database to the user's desktop.  But I don't know how to change the code to make it export a specific table data (which will have just one record always).  Can someone help?

--Steve

Dim StrFileName As String
   Dim fd As FileDialog
   Dim vrtSelectedItem As Variant
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = "c:\sample\*.xml"
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                Application.ExportXML _
                    DataSource:=vrtSelectedItem, _
                    ImportOptions:=acStructureAndData
                
            Next vrtSelectedItem
        Else
        End If
    End With
    Set fd = Nothing

Open in new window

0
Comment
Question by:SteveL13
[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
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:Gauthier
ID: 40361229
The above code is bugged and seems to mix import and export, see:
http://msdn.microsoft.com/en-us/library/office/ff193212(v=office.15).aspx
DataSource should be your table name and Data target should be the vrtSelectedItem

So the inner loop becomes:
                Application.ExportXML _
                    ObjectType:=acExportTable, _
                    DataSource:="yourTAbleName", _
                    DataTarget:=vrtSelectedItem

Open in new window

0
 

Author Comment

by:SteveL13
ID: 40361236
Well, I'm sure I have this all messed up but here is what I have so far.  Please remember, I am trying to use the following code to export specific table data from an Access 2010 database to the user's desktop.  But I don't know how to change the code to make it export a specific table data (which will have just one record always).

Private Sub Command523_Click()

Dim StrFileName As String
   Dim fd As FileDialog
   Dim vrtSelectedItem As Variant

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = "c:\sample\*.xml"
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
            Application.ExportXML _
                    ObjectType:=acExportTable, _
                    DataSource:="LOCALtblTEMPIHMGNotes", _
                    DataTarget:=vrtSelectedItem
            Next vrtSelectedItem
        Else
        End If
    End With
    Set fd = Nothing

End Sub

Open in new window

0
 

Author Comment

by:SteveL13
ID: 40361250
Also, I am supposed to end up with two files... XSD and XML
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 7

Accepted Solution

by:
Gauthier earned 500 total points
ID: 40361267
To output the xsd file, the parameter SchemaTarget need to be supplied.
I suppose the same filename with a different extension must be used.

SchemaTarget:=Replace(vrtSelectedItem, ".xml", ".xsd"), _

Open in new window

Replace() isn't really doing the job properly, so just try not to have .xml as part of the path or correct that with proper string manipulation ;)
0
 

Author Comment

by:SteveL13
ID: 40361268
I just resolved it with:

Dim strXLFile As String
Dim strXLFile2 As String
Dim strDesktopPath As String
Dim strDesktopPath2 As String

strDesktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
strDesktopPath2 = CreateObject("WScript.Shell").SpecialFolders("Desktop")
strXLFile = strDesktopPath & "\Checklist - " & Forms!frmIHMGnotes.Text328 & ".xml"
strXLFile2 = strDesktopPath2 & "\Checklist - " & Forms!frmIHMGnotes.Text328 & ".xsd"

    If MsgBox("This function will export the current checklist data record to your desktop.  Do you wish to continue?", vbYesNo) = vbYes Then
        Application.ExportXML acExportTable, "LOCALtblTEMPIHMGNotes", strXLFile, strXLFile2
        MsgBox "Please check your desktop.  The files should appear there."
        Else  ' answer is NO
        Exit Sub
    End If

Open in new window

0
 
LVL 7

Expert Comment

by:Gauthier
ID: 40361276
The "user desktop's", never though you meant the user desktop folder.
0
 

Author Comment

by:SteveL13
ID: 40361293
Yep.  Thanks for the help.
0

Featured Post

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
Access Query - calculate End of the Month in Query 2 53
Access lists formating 8 49
Combo box question 6 54
I really need your help with access query 9 26
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

732 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