Как открыть и заполнить шаблон с помощью Excel VBA

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

В этом упражнении мы будем использовать код VBA для открытия и заполнения шаблона. Шаблон может быть найдено здесь и используемый макрос Excel можно найти здесь.

В этой статье предполагается, что у читателя отображается лента «Разработчик» и он знаком с редактором VBA. Если нет, погуглите «Excel Developer Tab» или «Excel Code Window».

Шаблон

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

Откройте новый файл 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

Для имитации чтения из базы данных необходимо добавить ссылку на библиотеку ActiveX. Это можно сделать через меню «Инструменты» > «Ссылки» в окне кода.

Проверить код

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

ЗАКРЫТЬ книгу и снова открыть ее.

Нажимаем кнопку посмотреть результат. Увеличьте количество строк данных в «Базе данных» и запустите снова, чтобы увидеть, была ли диаграмма обновлена ​​​​дополнительной информацией.

В приведенном выше коде мы показали шаблон раньше, с XL.Видимый = Истина. В живой среде это можно сделать в самом конце, чтобы обновления экрана не были видны.

Справьтесь с информационной катастрофой!

Мало что может быть более неприятным, чем сбой в работе тщательно подготовленного файла Excel, повреждение исходного файла и отсутствие резервной копии. В таких случаях, когда Excel не удаётся восстановить повреждённый файл, вся проделанная работа с ним теряется, если у вас нет под рукой инструмента для восстановления. исправить Excel файлы.

Также целесообразно часто создавать резервные копии ценной работы.

Об авторе:

Феликс Хукер — эксперт по восстановлению данных в DataNumen, Inc., которая является мировым лидером в области технологий восстановления данных, включая ремонт rar и программные продукты для восстановления sql. Для получения дополнительной информации посетите www.datanumen.com