Jak otworzyć i wypełnić szablon za pomocą Excel VBA

Szablony programu Excel to zazwyczaj skoroszyty z ramą raportowania, często obsługiwane przez funkcje. Szablony (xltx) mogą być używane wielokrotnie, bez zanieczyszczania go danymi. Po wypełnieniu danymi skoroszyt szablonu jest zapisywany jako plik xlsx, zachowując pierwotny stan samego xltx.

W tym ćwiczeniu użyjemy kodu VBA do otwarcia i wypełnienia szablonu. Szablon można znaleźć w tym miejscu i używane makro programu Excel można znaleźć w tym miejscu.

W tym artykule założono, że czytelnik ma wyświetloną wstążkę programisty i zna edytor VBA. Jeśli nie, wybierz Google „Excel Developer Tab” lub „Excel Code Window”.

Szablon

Najpierw zbudujemy fikcyjny szablon wypełniony danymi, tabelą przestawną i wykresem w następujący sposób:

Otwórz nowy plik Excel. Zmień nazwę „Arkusz1” na „Wykres” i „Arkusz2” na „Dane”

Skopiuj następujący tekst wraz z nagłówkami do D1 zakładki „Dane”:

Wybierz wszystkie dane, w tym nagłówki kolumn, i wstaw tabelę przestawną w A1 arkusza „Dane”, jak pokazano poniżej.

Utwórz wykres na karcie „Wykres”, używając tabeli przestawnej jako źródła danych.

Usuń dane w D2: F15. Nie ma potrzeby resetowania zakresu danych tabeli przestawnej; pozostaw je wypełnione, nawet jeśli nie ma danych.  

Zapisz skoroszyt jako „VacancyTemplate.xltx. ” w podkatalogu tego, w którym ma znajdować się skoroszyt makr. Odpowiedz „Nie” na wszelkie alerty programu Excel podczas zapisywania.

Będziemy również potrzebować podkatalogu Raporty. Na przykład:

Raporty programu Excel (xlsm przechowywany tutaj)

| _Szablony (xlxt przechowywany tutaj)

       | _Reports (każdy zapisany plik xlsx tutaj)

Po zapisaniu jako xltxzamknij szablon

Makro

Otwórz nowy skoroszyt do przechowywania naszego kodu. Zmień nazwę „Sheet1” na „Main” i „Sheet2” na „Database”.

Umieść przycisk na „Głównym”, aby sterować aplikacją.

Zwykle dane są pobierane z baz danych. Ponieważ nie każdy ma pod ręką bazę danych, arkusz „Baza danych” będzie emulować tabelę bazy danych.

Skopiuj dane znalezione na początku artykułu do zakładki „Baza danych” na stronie A1…

Kod

Poniższa struktura kodu jasno definiuje procesy:

• Pobierz dane z „bazy danych”;
• Otwórz szablon;
• Wypełnij szablon danymi i zresetuj zakres danych tabeli przestawnej;
• Zapisz szablon jako raport

Option Explicit
    'Create objects to represent the template workbook and worksheets
    Public wb As Object
    Public XL As Object
    Public connDB As New ADODB.Connection
    Public rs As ADODB.Recordset
    Public eRow As Integer
    Public eRec As Integer
    Public dDate As String

Sub openWorksheet()
    Call GetData
    Call OpenTemplate
    Call PopulateTemplate
    
    'Save template as datestamped xlsx
    On Error Resume Next
    dDate = Format(Now(), "yyyy.mm.dd")
    wb.SaveAs Filename:=ActiveWorkbook.Path & "\Reports\Vacancies" & dDate & ".xlsx", _
         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

     Sheets("Main").Activate        'Move off the database tab
     wb.Activate            'Bring the chart to the fore
     Set wb = Nothing
     Set XL = Nothing
     Set rs = Nothing
     Set connDB = Nothing
End Sub

Sub GetData()
    'Emulate database retrieval
    If connDB.State = 1 Then connDB.Close
    Sheets("Database").Activate
    Sheets("Database").Range("A1").Select
    Selection.End(xlDown).Select
    
    eRec = ActiveCell.Row - 1 'establish how many records will be in the recordset
                     'This step won't be needed in a database environment
    eRow = ActiveCell.Row   'the end row, used later in the template
    
    connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Data Source=" & ActiveWorkbook.FullName & ";" & _
      "Extended Properties=Excel 12.0;"
        
    Set rs = New ADODB.Recordset
    rs.Open "Select top " & eRec & " * from [Database$]", connDB, , , adCmdText
End Sub

 Sub OpenTemplate()
    Set XL = CreateObject("Excel.Application")
    XL.Visible = True       'enables us to see what's happening on debug.
    XL.Workbooks.Add ActiveWorkbook.Path & "\Templates\VacancyTemplate.xltx"
    Set wb = XL.ActiveWorkbook             'the new workbook is referenced by "wb"
 End Sub
 
 Sub PopulateTemplate()
    wb.Sheets("Data").Activate
    wb.Sheets("Data").Range("D2").CopyFromRecordset rs
    wb.Sheets("Data").Range("A1").Select
    
    'resize the range driving the pivot table, using the eRow variable.
    wb.Sheets("Data").PivotTables("PivotTable1").ChangePivotCache wb. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C4:R" & eRow & "C6", _
        Version:=xlPivotTableVersion14)
    wb.Sheets("Chart").Select

 End Sub

Obiekty danych ActiveX

Aby zasymulować odczyt bazy danych, musimy odwołać się do biblioteki Active X. Można to zrobić w oknie kodu, wybierając Narzędzia>Odwołania.

Przetestuj kod

Przypisz przycisk „Main” do Sub Openworkbook. Zapisz skoroszyt jako „Populating Templates.xlsm”.

ZAMKNIJ skoroszyt i otwórz go ponownie.

Naciśnij przycisk, aby zobaczyć wynik. Zwiększ liczbę wierszy danych w „Bazie danych” i uruchom ponownie, sprawdzając, czy Wykres został zaktualizowany o dodatkowe informacje.

W powyższym kodzie wcześnie pokazaliśmy szablon z XL.Widoczny = Prawda. W środowisku na żywo można to zrobić na samym końcu, aby aktualizacje ekranu nie były widoczne.

Radzić sobie z katastrofą danych!

Niewiele rzeczy jest bardziej frustrujących niż awaria rozbudowanego pliku Excela, uszkodzenie pliku źródłowego i brak kopii zapasowej. W takich przypadkach, gdy Excel nie jest w stanie odzyskać uszkodzonego pliku, cała praca nad nim zostaje utracona, chyba że masz pod ręką narzędzie, które pomoże Ci go odzyskać. naprawić program Excel akta.

Rozsądne jest również częste tworzenie kopii zapasowych wartościowych prac.

Wprowadzenie autora:

Felix Hooker jest ekspertem w dziedzinie odzyskiwania danych w DataNumen, Inc., która jest światowym liderem w technologiach odzyskiwania danych, w tym naprawa rar i oprogramowanie do odzyskiwania sql. po więcej informacji odwiedź www.datanumen.com