Как да отворите и попълните шаблон с Excel VBA

Шаблоните на Excel обикновено са работни книги с рамка за отчитане, често поддържана от функции. Шаблоните (xltx) могат да се използват отново и отново, без да го замърсяват с данни. След популация с данни, работна книга на шаблон се записва като xlsx, запазвайки девственото състояние на самия xltx.

В това упражнение ще използваме VBA код за отваряне и попълване на шаблон. Шаблонът могат да бъдат намерени тук и използвания макрос на Excel може да бъде намерен тук.

Тази статия предполага, че на читателя е показана лентата за програмисти и е запознат с редактора на VBA. Ако не, моля Google „Раздел за програмисти на Excel“ или „Прозорец на кода на Excel“.

Шаблонът

Първо ще изградим фиктивен шаблон, попълнен с данни, обобщена таблица и диаграма, както следва:

Отворете нов файл на Excel. Преименувайте „Sheet1“ като „Chart“ и „Sheet2“ като „Data“

Копирайте следния текст, включително заглавията, в D1 на раздела „Данни“:

Изберете всички данни, включително заглавията на колони, и вмъкнете обобщена таблица в А1 на листа с данни, както е показано по-долу.

Създайте диаграма в раздела „Chart“, като използвате обобщената таблица като източник на данни.

Премахнете данните в D2: F15. Не е необходимо да нулирате обхвата на данните на обобщената таблица; оставете го попълнено, дори ако няма данни.  

Запазете работната книга като „VacancyTemplate.xltx. " в поддиректория на тази, в която трябва да се намира работната книга на макросите. Отговорете с „Не“ на предупреждения от Excel по време на записването.

Ще ни е необходима и поддиректория Reports. Например:

Отчети на Excel (xlsm се съхранява тук)

| _Шаблони (xlxt се съхранява тук)

       | _Доклади (всеки xlsx запазен тук)

След като бъде запазен като xltx, затворете шаблона

Макросът

Отворете нова работна книга, за да съхраните нашия код. Преименувайте „Sheet1“ като „Main“ и „Sheet2“ като „Database“.

Поставете бутон върху „Основно“, за да управлявате приложението.

Обикновено данните се извличат от бази данни. Тъй като не всеки има база данни под ръка, листът „База данни“ ще емулира таблица на базата данни.

Копирайте данните, намерени в началото на тази статия, в раздела „База данни“ на A1…

Кодексът

Структурата на кода по-долу ясно дефинира процесите:

• Вземете данните от „базата данни“;
• Отворете шаблона;
• Попълнете шаблона с данните и нулирайте диапазона от данни на обобщената таблица;
• Запазете шаблона като отчет

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

ActiveX обекти с данни

За да симулираме четене от база данни, трябва да се обърнем към библиотеката Active X. Направете това чрез Tools>References от прозореца с кода.

Тествайте кода

Задайте бутона на „Основно“ на Под Ажурна книга. Запазете работната книга като „Попълване на шаблони.xlsm“.

ЗАКРЕТЕТЕ работната книга и я отворете отново.

Натиснете бутона, за да видите резултата. Увеличете броя на редовете с данни в „База данни“ и стартирайте отново, като видите дали диаграмата е актуализирана с допълнителната информация.

В горния код показахме шаблона рано, с XL.Visible = Вярно. В средата на живо това може да се направи в самия край, така че актуализациите на екрана да не се виждат.

Справете се с бедствие с данни!

Малко неща са по-разочароващи от това, когато силно разработен Excel файл се срине, повреди изходния файл и няма налично резервно копие. В случаите, когато Excel не успее да възстанови повредения файл, цялата работа, извършена върху него, се губи, освен ако нямате под ръка инструмент. поправете Excel файлове.

Също така е разумно често да архивирате ценна работа.

Въведение на автора:

Феликс Хукър е експерт по възстановяване на данни в DataNumen, Inc., която е световен лидер в технологиите за възстановяване на данни, включително ремонт на RAR файлове и sql софтуерни продукти за възстановяване. За повече информация посетете WWW.datanumen.com