Excel automatiseren: welk hulpmiddel voor welk werk

Excel automatiseren betekent dat het werk dat je elke week met de hand doet, vanzelf gebeurt: een export inlezen en opkuisen, een rapport opmaken, gegevens van het ene blad naar het andere zetten of een mail sturen als een cel verandert. Daar heeft Microsoft vier hulpmiddelen voor: macro's in VBA, Power Query, Office Scripts en Power Automate. Daarnaast kan een koppeling Excel rechtstreeks laten praten met je andere software.

Ze lijken op elkaar, maar ze werken op een andere plek, met een andere licentie en met andere grenzen. Een macro draait op de pc waar het bestand openstaat. Een Office Script draait in Microsoft 365. Een flow in Power Automate draait in de cloud, ook als niemand Excel openheeft. Wie dat verschil kent, bouwt niet op het verkeerde hulpmiddel.

Vijf manieren naast elkaar

Wat een API-koppeling is, staat bij de begrippen. De rest van deze gids neemt de vijf een voor een door, met wat ze kosten en waar ze stoppen.

Macro's: snel gemaakt, maar gebonden aan een pc

Een macro is de oudste manier om Excel te automatiseren. Je drukt op opnemen, doet je handelingen en speelt ze daarna met een knop opnieuw af. In de VBA-editor kan je de code daarna aanpassen, met lussen en voorwaarden. Voor een rapport dat je elke maandag opmaakt, volstaat dat vaak.

Drie grenzen komen in de praktijk het vaakst terug. Een macro draait alleen in Excel op de pc, niet in Excel op het web en niet op een telefoon. Op Windows blokkeert Office standaard macro's in bestanden die van het internet komen, ook als een collega het bestand mailt. En de kennis zit vaak bij een persoon: wie de macro schreef, is de enige die weet wat hij doet als er een kolom bijkomt.

Power Query: data ophalen zonder knippen en plakken

Krijg je elke week een export uit je boekhouding, je webshop of je kassasysteem, dan is Power Query de eerste stap. Je wijst het bestand of de map aan, kuist de kolommen op, voegt tabellen samen en laadt het resultaat in een blad. De stappen worden bewaard. Komt volgende week een nieuwe export, dan klik je op vernieuwen en gebeurt alles opnieuw.

Power Query schrijft niets terug naar de bron en stuurt niets door. Het is een lezer, geen schrijver. Voor een wekelijks overzicht van omzet per klant werkt het uitstekend; moet je daarna nog met de hand iets overtypen naar een ander systeem, dan heb je een flow of een koppeling nodig. Groeit dat overzicht uit tot een rapport dat het hele team elke dag bekijkt, lees dan een dashboard laten maken.

Office Scripts: een Excel automatiseren script in de browser

Office Scripts is de opvolger van de macro voor wie in Microsoft 365 werkt. Je neemt handelingen op met de actierecorder of schrijft ze in TypeScript, en zet ze achter een knop in de werkmap. Het script staat in je OneDrive en je kan het delen met collega's in je organisatie.

Je hebt Excel op het web, Excel voor Windows vanaf versie 2210 of Excel voor Mac nodig, plus OneDrive voor bedrijven en een zakelijke of onderwijslicentie. Met een persoonlijk Microsoft-account werkt het niet. Draait je script via Power Automate, dan gelden deze grenzen:

Reken die grenzen na voor je een flow bouwt die elke nacht alle orders van de dag verwerkt. Past de batch niet in 120 seconden of 5 MB, dan knip je hem op, of laat je dat werk aan een koppeling die niet aan die grenzen vastzit.

Power Automate: Excel laten praten met de rest

Power Automate verbindt Excel met de andere apps van Microsoft en met software van anderen: een nieuw antwoord in een formulier wordt een rij in je tabel, een rij met status klaar wordt een mail naar de klant, een bijlage in Outlook belandt in een map en daarna in een blad.

Met een werk- of schoolaccount krijg je een gratis licentie: cloudflows met de standaardconnectors, zonder ze te delen. Wil je flows delen met je team, een eigen connector gebruiken of een premiumconnector, dan heb je Premium nodig. Microsoft vermeldt daarvoor 15 dollar per gebruiker per maand bij jaarbetaling, met 40.000 acties per dag en een robot die meekijkt op de pc voor desktopflows.

Een koppeling: als Excel niet meer de bron is

Een koppeling haalt gegevens via de API van je software op en zet ze waar ze horen: in een database, in je boekhouding of terug in een overzicht. Met een webhook gebeurt dat op het moment dat er iets verandert, niet pas als iemand op vernieuwen klikt. Dat is werk voor een ontwikkelaar, maar het hangt niet aan een licentie van Microsoft en niet aan een pc die moet openstaan. Hoe dat in zijn werk gaat, lees je in een koppeling laten maken.

Wanneer Excel automatiseren genoeg is geweest

Automatiseren maakt een spreadsheet sneller, niet groter. Een werkblad heeft 1.048.576 rijen en 16.384 kolommen, maar dat is zelden de grens die je eerst raakt. Je raakt ze bij mensen: twee collega's die tegelijk dezelfde planning aanpassen, een klant die een oude versie krijgt, iemand die de lonen ziet die hij niet hoort te zien.

Drie signalen dat je volgende macro een vergissing is:

• Je automatiseert het overtypen tussen bestandenDan heb je twee bronnen voor hetzelfde gegeven. Een koppeling of een gedeelde database lost dat op; een macro verplaatst het probleem.
• De automatisering hangt aan een persoon of een pcAls het rapport niet klaar is omdat iemand met verlof is, is de macro je zwakste punt geworden.
• Je hebt rechten per persoon nodigExcel kent beveiligde bladen, geen rollen. Moet een technieker alleen zijn eigen dagen zien en de boekhouding alleen de uren, dan zit je aan de grens.

Zit je daar, dan is de keuze niet meer welke macro, maar welk systeem. Een tabel met formulieren in een tool zoals Airtable is een tussenstap; hoe die zich tot Excel verhoudt, staat in Airtable of Excel. Gaat het vooral om een planning, lees dan planning in Excel of software. Werk je al met een ERP zoals Odoo en wil je Excel daar laten aansluiten, dan helpt Odoo implementatie.