Vba access создать excel
Обновлено: 05.07.2024
При разработке приложения, использующего данные в документе Office, может быть полезно иметь эти данные непосредственно в приложении Visual Basic, чтобы пользователь мог просматривать и редактировать данные без необходимости переключаться на приложение Office. Это можно сделать в Visual Basic с помощью элемента управления OLE Container.
В этой статье описывается динамическое создание и автоматизация документа Office с помощью элемента управления OLE Container.
Дополнительные сведения
Возможность вставки документа Office в Visual Basic осуществляется с помощью технологии технологии OLE и связывания и внедрения объектов Майкрософт. Технология OLE предназначена для того, чтобы позволить другому приложению размещать объект, принадлежащий другому приложению, так, чтобы он был удобным для конечного пользователя, но не требует от приложений знать внутренние сведения о другом. Visual Basic предоставляет элемент управления OLE Container, позволяющий программистам Visual Basic добавлять в форму объекты OLE.
После внедрения объекта большинство OLE-серверов поддерживают автоматизацию, чтобы ведущее приложение программно изменяет или манипулирует объектом из кода. Чтобы получить ссылку на объект Automation, используйте свойство Object контейнера OLE. Это свойство возвращает объект автоматизации, который точно соответствует внедренному элементу.
Создание приложения Visual Basic, в котором размещается книга Excel
Запустите Visual Basic и создайте новый стандартный проект. По умолчанию создается форма Form1.
На панели элементов управления добавьте три командные кнопки в форму Form1. Затем добавьте экземпляр элемента управления OLE Container. Когда элемент управления OLE Container размещается в форме, он запрашивает тип объекта, который вы хотите вставить. Для этого примера необходимо динамически добавить объект, поэтому нажмите кнопку Отмена, чтобы закрыть диалоговое окно без добавления объекта.
В окне кода для формы Form1 добавьте следующий код:
Создание приложения Visual Basic, в котором размещается документ Word
Запустите Visual Basic и создайте новый стандартный проект. По умолчанию создается форма Form1.
На панели элементов управления добавьте три командные кнопки в форму Form1. Затем добавьте экземпляр элемента управления OLE Container. Когда элемент управления OLE Container размещается в форме, он запрашивает тип объекта, который вы хотите вставить. Для этого примера необходимо динамически добавить объект, поэтому нажмите кнопку Отмена, чтобы закрыть диалоговое окно без добавления объекта.
В окне кода для формы Form1 добавьте следующий код:
Рекомендации по использованию OLE Container
При внедрении из существующего файла данные, содержащиеся в контейнере OLE, являются копией данных в файле. Все внесенные изменения не сохраняются автоматически в один и тот же файл. Несмотря на то, что вы можете использовать метод, аналогичный приведенному выше, чтобы сохранить результаты обратно в определенный файл, не все OLE серверы поддерживают эту возможность. Он не считается обычным поведением объекта OLE.
Если вы привязку к файлу, объект невозможно активировать на месте. Вместо этого, когда пользователь дважды щелкает объект, объект открывается в окне серверного приложения. На месте можно активировать только внедренные объекты.
Элемент управления OLE Container связан с данными. Если у вас есть база данных Access 97 или Access 2000, вы можете привязывать элемент управления к полю объекта OLE в базе данных. При отображении формы данные извлекаются из базы данных и отображаются для редактирования пользователем. Любые изменения, внесенные пользователем, автоматически сохраняются в базе данных при закрытии объекта OLE.
Чтобы сделать привязанные данные элемента управления OLE Container, добавьте элемент управления данными Visual Basic и присвойте свойству DatabaseName путь к базе данных. Затем присвойте источнику записи существующую таблицу в базе данных. Используйте свойство DataSource элемента управления OLE, чтобы присоединить элемент управления к элементу управления данными Visual Basic, а затем задайте свойство DataObject, чтобы оно ссылалось на определенное поле в наборе записей, содержащее объект OLE. Visual Basic выполняет остальные функции.
Размер и положение заштрихованной границы, которая отображается, когда контейнер становится активным на месте, определяется размером объекта и параметрами, выбранными для элемента управления OLE. Эта граница отображается, чтобы пометить границы окна редактирования. Границы окна редактирования часто не совпадают с границами самого контейнера OLE; Такое поведение является нормальным для объекта OLE. Окно редактирования невозможно изменить программным способом с помощью Visual Basic.
Некоторые методы автоматизации могут работать неправильно, если объект не был активирован на месте. Чтобы программно активировать объект OLE, используйте метод Доверб и укажите Вболешов (-1) в качестве команды.
Чтобы определить, отображается ли меню связанного или внедренного объекта в форме контейнера, можно задать свойство формы Неготиатеменус. Если для свойства Неготиатеменус дочерней формы задано значение true, а в контейнере определена строка меню, меню объекта помещаются в строку меню контейнера при активации объекта. Если в контейнере нет строки меню или для свойства Неготиатеменус задано значение false, меню объекта не отображаются при его активации. Обратите внимание, что свойство Неготиатеменус не применяется к формам MDI, поэтому меню формы MDI невозможно объединить с активизированными объектами. Чтобы проиллюстрировать согласование меню, попробуйте выполнить указанные ниже действия с примером приложения, созданного в предыдущем разделе.
Visual Basic не позволяет управлять процессом слияния меню или вносить изменения в элементы меню сервера при добавлении. Однако вы можете изменить или изменить меню приложения Office с помощью автоматизации, используя код, аналогичный приведенному ниже:
Обратите внимание, что некоторые изменения могут быть внесены до того, как объект станет активным на месте, в противном случае изменения могут не отображаться в меню слияние.
Note (Примечание ) Эта точка маркера не относится к Microsoft Office Excel 2007 или Microsoft Office Word 2007.
Visual Basic в настоящее время не поддерживает назначение пространства панели инструментов. Таким образом, закрепленные панели инструментов обычно не отображаются при активации объекта. Тем не менее, можно отобразить плавающее окно инструментов с автоматизацией:
Note (Примечание ) Эта точка маркера не относится к Microsoft Office Excel 2007 или Microsoft Office Word 2007.
Для программ, требующих, чтобы объект оставался активным на месте постоянно, корпорация Майкрософт предоставила технологию ActiveX Documents. Не все серверы OLE являются серверами документов ActiveX; Microsoft Word, Microsoft Excel и Microsoft PowerPoint — это серверы документов ActiveX.
Visual Basic не поддерживает собственный элемент управления для размещения документов ActiveX. Однако элемент управления WebBrowser, поставляемый с Internet Explorer (версия 3,0 и более поздние версии), поддерживает такую форму включения на месте. Этот элемент управления можно использовать для открытия документа Office в виде документа ActiveX. Для получения дополнительных сведений об использовании элемента управления WebBrowser обратитесь к следующей статье:
243058 как использовать элемент управления WebBrowser для открытия документа Office
Метод SaveToFile контейнера OLE создает файл, который можно открыть в контейнере OLE. Однако файлы, сохраненные с помощью метода SaveToFile контейнера OLE, невозможно открыть непосредственно в соответствующем приложении Office. Если вы хотите сохранить внедренный документ на диске, чтобы документ можно было открыть в целевом приложении, используйте метод SaveAs документа приложения:
Очень часто бывает такое, что нужно сформировать документы по определенному шаблону, на основе каких-то данных, например, по каждому сотруднику или по каждому лицевому счету. И делать это вручную бывает достаточно долго, когда этих самых сотрудников или лицевых счетов много, поэтому сегодня мы рассмотрим примеры реализации таких задач в Excel с помощью макроса написанного на VBA Excel.
Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т.е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.
В нашем примере мы, конечно, будем использовать простой шаблон, только для того чтобы это было просто наглядно и понятно (только в качестве примера), у Вас в свою очередь шаблон будет, как мне кажется намного сложней.
Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля
И так приступим!
Пишем макрос на VBA Excel по формированию документов
Реализовывать нашу задачу будем на примере «Электронной карточке сотрудника» (я это просто придумал:), хотя может такие и на самом деле есть), т.е. документ в котором хранится личные данные сотрудника вашего предприятия, в определенном виде, именно в Excel.
Примечание! Программировать будем в Excel 2010.
И для начала приведем исходные данные, т.е. сами данные и шаблон
Данные.
Лист, на котором расположены эти данные так и назовем «Данные»
Шаблон.
Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»
Далее, нам необходимо присвоить имена полей для вставки, так более удобней к ним обращаться чем, например, по номеру ячейки.
Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»
Свои поля я назвал следующим образом:
Код макроса на VBA Excel
Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.
Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»
затем, в правой области поставьте галочку напротив пункта «Разработчик»
После вкладка разработчик станет отображаться на ленте.
Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».
После у Вас откроется окно редактора кода, где собственно мы и будем писать свой код VBA. Ниже представлен код, я его как обычно подробно прокомментировал:
Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:
и после выполнения у Вас в той же папке появится вот такие файлы
Вот с таким содержимым:
Для удобства можете на листе с данными создать кнопку и задать ей событие выполнить наш только что созданный макрос, и после чего простым нажатием выполнять этот макрос. Вот и все! Удачи!
Всем привет, сегодня мы поговорим о том, как можно выгрузить данные из Access в такие приложения как Word и Excel. Но не о стандартном способе, который есть в Access (связь с Office), а о способе, который позволяет выгружать данные в заданный шаблон как в Word, так и в Excel.
Другими словами, это нужно тогда, когда создать отчет в Access по шаблону, который уже существует, например, в Word, невозможно или слишком трудоемко. Как Вы знаете, отчет в Access может выводиться просто коряво или, самый распространенный вариант, это когда много текста, который в отчете Access не так хорошо форматируется как в Word, а данных не так много, но отчет необходимо автоматизировать, например это какие-то договора, заявления и так далее.
Использование слияния из самого Word-а не очень удобно, поэтому сегодня я расскажу, как можно заполнять такие шаблоны напрямую из Access, путем нажатия на одну кнопку.
Кроме выгрузки в шаблон Word, иногда возникает и необходимость выгрузки в шаблон Excel, и этот способ мы тоже сегодня рассмотрим.
Экспорт данных из Access в шаблон Word
Вся разработка делится на две части, это:
- Настройка шаблона Word;
- Настройка выгрузки данных в шаблон.
Суть настройки шаблона заключается в том, чтобы проставить необходимые поля в тех местах шаблона, где нужно выводить те или иные данные. Это делается с помощью полей формы.
Примечание! Я использую Microsoft Word 2003.
После добавления поля, у Вас появится серая область, которая свидетельствует о том, что поле добавлено. Теперь необходимо задать имя этого поля, для того чтобы потом из access вставлять в него значения (стандартное названия не очень удобное). Для этого щелкните правой кнопкой мыши по полю и нажмите «Свойства». В поле закладка напишите желаемое имя этого поля, я в примере назвал его MyTestPole.
Создайте столько полей, сколько Вам нужно.
На этом настройка шаблона закончена, рекомендую сделать этот шаблон только для чтения, а то пользователь возьмет, сформирует документ и сохранит его, и шаблон тем самым потеряется, а если сделать его только для чтения, то у него такой возможности не будет, только сохранять через «Сохранить как».
Переходим к более интересной задачи, это к реализации самой выгрузки из Access в этот шаблон на VBA.
Примечание! Я использую Access в связке с MS SQL 2008, поэтому и данные буду брать от туда.
Код VBA для выгрузки данных в шаблон Word
Допустим, у Вас есть форма, сделайте на ней кнопку (я назвал ее testbutton) и в событие нажатие кнопки вставьте следующий код VBA:
Код прокомментирован, поэтому сложностей возникнуть не должно. Здесь весь смысл сводится к созданию объекта word.document и word.application. А после мы уже работаем с нашими объектами, т.е. заполняем их.
Экспорт данных из Access в шаблон Excel
В шаблоне Excel уже не нужно создавать поля как в Word, так как здесь мы уже будем ориентироваться по адресам ячеек.
Код VBA для выгрузки данных в шаблон Excel
Сначала добавьте кнопку на форму (я ее назвал testexcel) и вставьте следующий код в событие «Нажатие кнопки».
Здесь я также все подробно прокомментировал, но если есть вопросы, то задавайте их в комментариях к данной статье.
Существующая книга открывается из кода VBA Excel с помощью метода Open:
В кавычках указывается полный путь к открываемому файлу Excel. Если такой файл не существует, произойдет ошибка.
Проверить существование файла можно с помощью функции Dir:
Или, если файл существует, можно сразу его открыть:
Создание новой книги
Новая рабочая книга Excel создается в VBA с помощью метода Add:
Созданную книгу, если она не будет использоваться как временная, лучше сразу сохранить:
В кавычках указывается полный путь сохраняемого файла Excel, включая присваиваемое имя, в примере — это «test2.xls».
Обращение к открытой книге
Обращение к активной книге:
Обращение к книге с выполняемым кодом:
Обращение к книге по имени:
Обратиться по имени можно только к уже открытой книге, а чтобы из кода VBA Excel книгу открыть, необходимо указать полный путь к файлу.
Как закрыть книгу Excel из кода VBA
Открытая рабочая книга закрывается из кода VBA Excel с помощью метода Close:
Если закрываемая книга редактировалась, а внесенные изменения не были сохранены, тогда при ее закрытии Excel отобразит диалоговое окно с вопросом: Вы хотите сохранить изменения в файле test1.xlsx? Чтобы файл был закрыт без сохранения изменений и вывода диалогового окна, можно воспользоваться параметром метода Close — SaveChanges:
Закрыть книгу Excel из кода VBA с сохранением внесенных изменений можно также с помощью параметра SaveChanges:
Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.30 комментариев для “VBA Excel. Рабочая книга (открыть, создать новую, закрыть)”
Если речь идет об обновлении связей между книгами, то попробуйте так:
Здравствуйте, Вячеслав!
Для выбора книги в определённой папке используйте Стандартный диалог выбора файлов Application.GetOpenFilename.
закрывает саму книгу, но при этом сама прога остаётся висеть со своим интерфейсом. Можно нажать Ctlr+O и открыть какой-нибудь xls-файл.
При этом на компе могут быть открыты другие файлы, поэтому команда
не допустима.
Что делать?
Сергей, используйте глобальные переменные, если файл Excel открывается и закрывается разными процедурами (переменные позволят закрыть тот самый файл и тот самый экземпляр приложения, в котором открыт файл):
Set myWorkbook = myExcel . Workbooks . Open ( "C:\Users\. xlsx" )А как открывать книгу, если имя файла совпадает с названием из массива.
То есть есть массив переменных которые записаны в столбец начиная с столбца А строки 2. Названия всегда разные.но совпадают с названием файла. Можно ли поочередно открыть их для редактирования через VBA
Привет, YAN!
Используйте следующий код для открытия по очереди файлов Excel, имена которых записаны в первый столбец со второй ячейки:
Объявление глобальной переменной n размещено в разделе Declarations программного модуля. Число 15 соответствует номеру строки последней ячейки диапазона с именами рабочих книг.
имеется 2 книги (обе открытые)
Используя ссылку (без ручного ввода) надо обратиться Книга 2, скопировать 5 строк ниже ссылки (строка6:строка10) и вставить в рабочую книгу(Книга 1)
Этот код VBA копирует пять строк под указанной ячейкой из открытой книги Excel по адресу из ячейки D4 Листа1 текущей книги и вставляет их в текущую книгу в пять строк под ячейкой D4 Листа1:
Читайте также: