Vertical XML import into Excel

[Tdorman]

Is there anyway to have data be imported into an XML map vertically, rather than horizontally. Currently, the data is imported into rows, however, I need it to be imported into columns. The data can be anywhere from a couple rows/columns to over a thousand rows/columns. Is there anything, either through excel itself or VBA, that would allow me to have the data be imported to columns? (i.e., have the headers as the rows and the data expands by columns)

 

[Tdorman]

This is the code I'm using to import the XML to the excel file, however, it is importing it into a column. Can I have it import into a row instead, without using a XSLT file to convert the XML?

Sub AssignElementsToRanges2()
    Dim myMap As XmlMap
    Dim strXPath As String
    Dim xDataFile As String
    Dim ws1 As Worksheet
   
   
    On Error Resume Next
   
    Set ws1 = Sheets("Schedule A - Part I")
    xDataFile = Application.GetOpenFilename
       
    Set myMap = ActiveWorkbook.XmlMaps("ReturnState_Map")
   
        strXPath = "/ns1:ReturnState/ns1:ReturnDataState/ns1:SchA-U/ns1:MemberAType/ns1:MemberHeader/ns1:MemberFEIN"
        ws1.Range("N6").XPath.SetValue myMap, strXPath, , True
   
    ThisWorkbook.XmlMaps("ReturnState_Map").Import xDataFile
 
End Sub

 

[Tdorman]

Ive edited the code to transpose the data after it is imported. However there is still one issue. When the second set of data is imported it overwrites the data in the first cell of the table. As below, the frist set should be imported starting in cell N6 and the second set should start in cell N7. The first set imports and is then transposed. The second set is imported overwriting the first cell, N6, so it is throwing the whole import off.

Sub AssignElementsToRanges2()
    Dim myMap As XmlMap
    Dim strXPath As String
    Dim xDataFile As String
    Dim ws1 As Worksheet
    Dim LastRow As Long
    Dim lColumn As Long
    Dim UsedRange As Range
    
    
    On Error Resume Next
    
    Set ws1 = Sheets("Schedule A - Part I")
    xDataFile = Application.GetOpenFilename
        
    Set myMap = ActiveWorkbook.XmlMaps("ReturnState_Map")
    
        strXPath = "/ns1:ReturnState/ns1:ReturnDataState/ns1:SchA-U/ns1:MemberAType/ns1:MemberHeader/ns1:UnitaryFEIN"
        ws1.Range("N6").XPath.SetValue myMap, strXPath
        ThisWorkbook.XmlMaps("ReturnState_Map").Import xDataFile
        LastRow = ws1.Cells(Rows.Count, 14).End(xlUp).Row
        CopyTransposed Sheets("Schedule A - Part I").Range("N6:N" & LastRow), Sheets("Schedule A - Part I").Range("N6")
        ws1.Range("N7:N" & LastRow).ClearContents
        
        strXPath = "/ns1:ReturnState/ns1:ReturnDataState/ns1:SchA-U/ns1:MemberAType/ns1:MemberHeader/ns1:MemberFEIN"
        ws1.Range("N7").XPath.SetValue myMap, strXPath
        ThisWorkbook.XmlMaps("ReturnState_Map").Import xDataFile
        LastRow = ws1.Cells(Rows.Count, 14).End(xlUp).Row
        CopyTransposed Sheets("Schedule A - Part I").Range("N7:N" & LastRow), Sheets("Schedule A - Part I").Range("N7")
        ws1.Range("N8:N" & LastRow).ClearContents
        
    UsedRange = ws1.UsedRange
    UsedRange.Columns.AutoFit
 
End Sub

Sub CopyTransposed(rngSource As Range, rngTargetCell As Range)
    rngTargetCell.Resize(rngSource.Columns.Count, rngSource.Rows.Count).Value = _
        Application.WorksheetFunction.Transpose(rngSource)
End Sub

 

[Tetonne]

good evening to all
with that code
is it possible to automate the import and update of a xml file in an excel file via vba?
for example : https://www.vertex42.com/sitemaps/pages.xml ie

when opening the excel file the vba macro check if the collection of the xml file took place within 12 hours for example.
if yes do nothing (say ready)
otherwise the update of the xml is launch in the excel. (with an update message when it is finished)

 

[Fluff]

As your question is significantly different from the OP's question, please start a new thread.
Also when you do please include links to any other site where you have asked this question. Thanks