?
Solved

Export XML via VBA code

Posted on 2014-10-04
7
Medium Priority
?
1,400 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 7

Accepted Solution

by:
Gauthier earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

752 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