This VBA macro streamlines the process of downloading and parsing XML reports from Alma Analytics via its REST API. It dynamically extracts column headers and row data, then populates them into an Excel worksheet—perfect for librarians looking to automate data retrieval and simplify reporting in Excel or Power BI. If you are new the Excel Developer, you may need to check these links first:

• Show the Developer tab.
• Enable or disable macros in Microsoft 365 files.
• Run a macro in Excel.
• Bonus: Remove scientific notation for better displaying of MMS IDs

• Connects to Alma Analytics via REST API
• Parses XML response using MSXML2.DOMDocument.6.0
• Dynamically extracts column headers from XML schema
• Populates data rows into Excel
• Handles errors gracefully (HTTP and XML parsing)

• Microsoft Excel (with VBA support)
• Alma Analytics API access
• Valid API key and report path
• Internet access

• Open Excel.
• Press Alt + F11 to open the VBA Editor.
• Insert a new module.
• Copy and paste the contents of ParseXMLWithDynamicHeaders.vba into the module.
• Replace placeholders in the URL:
  • Your Analytics URL
  • Your report path
  • Your API key
• Save the Excel workbook to a macro enabled file (*.xlsm)

Run the macro ParseXMLWithDynamicHeaders, the macro will:

• Clear the first worksheet.
• Send a GET request to the Alma Analytics API.
• Parse the XML response. Extract column headers and data rows. Populate them into the worksheet.

** AI used to build this page

• For questions or support, please reach out via GitHub Issues. Stay in the loop—follow my GitHub for code updates.

• How to run a macro in Excel

• Get Excel data from Power BI

• Import Excel workbooks into Power BI

• The GitHub page has the most recent update

  Sub ParseXMLWithDynamicHeaders() Dim http As Object 'XML Dim xmlDoc As Object Dim schemaNode As Object Dim elementNodes As Object Dim rowNodes As Object Dim rowNode As Object Dim nodeBook As Object Dim attributeID As Object Dim attributeName As Object Dim headerName As String ' Dim ws As Worksheet ' Dim rows As Object Dim row As Object Dim childnode As Object Dim ChildNodesCounter As Integer Dim cellrow As Integer, cnt As Integer Dim columncount As Integer Dim colunnames() As String Dim arraylength As Integer With ThisWorkbook.Sheets(1) ' Set the target worksheet and clean contents before starting Set ws = ThisWorkbook.Sheets(1) ws.Cells.ClearContents ' Get HTTP request ' Define the URL url = "https://{YOUR ANALYTICS URL}?path={PATH TO THE REPORT}&limit=25&col_names=true&apikey={YOUR API KEY}" ' Create HTTP request Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", url, False http.Send If http.Status <> 200 Then MsgBox "Failed to download XML. Status: " & http.Status Exit Sub End If ' Load XML from response Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0") xmlDoc.async = False xmlDoc.LoadXML http.responseText If xmlDoc.ParseError.ErrorCode <> 0 Then MsgBox "Error in XML file: " & xmlDoc.ParseError.Reason Exit Sub End If ' Get Column Headers Set schemaNode = xmlDoc.SelectSingleNode("//*[local-name()='schema']/*[local-name()='complexType']/*[local-name()='sequence']") Set elementNodes = schemaNode.SelectNodes("*[local-name()='element']") ' Add columns headers to the worksheet ' and save the column names in array colunnames() arraylength = elementNodes.length ReDim Preserve colunnames(arraylength) For Each nodeBook In elementNodes If Not attributeID Is Nothing Then 'columnHeading is found in the node Set attributeID = nodeBook.Attributes.getNamedItem("saw-sql:columnHeading") Else 'If columnHeading is not found, use name instead Set attributeID = nodeBook.Attributes.getNamedItem("name") End If ' Fill column headings in the worksheet ws.Cells(1, cnt + 1).Value = attributeID.NodeValue ' Add column names to colunnames() colunnames(cnt) = nodeBook.Attributes.getNamedItem("name").NodeValue cnt = cnt + 1 Next nodeBook ' Fill rows ' Extract <Row> elements (namespace-aware) Set rows = xmlDoc.SelectNodes("//*[local-name()='Row']") If rows Is Nothing Or rows.length = 0 Then MsgBox "No <Row> elements found." Exit Sub End If ' Loop through rows and write data starting from the 2nd row on the worksheet (ws) cellrow = 2 For Each row In rows For ChildNodesCounter = 0 To row.ChildNodes.length - 1 ' Find the correct column for the value ' by matching the column name in colunnames array with XML childe node name For i = 0 To UBound(colunnames) - 1 If colunnames(i) = row.ChildNodes(ChildNodesCounter).NodeName Then ' Insert the value in the correct column ws.Cells(cellrow, i + 1).Value = row.ChildNodes(ChildNodesCounter).Text End If Next i Next ChildNodesCounter cellrow = cellrow + 1 Next row End With End Sub