Solved

Export XML via VBA code

Posted on 2014-10-04
7
1,339 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
Industry Leaders: 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!

 
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

Industry Leaders: 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

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 …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 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