Як відкрити та заповнити шаблон за допомогою Excel VBA

Шаблони Excel – це зазвичай робочі книги зі структурою звітності, яка часто підтримується функціями. Шаблони (xltx) можна використовувати знову і знову, не забруднюючи їх даними. Після заповнення даними робоча книга шаблону зберігається як xlsx, зберігаючи початковий стан самого xltx.

У цій вправі ми будемо використовувати код VBA, щоб відкрити та заповнити шаблон. Шаблон можна знайти тут і використовувані макроси Excel можна знайти тут.

У цій статті передбачається, що на пристрої для читання відображається стрічка розробника та він знайомий з редактором VBA. Якщо ні, будь ласка, перегляньте Google “Вкладка розробника Excel” або “Вікно коду Excel”.

Шаблон

Спочатку ми створимо фіктивний шаблон, заповнений даними, зведеною таблицею та діаграмою, як показано нижче:

Відкрийте новий файл Excel. Перейменуйте «Аркуш1» на «Діаграма», а «Аркуш2» — на «Дані»

Скопіюйте наступний текст, включаючи заголовки, у D1 вкладки «Дані»:

Виберіть усі дані, включаючи заголовки стовпців, і вставте зведену таблицю в A1 аркуша «Дані», як показано нижче.

Створіть діаграму на вкладці «Діаграма», використовуючи зведену таблицю як джерело даних.

Видаліть дані в D2:F15. Немає необхідності скидати діапазон даних зведеної таблиці; залиште його заповненим, навіть якщо немає даних.  

Збережіть книгу як «VacancyTemplate.xltx.” у підкаталозі того, у якому має розташовуватися книга макросів. Відповідайте «Ні» на будь-які сповіщення від Excel під час збереження.

Нам також знадобиться підкаталог Reports. Наприклад:

Звіти Excel (xlsm зберігається тут)

|_шаблони (xlxt зберігається тут)

       |_Звіти (кожен xlsx, збережений тут)

Після збереження як xltx, закрийте шаблон

Макрос

Відкрийте нову книгу, щоб зберегти наш код. Перейменуйте «Аркуш1» на «Основний», а «Аркуш2» на «Базу даних».

Розмістіть кнопку на «Головному», щоб керувати програмою.

Зазвичай дані витягуються з баз даних. Оскільки не всі мають під рукою базу даних, аркуш «База даних» буде емулювати таблицю бази даних.

Скопіюйте дані, знайдені на початку цієї статті, на вкладку «База даних» у розділі 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. Зробіть це через Інструменти>Посилання у вікні коду.

Перевірте код

Призначте кнопку «Головна» до Суб ажурний зошит. Збережіть книгу як «Заповнення шаблонів.xlsm».

ЗАКРИЙТЕ робочу книгу та відкрийте її знову.

Натисніть кнопку переглянути результат. Збільште кількість рядків даних у «Базі даних» і запустіть знову, щоб перевірити, чи оновлено діаграму додатковою інформацією.

У коді вище ми показали шаблон раннього, з XL.Visible = True. У прямому ефірі це можна зробити в самому кінці, щоб оновлення екрана не було видно.

Впорайтеся з катастрофою даних!

Мало що може дратувати більше, ніж збій файлу Excel, під час якого активно розробляється файл, пошкодження вихідного файлу та відсутність резервної копії. У таких випадках, коли Excel не вдається відновити пошкоджений файл, вся виконана над ним робота втрачається, якщо у вас немає під рукою інструменту для цього. виправити Excel файли.

Також доцільно часто створювати резервні копії важливої ​​роботи.

Вступ автора:

Фелікс Хукер - фахівець з відновлення даних у DataNumen, Inc., яка є світовим лідером у галузі технологій відновлення даних, в тому числі ремонт rar-файлів та програмні продукти для відновлення sql. Для отримання додаткової інформації відвідайте WWW.datanumen.com