Link to home
Start Free TrialLog in
Avatar of Lobb
Lobb

asked on

Import XML file into access table using VBA

Hi Experts,

I am working on an access form that browses for an xml file and then imports it into a table.  I have the browse button complete using VBA, but i am not sure how to write the code to import the XML file into the table.  I would like to import this xml file into a table called "data", and have it create fields for the fields on the XML file.  Here's my code for the browse button if that is any help.  Im not sure how to go about adding the xml file to the table using vba.


Option Compare Database

Option Explicit

Public Function GetFile() As Variant
Dim dialog As Object
Dim pickedfile As Boolean
Set dialog = Application.FileDialog(3)
GetFile = Null
With dialog
    .AllowMultiSelect = False
    .Title = "Please select file for import"
    .Filters.Clear
    .Filters.Add "XML Files", "*.XML"
    pickedfile = False
    pickedfile = .Show
    If pickedfile Then
        GetFile = .SelectedItems.Item(1)
    End If
End With
End Function

Open in new window


Thanks
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lobb
Lobb

ASKER

Thanks for your help so far LSMconsulting,

I've created a button on the form to submit this data. I am not sure how to put this file into a specific table.   Could you help me out on importing the data to a table? So far here is the code I have.  Also, it the import options line a built in option or do I need to specify it?

Private Sub Command10_Click()

Dim File As Variant
File = GetFile()
If IsNull(File) Then
    MsgBox "Nothing was selected", vbOKOnly
Else
    Me.FName = File


Application.ImportXML _
    DataSource:=Me.FName, _
    ImportOptions:=acStructureAndData
 
End If
End Sub

Open in new window

Avatar of Lobb

ASKER

I figured out how to do the import options..  I didn't realize there was a button to make your own options. Thanks for your help LSMconsulting!