Updaten van Excel sheets

Stel, u hebt in uw organisatie een Excel spreadsheet welke in ruim 100 uitvoeringen gebruikt wordt. Allemaal dezelfde look/feel/macrocode maar verschillende data. Bijvoorbeeld een timesheet per medewerker of een planning per afdeling of filiaal. Al die sheets hebben gemeen dat ze dezelfde layout en dezelfde VBA (Visual Basic for Applications, macro’s) programmering hebben. En die moeten behouden!

Nu wilt u in dat sheet een aanpassing doorvoeren zoals een formule aanpassen, een parameter waarde wijzigen of in de VBA programmering een fout verhelpen. Anything. Maar de gebruiker specifieke gegevens mogen niet aangetast worden, dat is een uitdaging. Best lastig om al die 100 sheets handmatig en foutloos door te moeten werken. Met name de aanpassingen op een scherm (kleur, opmaak, formules) zijn erg tijdrovend, foutgevoelig en geestdodend om te moeten toepassen. Dat moet eenvoudiger kunnen en het liefst automatisch.

Dit probleem had ik recent met de sheets bij een klant en ben daarop dieper in de materie gedoken. Het blijkt veel lastiger dan gedacht om een systeem te vinden die de sheets feilloos volledig kan updaten. Zou je alleen de VBA code willen aanpassen, dan is het mogelijk om een stuk programmatuur te schrijven die de VBA modules automatisch kan verversen: op afroep dan wel centraal gestuurd. Hiervoor heb ik generieke programmatuur geschreven waardoor je centraal gestuurd een update kunt doorvoeren, dan wel vanuit de lokale file een commando, bijvoorbeeld automatisch bij het openen van de sheet, kunt geven om de modules te verversen.

Echter, de betreffende 100 sheets moesten af en toe ook een cosmetische update onder gaan: kolommen verbergen, nieuwe formules in cellen plaatsen of teksten aanpassen enzovoort. Dat bleek veel lastiger te automatiseren. Ik heb daarom er voor gekozen om van het uitgezette spreadsheet centraal een masterversie te onderhouden. De update wordt vervolgens op een bepaald moment centraal gepushed naar alle uitvoeringen. Uiteindelijk wordt dus een kopie van die master mét de specifieke gebruikersgegevens, die ingekopieerd worden uit de operationele sheet, het nieuwe operationele sheet. Deze aanpak vereiste wel dat de gebruiker zeer beperkt is in de invoer van gegevens. Cellen en tabbladen waar hij niets te zoeken heeft, zijn met een wachtwoord afgeschermd. De gegevensstructuur is in feite dus in de code vastgelegd.

In de praktijk blijkt deze oplossing prima te functioneren. Het updateproces neemt voor de 100 sheets ongeveer 20 minuten in beslag (uiteraard afhankelijk van processortype en netwerksnelheid).

Hulp
JVGPRO heeft forse ervaring met Excel en kan assisteren bij het opzetten en implementeren van uw oplossing.