Microsoft Excel heeft verschillende functies en een daarvan is het automatisch genereren van rapporten. U kunt interactieve spreadsheets maken om het proces van het invoeren van gegevens voor anderen in de werkmap te vereenvoudigen, terwijl u ook het genereren van rapporten automatiseert. Voor beide functies is voldoende kennis van Visual Basic vereist. De stappen om beide taken uit te voeren worden hieronder beschreven.
Stap
Methode 1 van 2: Een interactieve spreadsheet maken
Stap 1. Bepaal de lay-out van het werkblad
De lay-out van de spreadsheet moet zo zijn dat anderen de velden kunnen vinden die nodig zijn om gegevens in te voeren.
Spreadsheetlay-outs kunnen horizontaal of verticaal worden opgemaakt. De meeste gebruikers vinden het gemakkelijker om met een verticale lay-out te werken, vooral als de spreadsheet wordt afgedrukt
Stap 2. Maak tekstlabels voor het werkblad
Schrijf een label bovenaan elke kolom en links van elke cel in de kolom die u als gegevensinvoer wilt gebruiken.
Stap 3. Druk tegelijk op de toetsen alt=""Image" en F11.</h4" />
Met deze toetsencombinatie wordt de Microsoft Visual Basic-editor geopend.
Stap 4. Dubbelklik op "Deze werkmap" in het deelvenster "Project-VBA-project" linksboven
Er verschijnt een venster voor het schrijven van code in het hoofdgedeelte van de editor.
Stap 5. Selecteer "Procedure" in het menu Invoegen
Het dialoogvenster Procedure toevoegen verschijnt.
Stap 6. Voer de naam van de procedure in het veld Naam in
Voer een betekenisvolle naam in voor de procedure, zoals "SumExpenses" als de spreadsheet wordt gebruikt om reiskosten te rapporteren. Klik op OK om het dialoogvenster te sluiten.
- Procedurenamen mogen geen spaties bevatten, maar kunnen een onderstrepingsteken (_) gebruiken om spaties te vervangen.
- Nadat het dialoogvenster Procedure toevoegen is gesloten, verschijnt er een regel met het label "Public Sub", gevolgd door de naam van de procedure. Onder die regel staat een spatie en de woorden 'End Sub'.
Stap 7. Voer de code in voor elk invoerveld in het werkblad
U schrijft twee regels code voor elk item.
- De eerste regel code heeft de vorm van "Bereik ("celnaam"). Selecteer", "celnaam" staat voor de cel waar de invoer is ingevoerd. Vul de naam in van de cel die direct rechts van het tekstlabel staat. Als het tekstlabel zich in cel A2 bevindt, is het invoerveld cel B2 (Bereik("B2"). Selecteren). De aanhalingstekens voor en na de celnaam worden in dit stadium nog steeds geschreven, maar zijn niet langer opgenomen in de volledige code-instructie.
- De code in de tweede regel is "ActiveCell. Value = InputBox ("InputPrompt")". "InputPrompt" staat voor de tekst die verschijnt om de gebruiker te informeren over het type gegevens dat in de invoercel moet worden ingevoerd. Als de invoercel bijvoorbeeld wordt gevuld met voedseluitgaven, vervangt u 'InputPrompt' door 'Voer totale uitgaven voor maaltijden in, inclusief fooien'. (De aanhalingstekens voor de invoerinstructietekst zijn nog steeds opgenomen, terwijl de aanhalingstekens voor en na de opdracht niet hoeven te worden geschreven.)
Stap 8. Voer de code in voor elk rekenveld
Wederom zijn de twee gebruikte regels dezelfde als voorheen, maar deze keer is ActiveCell. Value een berekening van een numerieke functie, bijvoorbeeld SUM, waarbij de functie InputBox-functie wordt gebruikt om invoerinstructies weer te geven.
Stap 9. Voeg een regel code toe om uw interactieve spreadsheet op te slaan
Het formaat is "ActiveWorkbook. SaveAs Filename:="Filename.xls". "Filename" is de naam van de interactieve spreadsheet. (De aanhalingstekens voor en na "Filename.xls" blijven geschreven, terwijl de aanhalingstekens voor alle commando's niet verplicht.)
Als u Microsoft Excel versie 2007 of later gebruikt, gebruikt u ".xlsx" in plaats van ".xls". Als er echter gebruikers van interactieve spreadsheets zijn met Excel 2003-versie en lager, kunnen ze de spreadsheet niet gebruiken zonder een reader-plug-in
Stap 10. Druk tegelijkertijd op de toetsen alt=""Image" en Q.</h4" />
De Visual Basic-editor wordt gesloten.
Stap 11. Druk tegelijkertijd op de toetsen alt=""Image" en F8.</h4" />
Het dialoogvenster Macro verschijnt.
Stap 12. Klik op de procedurenaam in de lijst Macro's
Als de gemaakte procedure de enige in de lijst is, wordt deze automatisch geselecteerd.
Stap 13. Klik op de knop Opties
U wordt gevraagd een toetsenbordteken in te voeren om als sneltoets te gebruiken met de Ctrl-toets. Kies een betekenisvolle letter die niet als sneltoets is gebruikt, zoals 'e' voor 'invoer'.
Stap 14. Klik op "OK" om het dialoogvenster Macro-opties te sluiten
Nu kunt u interactieve spreadsheets distribueren naar gebruikers. Na het openen kan de gebruiker de sneltoetsen gebruiken om gegevens in te voeren en de instructies volgen die u hebt gemaakt om de gegevens in te vullen.
Methode 2 van 2: Automatiseer het genereren van rapporten
Stap 1. Maak een rapport in een draaitabel
Draaitabellen zijn ontworpen om gegevens samen te vatten, zodat u cijfers kunt vergelijken en trends kunt identificeren. De draaitabel moet betrekking hebben op gegevens die zich in een van de gegevensverwerkers bevinden of uit een specifieke database zijn geïmporteerd.
Stap 2. Schrijf een Visual Basic-script om het rapport te openen en te sluiten
Het script moet de verschillende onderstaande functies kunnen uitvoeren. Elke functie wordt beschreven, gevolgd door de code tussen haakjes om deze te implementeren. Wanneer u daadwerkelijke code schrijft, schrijft u deze in een enkel blok, vervangt u de naam in het voorbeeld door uw eigen naam en plaatst u geen haakjes aan het begin en einde van het codevoorbeeld.
- Open de spreadsheet in alleen-lezen modus. [DIM XLAppSet XLApp = CreateObject("Excel. App")xlapp.visible=falsexlapp.workbooks.open \excelloc\filename.xls, 3,]
- Laad de gegevens opnieuw en sla het rapport op, in dit voorbeeld wordt het opgeslagen als een PDF met het datumbijschrift. [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, \pdfloc\reportname_ & DatePart("yyyy, Now()) & "-" & Right("0" & DatePart("m", Now()), 2) & "-" Right("0" & DatePart("d", Now()), 2) & ".pdf"] Als het uitvoerdocumentformaat anders is, vervangt u het ".pdf"-formaat door de juiste extensie naar wens.
- Sluit de spreadsheet zonder deze op te slaan en sluit vervolgens Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Gebruik ".xlsx" in plaats van ".xls" aan het einde van het werkblad als het werkblad is opgeslagen in Excel 2007 en een latere XML-indeling.
Stap 3. Schrijf een batchscript om het Visual Basic-script te starten
Het doel van het schrijven van het script is dat Visual Basic-scripts automatisch kunnen worden uitgevoerd. Zonder batchscript moeten VB-scripts handmatig worden uitgevoerd.
Het script is geschreven in de volgende indeling en vervangt door uw eigen mapnaam en bestandsnaam voor de naam die in dit voorbeeld wordt gegeven: [cscript /nologo \fileloc\script.vbs]
Stap 4. Schrijf een batchscript om ervoor te zorgen dat het uitvoerbestand bestaat zoals het is gemaakt
Uw script zou de onderstaande functies moeten uitvoeren. Elke functie wordt gevolgd door de code tussen haakjes om deze te implementeren. Wanneer u daadwerkelijke code schrijft, schrijft u deze in een enkel blok, vervangt u de naam in het voorbeeld door de naam die u nodig hebt, en plaatst u geen haakjes aan het begin en einde van de voorbeeldcode.
- Controleer of er een uitvoerbestand is. [For /f "tokens=2-4 delims=/ " %%a in ('date /t') do set rreport=reportname_%%c-%%a-%%b.pdf)] Als het uitvoerbestandsformaat is geen PDF, vervang ".pdf" door de juiste extensie.
- Als het uitvoerbestand/rapport bestaat, stuur het dan als een e-mail naar de persoon die het nodig heeft. [Indien aanwezig \pdfloc\%rreport% (sendmail -f [email protected] -t [email protected] -u Scheduled Report -m Report %%report% is bijgevoegd. -a \pdfloc\%rreport% -s uwserver:poort -xu gebruikersnaam -xp wachtwoord)]
- Als het uitvoerbestand/rapport niet bestaat op de opgegeven locatie, maakt u een procedure zodat het u een bericht kan sturen dat de levering is mislukt. [Anders (sendmail -f [email protected] -t [email protected] -u Rapport is niet uitgevoerd -m bestand %rreport% bestaat niet in \pdfloc\ -s uwserver:poort -xu gebruikersnaam -xp wachtwoord)]
Stap 5. Zorg ervoor dat de map "Desktop" op de computer staat
U moet het bestaan van de Desktop-map voor zowel 32-bits als 64-bits systemen controleren. Anders moeten Excel en de spreadsheet handmatig worden geopend.
- 32-bits systeemlocatie: c:\windows\system32\config\systemprofile
- 64-bits systeemlocatie: c:\windows\syswow64\config\systemprofile
Stap 6. Plan taken om indien nodig scripts uit te voeren
Batchscripts moeten opeenvolgend en continu worden uitgevoerd, zelfs als niemand de computer gebruikt. Bevoegdheden moeten op de hoogst mogelijke instelling worden ingesteld.
