Экспорт access в excel: Импорт или связывание данных в книге Excel

Выгрузка данных из Access в шаблон Word и Excel | Info-Comp.ru

Всем привет, сегодня мы поговорим о том, как можно выгрузить данные из Access в такие приложения как Word и Excel. Но не о стандартном способе, который есть в  Access (связь с Office), а о способе, который позволяет выгружать данные в заданный шаблон как в Word, так и в Excel.

Другими словами, это нужно тогда, когда создать отчет в Access по шаблону, который уже существует, например, в Word, невозможно или слишком трудоемко. Как Вы знаете, отчет в Access может выводиться просто коряво или, самый распространенный вариант, это когда много текста, который в отчете Access не так хорошо форматируется как в Word, а данных не так много, но отчет необходимо автоматизировать, например это какие-то договора, заявления и так далее.

Использование слияния из самого Word-а не очень удобно, поэтому сегодня я расскажу, как можно заполнять такие шаблоны напрямую из Access, путем нажатия на одну кнопку.

Кроме выгрузки в шаблон Word, иногда возникает и необходимость выгрузки в шаблон Excel, и этот способ мы тоже сегодня рассмотрим.

Содержание

  1. Экспорт данных из Access в шаблон Word
  2. Код VBA для выгрузки данных в шаблон Word
  3. Экспорт данных из Access в шаблон Excel
  4. Код VBA для выгрузки данных в шаблон Excel

Экспорт данных из Access в шаблон Word

Вся разработка делится на две части, это:

  • Настройка шаблона Word;
  • Настройка выгрузки данных в шаблон.

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

Примечание! Я использую Microsoft Word 2003.

Открываем шаблон Word, для начала добавим необходимую панель инструментов, для этого нажимаем «Вид -> Панель инструментов» и ставим галочку «Формы». Теперь у Вас отобразилась панель инструментом «Формы». Все, что осталось сделать — это вставить в местах, в которых необходимо выводить данные, элементы «Текстовое поле», которые доступны на только что добавленной панели инструментов.

После добавления поля, у Вас появится серая область, которая свидетельствует о том, что поле добавлено. Теперь необходимо задать имя этого поля, для того чтобы потом из access вставлять в него значения (стандартное названия не очень удобное). Для этого щелкните правой кнопкой мыши по полю и нажмите «Свойства». В поле закладка напишите желаемое имя этого поля, я в примере назвал его MyTestPole.


Создайте столько полей, сколько Вам нужно.

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

Переходим к более интересной задачи, это к реализации самой выгрузки из Access в этот шаблон на VBA.

Примечание! Я использую Access в связке с MS SQL 2008, поэтому и данные буду брать от туда.

Код VBA для выгрузки данных в шаблон Word

Допустим, у Вас есть форма, сделайте на ней кнопку (я назвал ее testbutton) и в событие нажатие кнопки вставьте следующий код 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 = "select * from dbo.table where 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
   
   'проверяем, что сделал user, если выбрал шаблон, то начинаем работу
   If FileDialog.Show = False Then
     'Если нет, то выходим
     Set dlgFile = Nothing
     Exit Sub
   End If
   
   'получаем путь к файлу
   path = Trim(FileDialog.SelectedItems(1))
   
   'Очистим переменную
   Set FileDialog = Nothing
   If path <> "" Then
   
   'Будем отслеживать ошибки
   On Error GoTo Err_testbutton_Click
   
   'Создаем объект Word
   Set WordOb = CreateObject("Word.document")
   
   'Задаем нашему документу значение из шаблона
   Set WordOb = GetObject(path)
   
   'Задаем значение объекту word. Application
   Set WordApOb = WordOb.Parent
   
   'делаем приложение word видимым
   WordApOb.Visible = True
   
   'ищем наше поле в шаблоне
   WordOb.Bookmarks("mytestpole").Select
   
   'задаем ему новое значение из нашего Recordset
   WordApOb.Selection.TypeText Text:=Nz(rsd.Fields("field").Value, " ")
   'и так далее по всем полям
   
   'в конце перейдем на начало нашего документа
   WordApOb.Selection.Goto wdGoToFirst
   'и активируем его
   WordApOb.Activate
   
   'Очистим переменные
   Set WordOb = Nothing
   Set WordApOb = Nothing
   
   Exit_testbutton_Click:
     Exit Sub
   
   Err_testbutton_Click:
     MsgBox Err.Description
     'в случае ошибки будем делать следующие
     'закроем word без сохранения
     WordOb.Close (wddonotsavechanges)
     WordApOb.Quit
    'и также очистим переменные
     Set WordOb = Nothing
     Set WordApOb = Nothing
     Resume Exit_testbutton_Click
    End If
   
   End Sub

Код прокомментирован, поэтому сложностей возникнуть не должно. Здесь весь смысл сводится к созданию объекта word.document и word.application. А после мы уже работаем с нашими объектами, т.е. заполняем их.

Экспорт данных из Access в шаблон Excel

В шаблоне Excel уже не нужно создавать поля как в Word, так как здесь мы уже будем ориентироваться по адресам ячеек.

Существует несколько способов, как заполнять Excel шаблон, я опишу два, первый — это тогда, когда Вам просто необходимо проставить несколько полей, т.е. в источнике данных будет всего одна строка с несколькими столбцами. Второй — это когда строк будет уже несколько, причем Вы не знаете, сколько именно (в зависимости от каких то условий). В шаблоне по умолчанию отведено для этого все пару строк, поэтому мы будем нужные нам строки добавлять, для того чтобы наши данные не накладывалась на строки ниже (допустим там примечание, подпись руководителя и т.д.). И совет, я здесь, для примера, использую всего один источник данных, а Вы, если Вам необходимо заполнить шапку, примечание и некое количество строк (т. е. область данных), можете использовать несколько источников (Recordset).

Код VBA для выгрузки данных в шаблон Excel

Сначала добавьте кнопку на форму (я ее назвал testexcel) и вставьте следующий код в событие «Нажатие кнопки».

   
   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 = "select * from dbo.table where 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("Лист1")
              .[a1] = rsd.Fields("field1")
              .[b1] = rsd.Fields("field2")
              .[c1] = rsd.Fields("field3")
              .[d1] = rsd.Fields("field4")
            End With
   
   '2 способ - если строк в источнике несколько
   'причем мы учтем то, что у нас есть шапка и примечание в Excel
   'и мы не знаем, сколько строк у нас вставится 
   'и поэтому строки будем добавлять в случае необходимости
   'зададим, с какой строки будем начинать вставлять данные
   Rowss = 10
   'для нумерации
   numrow = 1
   'запускаем цикл, он будет работать до тех пор, пока не закончатся строки в нашем источнике
   While Not (rsd.EOF)
      'смотрим, если строк больше чем мы задали в шаблоне
      If Rowss >= 12 Then
          'то добавляем строку
         XLT.Worksheets("Лист1").Rows(Rowss).Insert
          'Запомним нашу строку
         Set newrow = XLT. Worksheets("Лист1").Rows(Rowss)
          'и вставим туда копию предыдущей строки
          'для того если вдруг у вас там есть объединенные ячейки или какие-то нужные данные
          'так как новая строка создастся без всяких объединений и значений
         XLT.Worksheets("Лист1").Rows(Rowss - 1).Copy newrow
          'это просто для примера как можно очистить некий диапазон внутри документа
       'XLT.Worksheets("Лист1").Range("A10:F10").ClearContents
       'динамически формируем адрес нужной ячейки
          cell = "a" & Rowss
          'и задаем ей значение
         XLT.Worksheets("Лист1").Range(cell) = numrow
         cell = "b" & Rowss
         XLT.Worksheets("Лист1").Range(cell) = rsd.Fields("field5").Value
         'переходим на следующую строку
          Rowss = Rowss + 1
          'переходим на следующую строку в источнике данных
         rsd.MoveNext
      Else
          'а это выполняется до тех пор, пока не закончатся заданные строки в шаблоне
          'т. е. если строк в источнике всего 1, то в код, который выше мы даже не попадем
         cell = "a" & Rowss
         XLT.Worksheets("Лист1").Range(cell) = numrow
         cell = "b" & Rowss
         XLT.Worksheets("Лист1").Range(cell) = rsd.Fields("field5").Value
         Rowss = Rowss + 1
         rsd.MoveNext
      End If
         
        'для нумерации
        numrow = numrow + 1
   'конец цикла
   Wend
   
   'это просто пример как можно удалить строку целиком
   'XLT.Worksheets("Лист1").Rows(20).Delete
   
   'делаем Excel видимым
   XL.Visible = True
   
   'Очищаем переменные
   Set XL = Nothing
   Set XLT = Nothing
   Set newrow = Nothing
   
   End Sub

Здесь я также все подробно прокомментировал, но если есть вопросы, то задавайте их в комментариях к данной статье.

Для сведения, я здесь при создании объекта и Word.Application и Excel. Application использовал позднее связывание, для того чтобы не добавлять необходимые библиотеки и обеспечить совместимость.

Надеюсь, мои примеры Вам помогут!

Импорт и экспорт таблиц Excel в базах данных Access — MS Excel

Интеграция Access 2010 с другими приложениями осуществляется с помощью вкладки Внешние данные ленты. Здесь присутствуют области Импорт и Экспорт. Обмен информацией осуществляется с БД Access, электронными таблицами Excel, текстовыми и XML-файлами, возможен экспорт в PDF и XPS, отправка по электронной почте. При щелчке на кнопке Дополнительно появляется выпадающее меню, позволяющее работать с форматами HTML, dBASE, распределенными базами данных, а также с источниками данных ODBC.

Для экспорта в офисные форматы Word и Excel щелкаем на соответствующей кнопке на меню и указываем местонахождение будущего файла. Вариант окна для экспорта в электронные таблицы MS Excel представлен на рис. 1. На втором шаге экспорта имеется возможность сохранить текущий экспорт для последующего выполнения этой операции. Сохраненная операция вызывается пиктограммой Сохраненные операции экспорта. Аналогично можно поступать и с операциями импорта. Установив соответствующий флажок, можно инициировать открытие электронной таблицы непосредственно после экспорта. Экспорт в другие форматы выполняется аналогично и никаких затруднений не вызывает.

Рис. 1. Экспорт в Excel. Выбор места

Импорт из всех файлов внешних по отношению к Access форматов выполняется только потаблично, то есть каждая таблица должна быть импортирована (и экспортирована) отдельно. Исключение составляют только базы данных самого Access — в этом случае можно указать сразу все таблицы, которые необходимо импортировать.

Рассмотрим пример импорта файлов формата XLS. Щелкнув на соответствующей пиктограмме области Импортировать и связать (рис. 2), мы увидим окно, аналогичное изображенному на рис. 1.

Файл можно выбрать для импорта, для добавления данных в существующую таблицу, а также для связывания таблицы, как внешней. В последнем случае набор действий с таблицей ограничен. В частности, изменение данных связанной таблицы из Access невозможно. Выбрав файл и щелкнув на кнопке ОК, расположенной справа внизу, увидим диалоговое окно, в котором можно уточнить область импорта листы рабочей книги.
(листы) или именованные диапазоны.

Рис. 2. Область Импортировать и связать

[stextbox id=»info»]В MS Excel любому списку может быть присвоено имя. Для этого достаточно выделить список и записать его имя в поле имен, которое расположено в левом конце строки формул. Такой список будет восприниматься как именованный диапазон и может быть импортирован в MS Access как отдельная таблица.[/stextbox]

Ход дальнейшего импорта не зависит от того, что импортируется — диапазон или рабочий лист. В нижней части окна представлен макет будущей таблицы базы данных. Выбрав объект и щелкнув на кнопке Далее, перейдите в окно, в котором можно задать заголовки полей. Если первая строка списка действительно содержит заголовки, установите флажок Первая строка содержит заголовки столбцов и щелкните на кнопке Далее.

Иногда на этом этапе появляется сообщение о неполном соответствии первой строки строке имей. Это означает, что в первой строке что-то не так — отсутствует запись, поле имеет не текстовый формат и т. п. Может быть, правее или левее списка имеются какие-то данные, на которые список был автоматически расширен, и в этом случае заголовок столбца по попятной причине отсутствует. При появлении такого сообщения имеет смысл прервать импорт и просмотреть таблицу в Excel внимательнее. Впрочем, если структура таблицы и данные понятны и очевидны, можно продолжить импорт п внести все необходимые исправления уже в таблице MS Access.

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

Вероятно, в общем случае правильнее импортировать таблицу как новую, подправить форматы и уже потом добавлять данные в существующую таблицу, хотя обычно Access правильно преобразует форматы.

Как экспортировать данные из Access в Excel

Скачать инструмент восстановления ПК для быстрого поиска и автоматического исправления ошибок Windows

Знаете ли вы, что можно экспортировать базу данных Access в рабочий лист Excel ? В Microsoft Excel есть функции для импорта или экспорта данных из листа Excel или в него. При экспорте данных в Excel Access создает копию выбранных данных, а затем сохраняет скопированные данные в файле, который можно открыть в Microsoft Excel. В Access можно экспортировать таблицы, запросы, формы и отчеты, но нельзя экспортировать макросы или модули в Excel. При экспорте форм, отчетов или таблиц, содержащих подчиненные формы, подотчеты и подтаблицы, экспортируется только основная форма, отчет или таблица.

Чтобы экспортировать базу данных Access в Excel, выполните следующие действия:

  1. Откройте файл базы данных Access.
  2. Перейдите на вкладку «Внешние данные».
  3. Нажмите кнопку Excel в группе Экспорт.
  4. Щелкните Обзор.
  5. Назовите файл, выберите папку для сохранения файла экспорта и нажмите Сохранить.
  6. Нажмите OK.
  7. Нажмите кнопку Закрыть.
  8. Найдите и откройте файл.

Откройте файл базы данных Access .

Перейдите на вкладку Внешние данные .

Нажмите кнопку Excel в группе Экспорт .

Откроется диалоговое окно мастера Экспорт – Электронная таблица Excel .

Нажмите кнопку Обзор .

Откроется диалоговое окно File Save .

Назовите файл, выберите папку для сохранения файла экспорта и нажмите Сохранить .

Назад к экспорту – таблица Excel 9В диалоговом окне мастера 0005 нажмите OK .

Затем закройте диалоговое окно.

Найдите файл, который вы экспортировали в Excel, и откройте его (это файл Excel).

Можно ли скопировать базу данных Access?

Да, вы можете скопировать базу данных Access в другую папку. Откройте папку, содержащую базу данных Access, которую вы хотите скопировать. Щелкните правой кнопкой мыши файл базы данных Access и выберите «Копировать» в контекстном меню. Перейдите в папку, в которую вы хотите вставить базу данных Access; щелкните правой кнопкой мыши и выберите «Вставить» в контекстном меню.

Чтение : Как создавать таблицы с помощью конструктора таблиц в Access

Какие типы файлов можно экспортировать в Access?

В Access можно экспортировать данные из Access в различных форматах, таких как список Excel, Word и SharePoint. Экспорт базы данных Access в Excel приведет к экспорту объекта на рабочий лист в файле Excel. При экспорте базы данных Access в Word выбранный объект будет экспортирован в форматированный текст. Экспорт базы данных Access в SharePoint приведет к экспорту выбранного объекта в SharePoint в виде списка.

Чтение : Как вставить файл PDF в лист Excel

Как экспортировать данные из Access в Excel более чем 65000 строк?

Чтобы экспортировать данные Access, содержащие более 65 000 строк с форматированием и макетом, необходимо настроить запрос на экспорт 65 000 строк за раз в отдельные электронные таблицы. После этого вам нужно скопировать и вставить их вместе в одну таблицу.

Мы надеемся, что это руководство поможет вам понять, как экспортировать базу данных Access в Excel.

Загрузить инструмент восстановления ПК для быстрого поиска и автоматического исправления ошибок Windows

Дата: Теги: Access, Excel наук в области информационных технологий. Ее цель — стать администратором базы данных или системным администратором. Она любит читать и смотреть исторические документальные фильмы и драмы.

Как легко экспортировать данные Microsoft Access в Excel

Использование мастера доступа для быстрого экспорта данных в Excel

by Avantix Learning Team | Обновлено 18 сентября 2020 г.

Применимо к: Microsoft ® Access ® 2007, 2010, 2013, 2016, 2019 и 365 (Windows)

Microsoft Access включает простой в использовании мастер экспорта, который можно использовать для экспорта таблиц , запросы, формы или отчеты в Excel. Некоторые объекты экспортируются лучше, чем другие. Например, табличная форма экспортируется в более удобный формат, чем столбчатая форма. При экспорте данных в Excel копия выбранных данных отправляется в Excel. Обычно в организации много пользователей, использующих Excel, поэтому данные в формате Excel могут работать хорошо, особенно если число пользователей Microsoft Access ограничено.

Рекомендуемая статья: 10 Быстрых клавиш в Microsoft Access

Экспорт с помощью ленты

Чтобы запустить мастер экспорта Access to Excel:

  1. Перейдите на вкладку «Внешние данные» на ленте.
  2. Нажмите кнопку Excel в группе Экспорт. Появляется мастер.

Экспорт с форматированием или без него

Вы можете экспортировать данные таблиц и запросов в Excel с форматированием или без него. Однако формы и отчеты нельзя экспортировать без форматирования.

При экспорте таблицы без форматирования экспортируются все поля и записи. Если вы хотите экспортировать отфильтрованные записи в таблицу, вы должны экспортировать с форматированием, чтобы в Excel отправлялись только отфильтрованные записи. Кроме того, при экспорте с форматированием учитываются настройки свойства «Формат», скрытые столбцы исключаются, а значения поиска и полные гиперссылки экспортируются.

Как Access экспортирует вычисляемые поля, итоги и элементы управления в Excel

Вычисляемые поля, итоги и элементы управления, созданные в Access, экспортируются как значения, а не как вычисления.

Экспорт в Excel с помощью мастера Access

Для экспорта данных в Excel из Access:

  1. Откройте таблицу, запрос, форму или отчет, которые вы хотите экспортировать. Обычно вы достигаете наилучших результатов, если используете таблицу или запрос. При необходимости примените фильтр. Если вам не нужно фильтровать записи, просто щелкните объект в области навигации.
  2. Перейдите на вкладку «Внешние данные» на ленте.
  3. В группе Экспорт щелкните Excel. Мастер открывается.
  4. Введите имя файла (если вы введете то же имя, что и у существующей книги в месте назначения, вам будет предложено заменить его) и нажмите кнопку «Обзор», чтобы выбрать место для файла. Выберите другие параметры в мастере, такие как включение форматирования, если вы хотите открыть конечный файл и если вы хотите экспортировать отфильтрованные или выбранные записи. Если вы заменяете существующую книгу, файл необходимо сначала закрыть.
  5. Нажмите OK. Если вы решили открыть файл назначения после завершения, Excel откроется автоматически.

При присвоении имени книге Excel во время этого процесса вы можете включить дату в имя, если вам нужно сохранить копии данных для определенных дат. В конце процесса или когда вы вернетесь в Access, появится диалоговое окно с вопросом, хотите ли вы сохранить шаги экспорта. Если вы экспортируете на регулярной основе, вы можете сохранить шаги экспорта для повторного использования.

Подпишитесь, чтобы получать больше статей, подобных этой

Была ли эта статья полезной для вас? Если вы хотите получать новые статьи, присоединяйтесь к нашему списку адресов электронной почты.

Чтобы запросить эту страницу в другом формате, свяжитесь с нами.

Дополнительные ресурсы

Как преобразовать отчет Microsoft Access в PDF (3 способа)

Как фильтровать отчет на лету в Microsoft Access

10 методов разработки форм в Microsoft Access

Связанные курсы

Microsoft Access : Введение

Microsoft Access: средний/продвинутый

Microsoft Access: введение в VBA (Visual Basic для приложений)

ПОСМОТРЕТЬ ДРУГИЕ КУРСЫ >

Наши курсы под руководством инструктора проводятся в формате виртуального класса или в нашем центре Торонто по адресу 18 King Street East, Suite 1400, Toronto, Ontario, Canada (некоторые очные курсы также могут проводиться в другом месте в центре Торонто). Свяжитесь с нами по адресу [email protected], если вы хотите организовать индивидуальный виртуальный класс под руководством инструктора или обучение на месте в удобное для вас время.

Copyright 2022 Авантикс ® Обучение

Вы можете выделять значения в полях или записях в отчетах Microsoft Access, используя условное форматирование. Для применения форматирования должны быть выполнены определенные условия …

Вы можете создавать вычисляемые поля в запросах на выборку в Microsoft Access в сетке QBE (Query by Example). Вам нужно будет изучить несколько правил синтаксиса, а затем вы сможете создавать простые и более сложные вычисления.

Ознакомьтесь с этими отличными сочетаниями клавиш для управления элементами управления в представлении «Дизайн» как в формах, так и в отчетах в Microsoft Access.

Microsoft, логотип Microsoft, Microsoft Office и связанные приложения Microsoft и логотипы являются зарегистрированными товарными знаками Microsoft Corporation в Канаде, США и других странах.