Dynamische grafieken in Word - Pascal ter Heege

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:

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.
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;
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:

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

-----------------------------------------------------------------------------------------------------------------------
' Auteur        : pascalterheege.nl
' Datum         : 12-3-2018
' Object        : modStart
' Doel          : proces starten m.b.v. button op formulier
'-----------------------------------------------------------------------------------------------------------------------

Option Compare Database
Option Explicit
 
Sub Start()

    Dim xl As clsExcel
    Dim wd As clsWord

    Set xl = New clsExcel
    Set xl = Nothing
    
    Set wd = New clsWord
    Set wd = Nothing

End Sub

Excel instantie

'-----------------------------------------------------------------------------------------------------------------------
' Auteur        : pascalterheege.nl
' Datum         : 12-3-2018
' Object        : clsExcel
'-----------------------------------------------------------------------------------------------------------------------

Option Compare Database
Option Explicit
 
Dim objExcel As Excel.Application
Dim objWerkboek As Excel.Workbook

Private Sub Class_Initialize()

'   1. Een Excel instantie die

    Set objExcel = New Excel.Application
    Set objWerkboek = objExcel.Workbooks.Add(CurrentProject.Path & "\Grafiek.xlsx")
    
'    objExcel.Visible = True
    
'   A. Het Excel-werkboek voorziet van de juiste queries en relatieve verbindingen gebaseerd op een Access database;
    
    verwijderQueries
    verwijderVerbindingen
    
    maakQuery "Bedrijf"
    maakQuery "Medewerker"
    
'   B. Alle queries in het Excel-werkboek update;
    
    objWerkboek.RefreshAll
    
'   C. 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.

    maakPNG

End Sub
Private Sub verwijderQueries()

    Dim q
    
    For Each q In objWerkboek.Queries
    
        q.Delete
    
    Next q
    
End Sub
Private Sub verwijderVerbindingen()

    Dim c
    
    For Each c In objWerkboek.Connections
    
        c.Delete
    
    Next c

End Sub
Private Sub maakQuery(strQuery As String)
       
'   Query
    objWerkboek.Queries.Add Name:=strQuery, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Bron = Access.Database(File.Contents(""" & CurrentDb.Name & """), [CreateNavigationProperties=true])," & Chr(13) & "" & Chr(10) & "    _" & strQuery & " = Bron{[Schema="""",Item=""" & strQuery & """]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    _" & strQuery
    
'   Verbinding
    objWerkboek.Connections.Add2 "Query - " & strQuery, _
        "Verbinding maken met de query " & strQuery & " in de werkmap.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & strQuery & ";Extended Properties=""""" _
        , "SELECT * FROM [" & strQuery & "]", 2

End Sub
Private Sub maakPNG()

    Dim objWerkblad As Worksheet
    Dim objGrafiek As ChartObject
    Dim Grafiek As Chart
    
    For Each objWerkblad In objWerkboek.Sheets
    
        Set objGrafiek = objWerkblad.ChartObjects(1)
        Set Grafiek = objGrafiek.Chart
        
        Grafiek.Export CurrentProject.Path & "\Afbeeldingen\" & objWerkblad.Name & ".png", filtername:="PNG"
    
    Next objWerkblad

End Sub
Private Sub Class_Terminate()
    
    objWerkboek.Close False
    objExcel.Quit
    
    Set objWerkboek = Nothing
    Set objExcel = Nothing
    
End Sub

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.

Private Sub maakVerbinding(strQuery As String)

    objWerkboek.Connections.Add2 _
        strQuery, _
        "", Array(Array("ODBC;DSN=MS Access Database;DBQ=" & CurrentDb.Name & ";DefaultDir=" & CurrentProject.Path & ";DriverId=25;FIL=MS Access;MaxBuff"), Array("erSize=2048;PageTimeout=5;")), _
        "SELECT * FROM " & strQuery, _
        2
        
End Sub

Word instantie

'-----------------------------------------------------------------------------------------------------------------------
' Auteur        : pascalterheege.nl
' Datum         : 13-3-2018
' Object        : clsWord
'-----------------------------------------------------------------------------------------------------------------------

Option Compare Database
Option Explicit

Dim objWord As Word.Application
Dim objDocument As Word.Document
Dim objBookmark As Word.Bookmark

Private Sub Class_Initialize()

'   2. Een Word instantie die:

    Set objWord = New Word.Application
    Set objDocument = objWord.Documents.Add(CurrentProject.Path & "\Grafiek.docx")
    
    objWord.Visible = True
    
'   A. Itereert door alle bladwijzers in het Word sjabloon;
    
    For Each objBookmark In objDocument.Bookmarks
    
'       B. De naam van de bladwijzer (bijvoorbeeld Bedrijf) gebruikt voor het invoegen van de afbeelding Bedrijf.png.
        With objWord.Selection
            .GoTo What:=wdGoToBookmark, Name:=objBookmark.Name
            .InlineShapes.AddPicture FileName:=CurrentProject.Path & "\Afbeeldingen\" & objBookmark.Name & ".png", LinkToFile:=False, SaveWithDocument:=True
        End With
    
    Next objBookmark


End Sub
Private Sub Class_Terminate()

    Set objDocument = Nothing
    Set objWord = Nothing

End Sub

Download hier de toepassing.