Op deze pagina leggen we uit hoe je eventueel de gegevens (van de bestellingen) uit meerdere ontvangen Excel bestanden kunt verzamelen in één Excel bestand.

Voor het automatisch verzamelen van de gegevens gebruiken we een Excel bestand met daarin een macro. Dit Excel bestand is hier te downloaden en mag vrij gebruikt worden. De tabbladen in de Excel zijn beveiligd met een blanco (geen) wachtwoord.

Net als bij het voorzien van een besteloverzicht, nota, of factuur voor een formulier, is het eerste dat we doen is een extra tabblad toevoegen aan de Excel. Deze noemen we voor het gemak 'Rij'. Voor dit tabblad is de opmaak niet relevant: we houden dus de default opmaak van Excel aan.

We gebruiken kolom 'A' niet (mochten we ook gebruik maken de nota optie) en vullen kolom 'B' met de gegevensvelden en in kolom 'C' vullen we de formule in die de bijbehorende waardes uit het tabblad 'GEGEVENS' ophaalt.

De formule voor cel 'C1' wordt: =ALS(ISLEEG(GEGEVENS!D15);“”;GEGEVENS!D15)

De formule voor cel 'C2' wordt: =ALS(ISLEEG(GEGEVENS!J15);“”;GEGEVENS!J15)

etc.

Opmerking: De formule had ook simpelweg =GEGEVENS!D15 kunnen zijn. De extra formule hierboven zorgt ervoor dat als er niets is ingevuld in GEGEVENS!D15 er ook niets getoond wordt. Is er wel iets ingevuld dan wordt de waarde van GEGEVENS!D15 getoond.

We vullen de kolommen 'B' en 'C' verder aan met de producten, aantallen en totaalprijzen.

De formule voor cel 'C9' wordt: =TRUIEN!H15

De formule voor cel 'D9' wordt: =TRUIEN!K15

De formule voor cel 'C13' wordt: =BROEKEN!H15

De formule voor cel 'D13' wordt: =BROEKEN!K15

etc.

De lengte (aantal rijen) van deze lijst hangt natuurlijk af van het aantal items dat er op alle overige tabbladen wordt aangeboden. De kolommen 'C' en 'D' kunnen ook andere waardes hebben dan 'aantal' en 'totaalprijs' zoals in dit voorbeeld. Ook het aantal kolommen kan/mag afwijken.

Opmerking: Afhankelijk hoe de items in de tabbladen 'TRUIEN' en 'BROEKEN' zijn geplaatst, kun je de formules in 'C9' en 'D9' doorkopieren naar de overige cellen 'C10' tot 'C12' en 'D10' tot 'D12'.

We zijn nu klaar met dit tabblad. Het biedt nu een mooi overzicht van alle waardes die een klant invult en opstuurt bij een bestelling. Upload en converteer het Excel bestand nu naar een online bestelformulier zodat klanten bestellingen kunnen plaatsen.

Belangrijk: Zorg bij het converteren dat je niet het tabblad 'Rij' zichtbaar maakt. In dit geval kies je bij het converteren in stap 2 voor '3' als 'aantal tabbladen' zodat het 4de tabblad niet wordt geconverteerd.

Wanneer klanten een bestelling via uw online formulier plaatsen, ontvangt u als bijlage het Excel bestand met daarin de door de klant ingevuld gegevens. Uw Excel bestand bevat ook het tabblad 'Rij' in tegenstelling tot het Excel bestand dat de klant als bijlage ontvangt.

Sla alle Excel bestanden die u uiteindelijk in één overzicht Excel wilt hebben op in een folder.

Download de Excel met macro en open deze in Excel.

Opmerking: Het kan zijn dat u een beveiligingswaarschuwing te zien krijgt in Excel in verband met de macro. Klik in dat geval op 'Inhoud inschakelen' om de macro te kunnen gebruiken.

Het Excel bestand 'verzamel.xlsm' bevat de volgende 3 tabbladen:

• Start: hierin staat kort beschreven wat de macro doet, welke aanpassingen u kunt doorvoeren en wat de resultaten zijn wanneer de macro gebruikt is.
• Rij: dit tabblad is toegevoegd als voorbeeld tabblad zoals eerder beschreven in stap 1.
• verzameling: dit tabblad illustreert de uitkomst van de macro

Standaard variabelen voor lokatie van producten en waardes

Pas eventueel eerst de standaard variabelen aan in het tabblad 'Start' zodat deze kloppen met de tabblad zoals in stap 1 is aangemaakt. De standaard waarden 'Rij', 'B', 'C', '9' en '16' komen overeen met de waardes zoals in dit voorbeeld is beschreven. Heeft u bijvoorbeeld het tabblad niet 'Rij' maar 'Verzamel' genoemd, vul dan in K29 'Verzamel' in.

Opmerking: Wanneer u bepaalde variabelen heeft aangepast aan uw situatie, sla dan het Excel bestand op zodat deze ook voor de volgende keer bewaard worden.

Start de macro door op de blauwe knop 'start verzamelen' te klikken. Er verschijnt nu een pop-up waarmee u door uw harde schijf kunt navigeren om de folder met daarin alle Excel bestellingen te selecteren. Wanneer u de folder geselecteerd, klik op 'OK' waarna de macro de gegevens gaat verzamelen.

Opmerking: Sluit geen Excel bestanden af of maak geen wijzigingen aan Excel zolang de macro draait.

Wanneer de macro klaar is, heeft deze een nieuw Excel bestand gemaakt met daarin alle verzamelde gegevens. Dit bestand heeft als bestandsnaam 'verzameld_jjjjmmdd.xlsx' en wordt in eerder geselecteerde folder (met daarin de bestellingen) weggeschreven. In kolom 'B' worden de totalen per rij weergegeven, zoals te zien is in het voorbeeld hiernaast.

De macro heeft ook een samenvatting gemaakt die te zien is in de oorspronkelijke Excel (verzameling.xlsm):

• De datum waarop de macro is uitgevoerd
• De naam van het Excel bestand waarin alle gegevens zijn verzameld
• De folder met bestellingen die geselecteerd is
• Het totaal aantal Excel bestanden (*.xslx) die in de folder aanwezig zijn
• Het totaal aantal Excel bestanden waaruit succesvol gegevens zijn gehaald. Deze gegevens staan in de kolommen 'C', 'D' en 'E' in de nieuwe Excel
• Het totaal aantal Excel bestanden waaruit geen gegevens zijn gekopieerd omdat deze Excel bestanden hoogstwaarschijnlijk niet de juist tabblad naam hebben zoals in M29 is ingesteld
• Indien er Exel bestanden niet correct zijn verwerkt dan worden deze genoemd. In het voorbeeld is te zien dat Excel bestand 'bestelformulier_nota_3.xslx' niet mee is genomen in de samenvatting.