• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Pixcels.nl

  • Home
  • Categories
    • Excel apps
    • Pivottable stuff
    • PowerBI stuff
    • RGS en auditfiles
    • Sheet stuff
    • Userform stuff
    • VBA stuff
    • VBE stuff
    • XML stuff
  • About
You are here: Home / Categories / RGS en auditfiles / AuditfileValidator for Excel: validate and inspect Xml auditfiles

AuditfileValidator for Excel: validate and inspect Xml auditfiles

2020/09/20 by Frans

AuditfileValidator is an Excel addin that validates the most common auditfile formats and versions (XAF, XAS, XAA, SAF-T, etc) against their schema, and presents an overview of the most important properties of the auditfile.

It is a good practice to validate an Xml auditfile before you start analyzing it. Validating means that your auditfile is checked against it’s schema, which is basically a set of rules that defines the auditfile. There are several tools you can use to validate auditfiles, like Notepad++, XML-Notepad, Altova XMLSpy, and Microsoft Visual Studio. Now you can also validate any auditfile from within Excel by running the add-in AuditfileValidator.

Step 1: run the add-in

After downloading the Excel file you can simply run it by double clicking the file in Explorer; alternatively you can install the add-in permanently by copying it to your Excel add-in directory and activate it. After installing you find a Validate auditfile button in the Add-ins tab in Excel’s ribbon:

The Validate button in the Add-ins tab

Clicking the button opens the standard Open file dialog, from which you can navigate to and select your auditfile. AuditfileValidator creates a new workbook and starts reading the selected file, showing progress in Excel’s statusbar:

Showing progress in the statusbar

Validating is a two-step process. In the first pass AuditfileValidator verifies that the file is well formed xml. In the second pass the add-in will validate the xml against the schema and collects some key properties of the auditfile. For example, the report of the file SAF-T-OECD-v2.00_Maximal.xml that you can download here and was created by Visual Studio looks like this:

Validation report of a SAF-T file

Validation errors

The Toro Rosso demo file XAF32 ExactOnline_V32_2016 RGS.xaf (see my post XAF Explorer for Power BI) is an example of an invalid file, it’s report looks like this:

Report of a file with errors

The details of the validation errors can be found on the Errors sheet:

Details of the validation errors

In this case the errors are all constraint violations, which are easy to resolve or which probably can be ignored (however, you might argue that the constraint is wrong, the XAF schema should allow negative values).

Supported namespaces

The sheet Namespaces gives an overview of all supported namespaces and versions:

Overview of supported namespaces

The xsd-schema files for these namespaces are part of the AuditfileValidator.xlam file.

If you ask AuditfileValidator to validate an xml-file with unknown namespace the add-in will report the following error message:

Error message for unknown namespace

Link for download

Enter your e-mail below and you will receive an email with a download link to a zip-file with AuditfileValidator.xlam. It’s free!
We respect your privacy, and will use your e-mail only for communication related to this download.

Email *



Filed Under: Excel apps, RGS en auditfiles Tagged With: auditfiles, validate, xml

Primary Sidebar

Recent posts

  • The Grand Totals range of a pivottable in Excel
  • Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • AuditfileValidator for Excel: validate and inspect Xml auditfiles
  • SAF-T auditfiles for test and development
  • Een postcodetabel op basis van BAG Extract 2.0

Recent comments

  • Harry Cuntapay on Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • Lucio on Disable Shift key on open
  • Lucio on Disable Shift key on open
  • Julius Peter on The sequence of events in workbooks
  • Ben on The sequence of events in workbooks

Categories

Tags

ActiveControl ActivePrinter ADCS auditfiles BAG Connection Custom tab Database Debug Direct Debit drill-through Events event trapping Focus IBAN ISO 21378 MDI MSXML Multiple Document Interface normalize PAIN.008 PivotTable postcode Power BI RGS Ribbon SAF-T schema SEPA SOM Table Userforms validate VBA XAF xml XPath

Copyright © 2023 · eleven40 Pro on Genesis Framework · WordPress · Log in