Как открыть и заполнить шаблон с помощью 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 листа «Данные», как показано ниже.Вставьте сводную таблицу в A1 листа «Данные»

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

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

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

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

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

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

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

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

Макро

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

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

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

Скопируйте данные, найденные в start этой статьи во вкладку «База данных» на A1…Скопируйте данные, найденные на 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 из окна кода.Ссылка на Active X Library

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

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

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

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

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

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

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

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

Об авторе:

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

Поделись сейчас:

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

  1. великолепный пost, очень информативно. Мне интересно, почему противоположные специалисты этого сектора этого не замечают. Вы должны продолжать писать. Я уверен, у вас уже есть отличная читательская база!

Оставьте комментарий

Ваш электронный адрес не будет опубликован. Обязательные поля помечены * *