Microsoft Excel verfügt über verschiedene Funktionen und eine davon ist die automatische Erstellung von Berichten. Sie können interaktive Tabellen erstellen, um die Eingabe von Daten für andere in die Arbeitsmappe zu vereinfachen und gleichzeitig die Berichterstellung zu automatisieren. Beide Features erfordern ausreichende Kenntnisse von Visual Basic. Die Schritte zum Ausführen beider Aufgaben werden im Folgenden beschrieben.
Schritt
Methode 1 von 2: Erstellen einer interaktiven Tabelle
Schritt 1. Bestimmen Sie das Layout der Kalkulationstabelle
Das Layout der Tabelle muss so gestaltet sein, dass andere die Felder finden können, die zum Eingeben von Daten erforderlich sind.
Tabellenkalkulationslayouts können horizontal oder vertikal angeordnet werden. Die meisten Benutzer finden es einfacher, mit einem vertikalen Layout zu arbeiten, insbesondere wenn die Tabelle gedruckt wird
Schritt 2. Erstellen Sie Textbeschriftungen für die Tabelle
Schreiben Sie eine Beschriftung oben in jede Spalte und links neben jede Zelle in der Spalte, die Sie als Dateneingabe verwenden möchten.
Schritt 3. Drücken Sie gleichzeitig die Tasten und F11
Diese Tastenkombination öffnet den Microsoft Visual Basic-Editor.
Schritt 4. Doppelklicken Sie oben links im Bereich "Projekt-VBA-Projekt" auf "Diese Arbeitsmappe"
Im Hauptbereich des Editors erscheint ein Fenster zum Schreiben von Code.
Schritt 5. Wählen Sie "Prozedur" aus dem Menü Einfügen
Das Dialogfeld Prozedur hinzufügen wird angezeigt.
Schritt 6. Geben Sie den Namen der Prozedur in das Feld Name ein
Geben Sie einen aussagekräftigen Namen für das Verfahren ein, z. B. "SumExpenses", wenn die Tabelle zur Abrechnung von Reisekosten verwendet wird. Klicken Sie auf OK, um das Dialogfeld zu schließen.
- Prozedurnamen dürfen keine Leerzeichen enthalten, können jedoch einen Unterstrich (_) verwenden, um Leerzeichen zu ersetzen.
- Nachdem das Dialogfeld Prozedur hinzufügen geschlossen wurde, wird eine Zeile mit der Bezeichnung "Public Sub" angezeigt, gefolgt vom Namen der Prozedur. Unter dieser Zeile befindet sich ein Leerzeichen und die Worte "End Sub".
Schritt 7. Geben Sie den Code für jedes Eingabefeld in der Tabelle ein
Sie schreiben für jeden Eintrag zwei Codezeilen.
- Die erste Codezeile hat die Form "Range("cellname"). Select", "cellname" steht für die Zelle, in die die Eingabe eingegeben wird. Geben Sie den Namen der Zelle ein, die sich direkt rechts neben der Textbeschriftung befindet. Wenn sich die Textbeschriftung in Zelle A2 befindet, ist das Eingabefeld Zelle B2 (Range("B2"). Select). Die Anführungszeichen vor und nach dem Zellnamen werden zu diesem Zeitpunkt noch geschrieben, sind jedoch nicht mehr in der vollständigen Code-Anweisung enthalten.
- Der Code in der zweiten Zeile lautet "ActiveCell. Value = InputBox("InputPrompt")". "InputPrompt" stellt den Text dar, der angezeigt wird, um den Benutzer über den Datentyp zu informieren, der in die Eingabezelle einzugeben ist. Wenn die Eingabezelle beispielsweise mit Essensausgaben gefüllt wird, ersetzen Sie "InputPrompt" durch "Geben Sie die Gesamtausgaben für Mahlzeiten ein, einschließlich Trinkgelder". (Die Anführungszeichen für den Text der Eingabeanweisung sind weiterhin enthalten, während die Anführungszeichen vor und nach dem Befehl nicht geschrieben werden müssen.)
Schritt 8. Geben Sie den Code für jedes Berechnungsfeld ein
Auch hier sind die beiden verwendeten Zeilen die gleichen wie zuvor, aber diesmal ist ActiveCell. Value eine Berechnung einer numerischen Funktion, zum Beispiel SUM, wobei die Funktion InputBox verwendet wird, um Eingabeanweisungen anzuzeigen.
Schritt 9. Fügen Sie eine Codezeile hinzu, um Ihre interaktive Tabelle zu speichern
Das Format ist "ActiveWorkbook. SaveAs Filename:="Filename.xls". "Filename" ist der Name der interaktiven Tabellenkalkulation. (Die Anführungszeichen vor und nach "Filename.xls" bleiben geschrieben, während die Anführungszeichen für alle Befehle sind nicht benötigt.)
Wenn Sie Microsoft Excel Version 2007 oder höher verwenden, verwenden Sie ".xlsx" anstelle von ".xls". Benutzer von interaktiven Tabellenkalkulationen mit Excel 2003-Version und niedriger können die Tabellenkalkulation jedoch nicht ohne ein Reader-Plug-in verwenden
Schritt 10. Drücken Sie gleichzeitig die Tasten und Q
Der Visual Basic-Editor wird geschlossen.
Schritt 11. Drücken Sie gleichzeitig die Tasten und F8
Das Dialogfeld Makro wird angezeigt.
Schritt 12. Klicken Sie in der Liste Makros auf den Prozedurnamen
Wenn das erstellte Verfahren das einzige in der Liste ist, wird es automatisch ausgewählt.
Schritt 13. Klicken Sie auf die Schaltfläche Optionen
Sie werden aufgefordert, ein Tastaturzeichen einzugeben, das Sie mit der Strg-Taste als Tastenkombination verwenden können. Wählen Sie einen aussagekräftigen Buchstaben, der nicht als Abkürzungszeichen verwendet wurde, z. B. "e" für "Eintrag".
Schritt 14. Klicken Sie auf "OK", um das Dialogfeld Makrooptionen zu schließen
Jetzt können Sie interaktive Tabellen an Benutzer verteilen. Nach dem Öffnen kann der Benutzer mit den Tastenkombinationen Eingaben vornehmen und den von Ihnen erstellten Anweisungen zum Ausfüllen der Daten folgen.
Methode 2 von 2: Berichtserstellung automatisieren
Schritt 1. Erstellen Sie einen Bericht in einer PivotTable
PivotTables dienen dazu, Daten zusammenzufassen, damit Sie Zahlen vergleichen und Trends erkennen können. Die PivotTable muss sich auf Daten beziehen, die sich in einem der Datenprozessoren befinden oder aus einer bestimmten Datenbank importiert wurden.
Schritt 2. Schreiben Sie ein Visual Basic-Skript, um den Bericht zu öffnen und zu schließen
Das Skript sollte die verschiedenen unten aufgeführten Funktionen ausführen können. Jede Funktion wird beschrieben, gefolgt von dem in Klammern angegebenen Code, um sie zu implementieren. Wenn Sie eigentlichen Code schreiben, schreiben Sie ihn in einen einzelnen Block, ersetzen Sie den Namen im Beispiel durch Ihren eigenen und schließen Sie am Anfang und Ende des Codebeispiels keine Klammern ein.
- Öffnen Sie die Tabelle im schreibgeschützten Modus. [DIM XLAppSet XLApp = CreateObject("Excel. App")xlapp.visible=falsexlapp.workbooks.open \excelloc\filename.xls, 3,]
- Laden Sie die Daten erneut und speichern Sie den Bericht, in diesem Beispiel wird er als PDF mit der Datumsüberschrift gespeichert. [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, \pdfloc\reportname_ & DatePart("yyyy, Now()) & "-" & Right("0" & DatePart("m", Now()), 2) & "-" Right("0" & DatePart("d", Now()), 2) & ".pdf"] Wenn das Ausgabedokumentformat unterschiedlich ist, ersetzen Sie das Format ".pdf" durch die gewünschte Erweiterung.
- Schließen Sie die Tabelle, ohne sie zu speichern, und schließen Sie dann Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Verwenden Sie ".xlsx" anstelle von ".xls" am Ende der Kalkulationstabelle, wenn die Kalkulationstabelle in Excel 2007 und einem späteren XML-basierten Format gespeichert ist.
Schritt 3. Schreiben Sie ein Batch-Skript, um das Visual Basic-Skript zu starten
Der Zweck des Schreibens des Skripts besteht darin, dass Visual Basic-Skripts automatisch ausgeführt werden können. Ohne Batch-Skript müssen VB-Skripte manuell ausgeführt werden.
Das Skript ist im folgenden Format geschrieben, wobei Sie den in diesem Beispiel angegebenen Namen durch Ihren eigenen Ordnernamen und Dateinamen ersetzen: [cscript /nologo \fileloc\script.vbs]
Schritt 4. Schreiben Sie ein Batch-Skript, um sicherzustellen, dass die Ausgabedatei wie erstellt vorhanden ist
Ihr Skript sollte die folgenden Funktionen ausführen. Auf jede Funktion folgt der in Klammern angegebene Code, um sie zu implementieren. Wenn Sie eigentlichen Code schreiben, schreiben Sie ihn in einen einzelnen Block, ersetzen Sie den Namen im Beispiel durch den Namen, den Sie benötigen, und schließen Sie am Anfang und Ende des Beispielcodes keine Klammern ein.
- Überprüfen Sie, ob eine Ausgabedatei vorhanden ist. [Für /f "tokens=2-4 delims=/ " %%a in ('date /t') setzen Sie rreport=reportname_%%c-%%a-%%b.pdf)] Wenn das Ausgabedateiformat kein PDF ist, ersetzen Sie ".pdf" durch die entsprechende Erweiterung.
- Wenn die Ausgabedatei/der Bericht vorhanden ist, senden Sie sie als E-Mail an die Person, die sie benötigt. [Falls vorhanden \pdfloc\%rreport% (sendmail -f [email protected] -t [email protected] -u Geplanter Bericht -m Bericht %%report% ist angehängt. -a \pdfloc\%rreport% -s deinserver:port -xu benutzername -xp passwort)]
- Wenn die Ausgabedatei/der Bericht am angegebenen Speicherort nicht vorhanden ist, erstellen Sie eine Prozedur, damit Sie eine Nachricht senden können, dass die Zustellung fehlgeschlagen ist. [Sonst (sendmail -f [email protected] -t [email protected] -u Bericht wurde nicht ausgeführt -m file %rreport% existiert nicht in \pdfloc\ -s yourserver:port -xu username -xp password)]
Schritt 5. Stellen Sie sicher, dass sich der Ordner "Desktop" auf dem Computer befindet
Sie müssen das Vorhandensein des Desktop-Ordners sowohl für 32-Bit- als auch für 64-Bit-Systeme überprüfen. Andernfalls müssen Excel und die Tabellenkalkulation manuell geöffnet werden.
- 32-Bit-Systemspeicherort: c:\windows\system32\config\systemprofile
- 64-Bit-Systemspeicherort: c:\windows\syswow64\config\systemprofile
Schritt 6. Planen Sie Aufgaben zum Ausführen von Skripts nach Bedarf
Batch-Skripte müssen sequentiell und kontinuierlich ausgeführt werden, auch wenn niemand den Computer benutzt. Privilegien müssen auf die höchstmögliche Einstellung gesetzt werden.
