Solved

Export XML via VBA code

Posted on 2014-10-04
7
1,148 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now