'Import XML into Access with VBA

I have an XML file I'm trying to import into Access. I need help formatting the XSL transform to match the data. Right now, the XML imports as 3 separate tables. I only need the data starting at "Worksheet ss:Name='MetaInfo'". The other problem is that this sheet imports as a single column. Can someone help format this?

Current Access Result: Access Result

Current Excel Result: Excel Result

What I'd like (example of 1st 3 columns, not all colums in data):

| TESTED BY |    DATE    | SENT TO |
|:_________ |:__________:|:_______ |
| ZNB       | 02/21/2022 | 53      |

Current XML:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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">
  <Version>16.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>13845</WindowHeight>
  <WindowWidth>28800</WindowWidth>
  <WindowTopX>32767</WindowTopX>
  <WindowTopY>32767</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="yyyy/dd/mm\ hh:mm:ss"/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="MetaInfo">
  <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="28" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="80.25"/>
   <Column ss:Width="125.25"/>
   <Column ss:Width="82.5"/>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">UniqueReportId</Data></Cell>
    <Cell><Data ss:Type="String">UniqueMetaInfoId</Data></Cell>
    <Cell><Data ss:Type="String">Text</Data></Cell>
    <Cell><Data ss:Type="String">Unit</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">TESTED BY</Data></Cell>
    <Cell><Data ss:Type="String">ZNB</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">DATE</Data></Cell>
    <Cell><Data ss:Type="String">02/21/2022</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">SENT TO</Data></Cell>
    <Cell><Data ss:Type="Number">53</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">PLANT</Data></Cell>
    <Cell><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">F.O.NO.</Data></Cell>
    <Cell><Data ss:Type="String">B7861</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">LENGTH</Data></Cell>
    <Cell><Data ss:Type="Number">3364</Data></Cell>
    <Cell><Data ss:Type="String">ft</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">CUSTOMER</Data></Cell>
    <Cell><Data ss:Type="String">CUMBERLAND</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">PHASE</Data></Cell>
    <Cell><Data ss:Type="String">SINGLE</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">DESCRIPTION</Data></Cell>
    <Cell><Data ss:Type="String">3/C #15 X-RAY</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">CABLE NO.</Data></Cell>
    <Cell><Data ss:Type="String">438123A</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">CABLE RATING</Data></Cell>
    <Cell><Data ss:Type="Number">75</Data></Cell>
    <Cell><Data ss:Type="String">kV</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">SPECIFICATION</Data></Cell>
    <Cell><Data ss:Type="String">PL SPEC</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">INSULATION MATERIAL</Data></Cell>
    <Cell><Data ss:Type="String">G63</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">CALIBRATED AT</Data></Cell>
    <Cell><Data ss:Type="Number">20</Data></Cell>
    <Cell><Data ss:Type="String">pC</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">TITLE</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">DetectorName</Data></Cell>
    <Cell><Data ss:Type="String">192.168.10.101</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">StartDateTime</Data></Cell>
    <Cell><Data ss:Type="String">44613.605998125</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">TestMode</Data></Cell>
    <Cell><Data ss:Type="String">0</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">HasRIV</Data></Cell>
    <Cell><Data ss:Type="String">0</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">PDFailureLimit</Data></Cell>
    <Cell><Data ss:Type="String">5</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">RIVFailureLimit</Data></Cell>
    <Cell><Data ss:Type="String">100</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">PDFilterBandwidth</Data></Cell>
    <Cell><Data ss:Type="String">500000</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">PDFilterCentreFrequency</Data></Cell>
    <Cell><Data ss:Type="String">329000</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">PDFilterIECCompliant</Data></Cell>
    <Cell><Data ss:Type="String">1</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">MuxFilterOn</Data></Cell>
    <Cell><Data ss:Type="String">1</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">RIVFrequency</Data></Cell>
    <Cell><Data ss:Type="String">0</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">RIVFilter</Data></Cell>
    <Cell><Data ss:Type="String">0</Data></Cell>
    <Cell><Data ss:Type="String">N/A</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>27</ActiveRow>
     <ActiveCol>2</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="CustomInfo">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="7" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">UniqueReportId</Data></Cell>
    <Cell><Data ss:Type="String">UniqueCustomInfoId</Data></Cell>
    <Cell><Data ss:Type="String">Text</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">Field1</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">Field2</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">Field3</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">Field4</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">Field5</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">PDReport</Data></Cell>
    <Cell><Data ss:Type="String">Field6</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Channel-1_1">
  <Table ss:ExpandedColumnCount="19" ss:ExpandedRowCount="6" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Index="2" ss:Width="97.5"/>
   <Column ss:Index="4" ss:Width="48.75" ss:Span="13"/>
   <Column ss:Index="19" ss:Width="48.75"/>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Channel</Data></Cell>
    <Cell><Data ss:Type="String">Timestamp</Data></Cell>
    <Cell><Data ss:Type="String">Q_IEC_PC</Data></Cell>
    <Cell><Data ss:Type="String">Timestamp_Seconds</Data></Cell>
    <Cell><Data ss:Type="String">PdAttenuation</Data></Cell>
    <Cell><Data ss:Type="String">AllInputs</Data></Cell>
    <Cell><Data ss:Type="String">PdInputs</Data></Cell>
    <Cell><Data ss:Type="String">PdOverrange</Data></Cell>
    <Cell><Data ss:Type="String">Prf</Data></Cell>
    <Cell><Data ss:Type="String">Gating</Data></Cell>
    <Cell><Data ss:Type="String">Riv</Data></Cell>
    <Cell><Data ss:Type="String">RivAttenuation</Data></Cell>
    <Cell><Data ss:Type="String">RivOverRange</Data></Cell>
    <Cell><Data ss:Type="String">Voltage_RMS</Data></Cell>
    <Cell><Data ss:Type="String">Voltage_PK</Data></Cell>
    <Cell><Data ss:Type="String">VoltageMean</Data></Cell>
    <Cell><Data ss:Type="String">VoltageInput</Data></Cell>
    <Cell><Data ss:Type="String">VoltageSynchronization</Data></Cell>
    <Cell><Data ss:Type="String">Frequency</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Channel-1(1)</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="DateTime">1899-12-31T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="String">19</Data></Cell>
    <Cell><Data ss:Type="Number">20.7</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">-1</Data></Cell>
    <Cell><Data ss:Type="Number">7.0180240673669116E-315</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">255</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0.01</Data></Cell>
    <Cell><Data ss:Type="Number">0.06</Data></Cell>
    <Cell><Data ss:Type="Number">-131.37</Data></Cell>
    <Cell><Data ss:Type="Number">152</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="Number">60</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Channel-1(1)</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="DateTime">1899-12-31T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="String">19</Data></Cell>
    <Cell><Data ss:Type="Number">22</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">-1</Data></Cell>
    <Cell><Data ss:Type="Number">7.0180240673669116E-315</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">255</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0.01</Data></Cell>
    <Cell><Data ss:Type="Number">0.06</Data></Cell>
    <Cell><Data ss:Type="Number">-123.15</Data></Cell>
    <Cell><Data ss:Type="Number">152</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="Number">60</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Channel-1(1)</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="DateTime">1899-12-31T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="String">19</Data></Cell>
    <Cell><Data ss:Type="Number">23.4</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">1140.49</Data></Cell>
    <Cell><Data ss:Type="Number">7.0180240673669116E-315</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">255</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0.01</Data></Cell>
    <Cell><Data ss:Type="Number">0.06</Data></Cell>
    <Cell><Data ss:Type="Number">-123.96</Data></Cell>
    <Cell><Data ss:Type="Number">152</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="Number">60</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Channel-1(1)</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="DateTime">1899-12-31T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="String">19.6</Data></Cell>
    <Cell><Data ss:Type="Number">22.7</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">1369.99</Data></Cell>
    <Cell><Data ss:Type="Number">7.0180240673669116E-315</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">255</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0.01</Data></Cell>
    <Cell><Data ss:Type="Number">0.06</Data></Cell>
    <Cell><Data ss:Type="Number">-119.71</Data></Cell>
    <Cell><Data ss:Type="Number">152</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="Number">60</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">Channel-1(1)</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="DateTime">1899-12-31T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="String">19</Data></Cell>
    <Cell><Data ss:Type="Number">23.4</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">-1</Data></Cell>
    <Cell><Data ss:Type="Number">7.0180240673669116E-315</Data></Cell>
    <Cell><Data ss:Type="Number">0</Data></Cell>
    <Cell><Data ss:Type="Number">255</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">0.01</Data></Cell>
    <Cell><Data ss:Type="Number">0.06</Data></Cell>
    <Cell><Data ss:Type="Number">-122.76</Data></Cell>
    <Cell><Data ss:Type="Number">152</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="Number">60</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
xmlvbams-access


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions