In a recent project I needed the range of the Grand Totals in any pivottable in Excel. Unfortunately the PivotTable object has no property for this. Solutions I found on the Internet were for specific cases. So I developed a generic solution that I want to share with you.
[Continue reading] about The Grand Totals range of a pivottable in ExcelReplace drill-through for Power Pivot and Power BI pivottables in Excel
Excel's default drill-through for pivottables connected to a datamodel or Power BI dataset is often not very helpful. The add-in PowerDrill for Excel replaces Excel's drill-through and gives you full control over the drill details. … [Continue reading] about Replace drill-through for Power Pivot and Power BI pivottables in Excel
AuditfileValidator for Excel: validate and inspect Xml auditfiles
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. … [Continue reading] about AuditfileValidator for Excel: validate and inspect Xml auditfiles
SAF-T auditfiles for test and development
Most test and demo auditfiles that are available on the internet are incomplete and invalid. In this post I present a maximal SAF-T auditfile which is valid and contains all possible data elements. I also explain how to create this file and it's … [Continue reading] about SAF-T auditfiles for test and development
Een postcodetabel op basis van BAG Extract 2.0
In de Basisregistratie Adressen en Gebouwen (BAG) beheert het Kadaster alle adressen die in Nederland voorkomen. In de BAG wordt bij een adres ook de postcode van PostNL vastgelegd, en dus moet het mogelijk zijn om o.b.v. de BAG een postcodetabel van … [Continue reading] about Een postcodetabel op basis van BAG Extract 2.0
Improvements on SAF-T, OESO’s Standard Audit File for Tax
SAF-T (Standard Audit File for Tax) is an international standard for electronic exchange of accounting data. In this post I propose some improvements for SAF-T version 2.00. … [Continue reading] about Improvements on SAF-T, OESO’s Standard Audit File for Tax
Power BI ontsluit specificatie ISO standaard Audit Data Collection
In dit artikel beschrijf ik een Power BI app die de specificatie ontsluit van ISO standaard 21378 Audit Data Collection. Deze standaard is beschreven in een pdf document dat als bron heeft gediend voor de Power BI app. De app maakt de informatie … [Continue reading] about Power BI ontsluit specificatie ISO standaard Audit Data Collection
XAF Explorer voor Power BI
XAF Explorer voor Power BI toont de volledige inhoud van een XML Auditfile Financieel (XAF) en voert diverse controles uit. XAF Explorer is beschikbaar als template en ondersteunt de XAF versies vanaf 3.0. … [Continue reading] about XAF Explorer voor Power BI
RGS en XAF: bevindingen
In de afgelopen weken heb ik onderzoek gedaan naar het Referentie Grootboekschema RGS en de combinatie van RGS met de XML Auditfiles, met name XAF 3.2. Mijn bevindingen heb ik beschreven in twee openbare Power BI sites. … [Continue reading] about RGS en XAF: bevindingen
Globale straling in Power BI
Om te bepalen of je zonnepanelen goed presteren kun je de gemeten opbrengst in een periode vergelijken met de theoretisch maximaal haalbare opbrengst. Deze verhouding zou altijd constant moeten zijn. Voor het bepalen van de maximale opbrengst moeten … [Continue reading] about Globale straling in Power BI
How to set Excel’s ActivePrinter using VBA?
Recently I wanted to change Excel's ActivePrinter using VBA. That isn't as straightforward as I expected it to be. In particular there is a localization issue. … [Continue reading] about How to set Excel’s ActivePrinter using VBA?
Pivottable data connection models
The way a datasource connects to a PivotTable depends on the datasource. In this post an overview of the six connection models that you can create by means of Excel's userinterface. … [Continue reading] about Pivottable data connection models
Excel and XML: four observations and two bugs
I have been working with Xml files in Excel for some time now, and thought it is time to share some observations. … [Continue reading] about Excel and XML: four observations and two bugs
Create Xml from Schema
In this post I describe a tool to create an Xml file from a schema (Xsd) using VBA and the DOM and SOM objects of the Microsoft XML library MSXML version 6. … [Continue reading] about Create Xml from Schema
Get XPaths from workbook – update
In Get XPath objects from Excel workbook I described a method to retrieve all mapped XPaths from a workbook. In this post I describe another method, using the Worksheet.XmlMapQuery function. It's simpler, and faster. … [Continue reading] about Get XPaths from workbook – update
IBAN validation with BBAN format check
In this post I describe Excel/VBA functions for IBAN validation that not only perform a Mod 97 calculation but also validate the format of the country-specific Basic Bank Account Number (BBAN). … [Continue reading] about IBAN validation with BBAN format check
Sepa Direct Debits: from Excel to Pain
Europe is preparing for SEPA, the Single Euro Payments Area. One of the SEPA goals is to replace all national direct debit standards by one XML based SEPA standard by 1 February 2014. This article shows how to create such an Xml file from Excel, … [Continue reading] about Sepa Direct Debits: from Excel to Pain
Get XPath objects from Excel workbook
When working with XML enabled workbooks the relevant objects and properties are easy to retrieve. Unfortunately a workbook has no built-in collection of XPath objects. How can we get a list of all XPath objects in a workbook? … [Continue reading] about Get XPath objects from Excel workbook
Mastermind in Excel
In this post an Excel worksheet implementation of the well known Mastermind game. I developed the Mastermind part of this workbook several years ago. For this showcase app I added a scoreboard, and turned it into a so called dictator application. … [Continue reading] about Mastermind in Excel
Sequence of events in Userforms
In a previous post I wrote about the sequence of events in workbooks. This post is about the sequence of events in a simple userform. To built a reliable userform you need to know something about the userform mysteries. … [Continue reading] about Sequence of events in Userforms