Как можно загружать данные из Access в такие приложения, как Word и Excel. Но не о стандартном способе, который есть у Access (подключение к Office), а о способе, позволяющем выгружать данные в заданный шаблон и в Word, и в Excel.
Другими словами, это необходимо, когда невозможно или слишком трудоемко создать отчет в Access по уже существующему шаблону, например в Word. Как известно, отчет в Access может выглядеть просто коряво, или самый распространенный вариант, когда много текста, который в отчете Access не так хорошо отформатирован, как в Word, и данных не так много, а отчет должен быть автоматизирован, например это какие-то договоры, декларации и так далее.
Пользоваться слиянием из самого Word не очень удобно, поэтому можно заливать такие шаблоны прямо из Access нажатием одной кнопки.
Кроме загрузки в шаблон Word, иногда возникает необходимость выгрузки в шаблон Excel, и этот способ мы тоже сегодня рассмотрим.
Экспорт данных из Access в шаблон Word
Вся разработка делится на две части, это:
- Настройка шаблона Word;
- Настройка загрузки данных в шаблон.
Суть настройки шаблона заключается в том, чтобы проставить необходимые поля в тех местах шаблона, где вы хотите отображать те или иные данные. Это делается с помощью полей формы.
Откройте шаблон Word, сначала добавьте необходимую панель инструментов, для этого нажмите «Вид -> Панель инструментов» и отметьте «Формы». Теперь у вас отображается панель инструментов Shapes. Все, что осталось сделать, это вставить элементы «Текстового поля», доступные на панели инструментов, которую вы только что добавили, в места, где вы хотите отображать данные.
После добавления поля вы получите серую область, указывающую, что поле было добавлено. Теперь нужно указать имя этого поля, чтобы потом вставлять в него значения из доступа (название по умолчанию не очень удобно). Для этого щелкните поле правой кнопкой мыши и выберите пункт Свойства. В поле закладки пишешь желаемое имя для этого поля, в примере я назвал его MyTestPole.
Создайте столько полей, сколько вам нужно.
На этом настройка шаблона закончена, рекомендую сделать этот шаблон только для чтения, иначе пользователь возьмет его, сформирует документ и сохранит, и шаблон потеряется, а если сделать только для чтения, то у него не будет такого вариант, просто сохранить через "Сохранить как».
Перейдем к более интересной задаче, это реализация выгрузки из Access в этот шаблон на VBA.
Код VBA для выгрузки данных в шаблон Word
Допустим, у вас есть форма, создайте на ней кнопку (я назвал ее тестовой кнопкой) и вставьте следующий код VBA в событие нажатия кнопки:
Private Sub testbutton_Click() 'Объявляет переменные Dim FileDialog As FileDialog Dim rsd As ADODB.Recordset Dim strSQL As String Dim WordApOb As Object Dim WordOb As Object Dim path As String Set rsd = New ADODB.Recordset 'Запрос к базе данных для получения необходимых данных strSQL = "выберите * из dbo.table, где KOD = " & Me.kod & "" rsd.open strSQL, CurrentProject.Connection 'Выберите шаблон Set FileDialog = Application.FileDialog(msoFileDialogOpen) 'удалите дополнительные варианты, нам они не нужны FileDialog.AllowMultiSelect = False 'очистить и установить фильтры FileDialog.Filters.Clear FileDialog.Filters.add "Word", "*.doc" 'установить фильтр по умолчанию FileDialog.FilterIndex = 1 'проверить, что сделал пользователь, если он выбрал шаблон, то начинаем работу If FileDialog.Show = False Then 'Если нет, выходим Set dlgFile = Nothing Exit Sub End If 'получаем путь к файлу path = Trim(FileDialog.SelectedItems(1)) 'Очищаем переменную Set FileDialog = Nothing If path ""Then ' Давайте отследим В случае ошибки Перейти к Err_testbutton_Click 'Создать объект Word Set WordOb = CreateObject("Word.document") 'Установить для нашего документа значение из шаблона Set WordOb = GetObject(path) 'Установить значение для объекта word.Application Set WordApOb = WordPress Nz(rsd .Поля("поля"). Значение, "") 'и так далее по всем полям 'в конце переходим к началу нашего документа WordApOb.Selection.Goto wdGoToFirst' и активируем его WordApOb.Activate 'Очистить переменные Set WordOb = Nothing Set WordApOb = Nothing Exit_testbutton_Click: Exit Sub Err_testbutton_Click : MsgBox Err.Description 'в случае ошибки мы сделаем следующее 'закрыть слово без сохранения WordOb. Если конец под
Код закомментирован, так что проблем быть не должно. Здесь весь смысл в создании объекта word.document и word.application. А дальше мы уже работаем со своими объектами, т.е наполняем их.
Экспорт данных из Access в шаблон Excel
В шаблоне Excel больше не нужно создавать поля как в Word, так как здесь мы уже ориентируемся на адреса ячеек.
Существует несколько способов заполнения шаблона Excel, я опишу два, первый — когда нужно заполнить только несколько полей, т.е в источнике данных будет только одна строка с несколькими столбцами. Второй — когда линий уже несколько и вы точно не знаете сколько (зависит от некоторых условий). В стандартном шаблоне для этого отведены все пару строк, поэтому мы добавим нужные нам строки, чтобы наши данные не перекрывали строки ниже (допустим, это записка, подпись руководителя и так далее). И совет, вот, например, я использую только один источник данных, а вы, если вам нужно заполнить шапку, примечание и определенное количество строк (т.е диапазон данных), можете использовать несколько источников (Recordset).
Код VBA для выгрузки данных в шаблон Excel
Сначала добавьте на форму кнопку (я назвал ее testexcel) и вставьте следующий код в событие Button Click».
Private Sub testexcel_Click() 'Объявить переменные Dim XL As Object Dim XLT As Object Dim newrow As Object Dim rsd As ADODB.Recordset Dim strSQL As String Set rsd = New ADODB.Recordset 'Запрос к базе данных strSQL = "выбрать * из dbo.table, где kod = " & Me.kod & "" rsd.open strSQL, CurrentProject.Connection 'Создаем необходимые объекты Set XL = CreateObject("Excel.Application") 'Показываю для примера как можно сразу загрузить шаблон без выбора Set XLT = XL.Workbooks.open("C:\testfile.xls") '1 способ - если в источнике данных только одна строка With XLT.Worksheets("Sheet1") .[a1] = rsd.Fields(" field1") .[b1] = rsd.Fields("field2") .[c1] = rsd.Fields("field3") .[d1] = rsd.Fields("field4") End with '2 way - если есть больше строк в исходнике' и мы учтем тот факт, что у нас есть заголовок и примечание в Excel' и мы не знаем, сколько строк вставить' и поэтому мы добавим строки при необходимости' мы укажем из с какой строки мы должны начать вставлять данные Ro wss = 10 'для нумерации и numrow = 1 ' запускаем цикл, он будет работать до тех пор, пока не закончатся строки в нашем исходнике. While Not (rsd.EOF) 'проверить, есть ли строки больше, чем мы указали в шаблоне If Rowss >= 12 Then 'добавить строку XLT.Worksheets("Sheet1").Rows(Rowss).Insert 'запомнить наш набор строк newrow = XLT.Worksheets("Лист1").Rows(Rowss) 'и вставить копию предыдущей строки с объединенными ячейками или некоторыми необходимыми данными 'поскольку новая строка будет создана без объединения и XLT.Worksheets("Лист1") .Строки(Строки - 1). 'XLT.Worksheets("Лист1").Range("A10:F10").ClearContents динамически генерирует адрес нужной ячейки = "a" & Rowss 'и устанавливает значение XLT.Worksheets("Лист1"). Диапазон (ячейка) = числовая ячейка = «b» и Rowss XLT.Worksheets («Лист1»). Диапазон (ячейка) = rsd.Fields («поле5»). Значение 'перейти к следующей строке Rowss = Rowss + 1 'перейти к следующей строке в источнике данных rsd.MoveNext Else 'и это делается до тех пор, пока заданные строки в шаблоне не закончатся', т.е если есть только 1 строка в source мы даже не входим в приведенный выше код cell = "a" & Rowss XLT.Worksheets("Sheet1"). Диапазон (ячейка) = числовая ячейка = «b» и Rowss XLT.Worksheets («Лист1»). Range(cell) = rsd.Fields("field5").Value Rowss = Rowss + 1 rsd.MoveNext End Если 'для нумерации numrow = numrow + 1 'End loop Wend', это просто пример того, как удалить строку' XLT.Worksheets("Sheet1").Rows(20).Delete 'Сделать Excel видимым XL.Visible = True 'Удалить переменные Set XL = Ничего не установлено XLT = Ничего Установить новую строку = Ничего End Sub
