Automating Alma Analytics for Librarians with VBA Macros for Excel and Power BI

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

• Power BI: Get data from Excel

• Import Excel workbooks into Power BI

• Microsoft Power BI – connecting to Alma API