Export XML via VBA code

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

SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GauthierCommented:
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
SteveL13Author Commented:
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
SteveL13Author Commented:
Also, I am supposed to end up with two files... XSD and XML
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

GauthierCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
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
GauthierCommented:
The "user desktop's", never though you meant the user desktop folder.
0
SteveL13Author Commented:
Yep.  Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.