troubleshooting Question

Import XML File Into Access Table Using VBA???

Avatar of Eric Sherman
Eric ShermanFlag for United States of America asked on
Microsoft AccessMicrosoft ApplicationsMicrosoft Development
16 Comments3 Solutions6279 ViewsLast Modified:
I am testing various options to import a XML file into a Access table.  I can open the XML file in Excel and save it as a workbook then import it but I would like to avoid the extra steps.  Have not worked with XML files very much but I think it's possible using VBA to load the XML file then walk through it looking for specific nodes, etc.  

Below is a sample of a demo customer xml file that I am working with.  It has three customer records for this sample.  The number of customer rows will vary in the actual files.  Since the table with the same field names as the heading rows will exist in the Access DB, I just need to move to each row in the xml file after loaded and write the data to the table using a recordset.  

The field names are;

Internal ID, Name, Phone, Billing Address 1, Billing City, Billing State, Sales Rep, Search Engine, Keyword Search, Medium
<?xml version="1.0" encoding="utf-8"?>
 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>DemoCompany</Author>
<LastAuthor>DemoCompany</LastAuthor>
<Company>DemoCompany</Company>
</DocumentProperties>
<Styles>
<Style ss:ID="company">
<Alignment ss:Horizontal="Center"/>
<Font ss:Bold="1"/>
</Style> <Style ss:ID="error">
<Alignment ss:Horizontal="Center"/>
<Interior ss:Color="#d04040" ss:Pattern="Solid"/>
<Font ss:Bold="1"/>
</Style> <Style ss:ID="header">
<Alignment ss:Horizontal="Center"/>
<Font ss:Size="7" ss:Bold="1"/>
<Interior ss:Color="#d0d0d0" ss:Pattern="Solid"/>
</Style> <Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" ss:Size="8"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s__TIMEOFDAY"><NumberFormat ss:Format="Medium Time"/></Style>
<Style ss:ID="s__DATETIME"><NumberFormat ss:Format="General Date"/></Style>
<Style ss:ID="s__DATETIMETZ"><NumberFormat ss:Format="General Date"/></Style>
<Style ss:ID="s__DATE"><NumberFormat ss:Format="Short Date"/>
</Style><Style ss:ID="s__text"></Style><Style ss:ID="s__currency"><NumberFormat ss:Format="Currency"/></Style>
<Style ss:ID="s1_b_text"><Alignment ss:Indent="1"/><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/></Style>
<Style ss:ID="s_b_text"><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/></Style>
<Style ss:ID="s2__text"><Alignment ss:Indent="2"/></Style>
<Style ss:ID="s_b_currency"><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/><NumberFormat ss:Format="Currency"/></Style>
<Style ss:ID="s_currency_nosymbol"><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" /><NumberFormat ss:Format="#,##0.00_);[Red]\(#,##0.00\)"/></Style>
<Style ss:ID="s1__text"><Alignment ss:Indent="1"/></Style>
<Style ss:ID="s_b_currency_X"><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/><Interior ss:Color="#f0e0e0" ss:Pattern="Solid"/><NumberFormat ss:Format="Currency"/></Style>
<Style ss:ID="s__currency_en_US"><Alignment ss:Vertical="Center" ss:Horizontal="Right"/><NumberFormat ss:Format="&quot;$&quot;#,##0.00_);(&quot;$&quot;#,##0.00)"/></Style>
<Style ss:ID="s__currency_en_GB"><Alignment ss:Vertical="Center" ss:Horizontal="Right"/><NumberFormat ss:Format="&quot;£&quot;#,##0.00_);(&quot;£&quot;#,##0.00)"/></Style>
<Style ss:ID="s__currency_en_CA"><Alignment ss:Vertical="Center" ss:Horizontal="Right"/><NumberFormat ss:Format="&quot;Can$&quot;#,##0.00_);(&quot;Can$&quot;#,##0.00)"/></Style>
<Style ss:ID="s__currency_fr_FR_EURO"><Alignment ss:Vertical="Center" ss:Horizontal="Right"/><NumberFormat ss:Format="&quot;€&quot;#,##0.00_);(&quot;€&quot;#,##0.00)"/></Style>
</Styles>
<Worksheet ss:Name="DemoCustomerViewResults">
<Table><Row>
<Cell ss:StyleID="header"><Data ss:Type="String">Internal ID</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Name</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Phone</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Billing Address 1</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Billing City</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Billing State/Province</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Sales Rep</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Search Engine</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Keyword Search</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Medium</Data></Cell>
</Row>
<Row><Cell ss:StyleID="s__text"><Data ss:Type="Number">1397</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">3M</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">526 8426 1095</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
</Row>
<Row><Cell ss:StyleID="s__text"><Data ss:Type="Number">1542</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Aaron Abbott</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">(303) 464-4122</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">1500 Broadway</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Denver</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">CO</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Mary Redding</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
</Row>
<Row><Cell ss:StyleID="s__text"><Data ss:Type="Number">1249</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">AB&amp;I Holdings</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">(916) 831-7299</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Mary Redding</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Google</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Desk Accessories</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Print</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

Thanks,

ET
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 16 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros