• 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 / Archives for Frans

Frans

The Grand Totals range of a pivottable in Excel

2020/10/19 by Frans

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.

[Read more…] about The Grand Totals range of a pivottable in Excel

Filed Under: Pivottable stuff, PowerBI stuff Tagged With: PivotTable, Power BI, VBA

Replace drill-through for Power Pivot and Power BI pivottables in Excel

2020/09/22 by Frans

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.

[Read more…] about Replace drill-through for Power Pivot and Power BI pivottables in Excel

Filed Under: Excel apps, PowerBI stuff Tagged With: drill-through, PivotTable, Power BI

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.

[Read more…] about AuditfileValidator for Excel: validate and inspect Xml auditfiles

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

SAF-T auditfiles for test and development

2020/09/04 by Frans

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 minimal variants.

[Read more…] about SAF-T auditfiles for test and development

Filed Under: RGS en auditfiles, XML stuff

Een postcodetabel op basis van BAG Extract 2.0

2020/07/28 by Frans

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 Nederland samen te stellen.

[Read more…] about Een postcodetabel op basis van BAG Extract 2.0

Filed Under: XML stuff Tagged With: BAG, postcode, xml

Improvements on SAF-T, OESO’s Standard Audit File for Tax

2020/07/23 by Frans

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.

[Read more…] about Improvements on SAF-T, OESO’s Standard Audit File for Tax

Filed Under: RGS en auditfiles, XML stuff Tagged With: SAF-T

Power BI ontsluit specificatie ISO standaard Audit Data Collection

2020/04/22 by Frans

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 toegankelijker en biedt de mogelijkheid om deze op verschillende manieren te benaderen.

[Read more…] about Power BI ontsluit specificatie ISO standaard Audit Data Collection

Filed Under: RGS en auditfiles Tagged With: ADCS, ISO 21378, Power BI

XAF Explorer voor Power BI

2019/12/20 by Frans

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.

[Read more…] about XAF Explorer voor Power BI

Filed Under: PowerBI stuff, RGS en auditfiles

RGS en XAF: bevindingen

2019/11/29 by Frans

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.

[Read more…] about RGS en XAF: bevindingen

Filed Under: RGS en auditfiles Tagged With: auditfiles, RGS, XAF

Globale straling in Power BI

2019/04/24 by Frans

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 we de globale straling weten. Hiervoor is nu een openbare Power BI app beschikbaar.

[Read more…] about Globale straling in Power BI

Filed Under: PowerBI stuff

How to set Excel’s ActivePrinter using VBA?

2015/02/27 by Frans

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.

[Read more…] about How to set Excel’s ActivePrinter using VBA?

Filed Under: VBA stuff Tagged With: ActivePrinter, VBA

Normalize data: from Userfriendly to Pivotfriendly

2013/12/08 by Frans

If possible I always use pivottables to create reports in Excel. Pivottables need well formed, normalized data tables as source, which are usually not suitable for data entry. In this post I describe two techniques to bridge the gap between userfriendly and pivotfriendly data structures.
[Read more…] about Normalize data: from Userfriendly to Pivotfriendly

Filed Under: Pivottable stuff Tagged With: Database, normalize, PivotTable, Table

Pivottable data connection models

2013/11/20 by Frans

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. [Read more…] about Pivottable data connection models

Filed Under: Pivottable stuff Tagged With: Connection, Database, PivotTable

Excel and XML: four observations and two bugs

2013/10/22 by Frans

I have been working with Xml files in Excel for some time now, and thought it is time to share some observations. [Read more…] about Excel and XML: four observations and two bugs

Filed Under: XML stuff Tagged With: schema, Table, xml, XPath

Create Xml from Schema

2013/10/04 by Frans

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. [Read more…] about Create Xml from Schema

Filed Under: XML stuff Tagged With: MSXML, schema, SOM, xml

Get XPaths from workbook – update

2013/09/03 by Frans

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.

[Read more…] about Get XPaths from workbook – update

Filed Under: XML stuff Tagged With: xml, XPath

IBAN validation with BBAN format check

2013/08/30 by Frans

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). [Read more…] about IBAN validation with BBAN format check

Filed Under: Excel apps Tagged With: IBAN, SEPA

Sepa Direct Debits: from Excel to Pain

2013/06/18 by Frans

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, without scripting. [Read more…] about Sepa Direct Debits: from Excel to Pain

Filed Under: XML stuff Tagged With: Direct Debit, PAIN.008, SEPA, xml

Get XPath objects from Excel workbook

2013/06/06 by Frans

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? [Read more…] about Get XPath objects from Excel workbook

Filed Under: XML stuff Tagged With: xml, XPath

Mastermind in Excel

2013/05/15 by Frans

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. [Read more…] about Mastermind in Excel

Filed Under: Excel apps Tagged With: Custom tab, event trapping, Ribbon, Userforms

  • Go to page 1
  • Go to page 2
  • Go to Next Page »

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