Dynamische grafieken in Word

Dit artikel zoomt in op het automatiseren van dynamische grafieken in Word via Excel gebaseerd op Access. Deze techniek wordt gerealiseerd m.b.v. VBA.

In de praktijk komt het vaak voor dat grafieken uit Excel handmatig worden gekopieerd en geplakt in Word. Veelal gaat het hier om Word rapporten. Met de onderstaande techniek wordt dit foutgevoelige, tijdrovende en omslachtige proces geautomatiseerd.

Er is gekozen voor deze Word-Excel-Access instructie omdat:

  • Word het beste werkt voor rapporten;
  • Excel het beste werkt voor grafieken*;
  • Access het beste werkt voor relationele data.

Uiteraard is het niet nodig om Access als schakel te gebruiken. Deze techniek zal ook prima werken tussen alleen Excel en Word. Access voegt echter alleen iets toe wanneer er sprake is van relationele data. In dit artikel wordt als voorbeeld relationele data tussen bedrijven en medewerkers gebruikt.

De ingrediënten voor deze techniek zijn:

  1. Een Access database met relationele data, bijvoorbeeld:
    1. Een tabel met bedrijven en een tabel met gekoppelde medewerkers en de behaalde omzet;
    2. Een query Bedrijf met de totale omzet op bedrijfsniveau;
    3. Een query Medewerker met de totale omzet op medewerkersniveau.
  2. Een Excel werkboek met één draaigrafiek per werkblad: bijvoorbeeld een werkblad genaamd Bedrijf met een draaigrafiek gebaseerd op de query Bedrijf uit Access. Idem voor Medewerker;
  3. Een Word sjabloon (rapport) met de bladwijzers Bedrijf en Medewerker op de gewenste posities van de grafieken.

De techniek werkt als volgt, Access VBA creëert:

  1. Een Excel instantie die:
    1. Het Excel-werkboek voorziet van de juiste queries en relatieve verbindingen gebaseerd op een Access database;
    2. Alle queries in het Excel-werkboek update;
    3. Itereert door alle werkbladen van het Excel-werkboek en per werkblad een afbeelding (png) genereert van de draaigrafiek. De naam van de afbeelding is gelijk aan de naam van het werkblad.
  2. Een Word instantie die:
    1. Itereert door alle bladwijzers in het Word sjabloon;
    2. De naam van de bladwijzer (bijvoorbeeld Bedrijf) gebruikt voor het invoegen van de afbeelding Bedrijf.png.

*Word heeft ook de mogelijkheid om embedded interne of externe Excel grafieken te gebruiken. Het Excel object kan zelfs in Word worden uitgelezen (GetObject) en bewerkt worden. Performance technisch is dit niet de meest wenselijke oplossing. Daarnaast is deze oplossing ook erg storingsgevoelig.

De onderstaande Access VBA code bevat de bovenstaande techniek en is gemakshalve gebaseerd op een vroege binding t.a.v. de Excel en Word instanties.

Start module

Excel instantie

Update 30 maart 2018: om meer met SQL te kunnen doen kan de onderstaande functie gebruikt worden voor het gebruik van een SQL instructie gebaseerd op Microsoft Query van Excel. Dit i.p.v. de procedure maakQuery.

Word instantie

Download hier de toepassing.