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

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

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

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

Шаблон

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

Откройте новый файл Excel. Переименуйте «Лист1» в «Диаграмма» и «Лист2» в «Данные».

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

управление	Ссылка на работу	пол
Дети и Семья	CH SW2588	F
Дети и Семья	Канал RS2775	F
Дети и Семья	CH SW2630	F
Дети и Семья	Канал RS2775	M
Дети и Семья	CH CC2628	F
Дети и Семья	CH HT2579	F
Здоровье общества	CW Т(2559	F
Здоровье общества	CW QS2774	F
Здоровье общества	КВ О2745	M
Окружающая среда	ЭЭ СМ2814	F
Окружающая среда	ЕЕ IT2772	M
Окружающая среда	ЕЕ SO2784	M
Полезные ресурсы	РС СО2557	F
Полезные ресурсы	РС HO2539	M

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

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

Удалите данные в D2:F15. Нет необходимости сбрасывать диапазон данных сводной таблицы; оставьте его заполненным, даже если данных нет.

Сохраните книгу как «VacancyTemplate.XLTX». в подкаталоге того, в котором должна находиться книга макросов. Отвечайте «Нет» на любые предупреждения Excel во время сохранения.

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

Отчеты Excel (xlsm хранится здесь)

|_Шаблоны (xlxt хранится здесь)

|_Отчеты (каждый xlsx сохранен здесь)

После сохранения как XLTX, закройте шаблон

Макро

Откройте новую книгу для хранения нашего кода. Переименуйте «Лист1» в «Основной» и «Лист2» в «База данных».

Поместите кнопку на «Главную», чтобы управлять приложением.

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

Скопируйте данные, найденные в start этой статьи во вкладку «База данных» на A1…

Кодекс

Структура кода ниже четко определяет процессы:

Получить данные из «базы данных»;
Откройте шаблон;
Заполните шаблон данными и сбросьте диапазон данных сводной таблицы;
Сохраните шаблон как отчет

Option Explicit 'Создать объекты для представления рабочей книги и листов шаблона 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 Вызов OpenTemplate Вызов PopulateTemplate 'Сохранить шаблон с отметкой даты xlsx При ошибке Возобновить следующий dDate = Format(Now(), "yyyy.mm.dd") wb.SaveAs Имя файла:=ActiveWorkbook.Path & "\Reports\Vacances" & dDate & ".xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False Sheets("Main").Activate 'Убрать вкладку базы данных wb.Activate 'Вывести диаграмму на передний план Set wb = Ничего Установить XL = Ничего Установить rs = Nothing Set connDB = Nothing End Sub Sub GetData() 'Эмуляция извлечения из базы данных If connDB.State = 1 Then connDB.Close Sheets("Database").Activate Sheets("Database").Range("A1").Выбрать выделение. End(xlDown).Select eRec = ActiveCell.Row - 1 'установить, сколько записей будет в наборе записей 'Этот шаг не потребуется в среде базы данных eRow = ActiveCell.Row 'конечная строка, используемая позже в шаблоне connDB.Open "Поставщик = Microsoft.ACE.OLEDB.12.0;" & _ "Источник данных=" & ActiveWorkbook.FullName & ";" & _ "Расширенные свойства = Excel 12.0;" Set rs = New ADODB.Recordset rs.Open "Выбрать верхнюю часть" & eRec & " * from [Database$]", connDB, , adCmdText End Sub Sub OpenTemplate() Set XL = CreateObject("Excel.Application") XL. Visible = True 'позволяет нам увидеть, что происходит при отладке. XL.Workbooks.Add ActiveWorkbook.Path & "\Templates\VacancyTemplate.xltx" Установите wb = XL.ActiveWorkbook 'на новую книгу ссылается "wb" End Sub Sub PopulateTemplate() wb.Sheets("Data").Activate wb .Sheets("Данные").Range("D2").CopyFromRecordset rs wb.Sheets("Данные").Range("A1").Выберите "изменить размер диапазона сводной таблицы, используя переменную eRow. wb.Sheets("Данные").Сводные таблицы("Сводная таблица1").ChangePivotCache wb. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C4:R" & eRow & "C6", _ Version:=xlPivotTableVersion14) wb.Sheets("Chart"). Выберите End Sub

Объекты данных ActiveX

Чтобы имитировать чтение базы данных, мы должны ссылаться на библиотеку Active X.rarу. Сделайте это через Tools>References из окна кода.

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

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

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

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

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

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

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

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

Об авторе:

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