Vba excel управление другими программами

Обновлено: 06.01.2025

Иногда бывает необходимо перенести что-то из Excel в другое приложение. Я возьму для примера Word. Например скопировать ячейки и вставить. Обычно мы это так и делаем - скопировали в Excel, открыли Word - вставили. Но сделать это при помощи кода чуть сложнее, хотя если разобраться никаких сложностей нет. Ниже приведен пример кода, который открывает Word, открывает в нем определенный документ, копирует данные из Excel и вставляет в открытый документ Word.

Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Excel диапазон "A1:A10" Range("A1:A10").Copy 'вставляем скопированные ячейки в Word - в начала документа objWrdDoc.Range(0).Paste 'закрываем документ Word с сохранением objWrdDoc.Close True ' False - без сохранения 'закрываем приложение Word - обязательно! objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub

Tips_Macro_OpenWord.xls (49,5 KiB, 5 640 скачиваний)

В файле-примере, приложенном к данной статье, в комментариях к коду есть несколько добавлений. Например, как вставить текст из ячеек в определенные закладки Word-а и как добавить новый документ, а не открывать уже имеющийся. Так же так есть код проверки - открыто ли приложение Word в данный момент. Порой это тоже может пригодиться, чтобы работать с запущенным приложением Word, а не создавать новое:

В принципе, активировать или вызвать(если закрыто) другое приложение Офиса можно одной строкой:

Sub Open_AnotherApp() Application.ActivateMicrosoftApp xlMicrosoftWord End Sub

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

По сути, методами CreateObject и GetObject можно обратиться к любому стороннему приложению(например Internet Explorer). Куда важнее при обращении к этим объектам знать объектную модель того приложения, к которому обращаетесь. Чтобы увидеть свойства и методы объектной модели приложения, можно в редакторе VBA подключить необходимую библиотеку, объявить переменную, назначив ей тип приложения. Покажу на примере того же Word-а.
Для начала открываем меню Tools - References :

Подключаем библиотеку:

Затем объявляем переменную и присваиваем ей тип нужного приложения:

Sub OpenWord() Dim objWrdApp As Word.Application Set objWrdApp = New Word.Application objWrdApp.Visible = True End Sub

Если теперь в редакторе, внутри этой процедуры в любом месте ниже объявления переменной набрать objWrdApp и точку, то сразу после ввода точки выпадет меню, в котором будут перечислены все доступные методы и свойства этого приложения.

Так же можно нажать F2 и через поиск найти Word и просмотреть все методы и свойства данного приложения.

Метод установки ссылки на библиотеку приложения через Tools-References называют еще ранним связыванием. Подобный метод позволяет создать ссылку на приложение быстрее и, как описано выше, предоставляет разработчику доступ к визуальному отображению свойств и методов объекта. Но есть существенный минус: если в своем коде Вы установите ссылку на Word 12 Object Libbary(Word 2007), то на ПК с установленным Word 2003 получите ошибку MISSING, т.к. Word 2003 относится к библиотеке Word 11 Object Libbary. Подробнее можно прочитать в статье Ошибка — Cant find project or library.
Метод же CreateObject еще называется методом позднего связывания. Применяя его не возникнет проблем с MISSING, очень часто возникающих при раннем связывании. Поэтому я рекомендовал бы при разработке использовать раннее связывание для удобства использования свойств и методов(если Вы их не знаете), а перед распространением приложения в коде заменить все именованные константы(типа wdLine) на числовые константы(для wdLine это 5) и применить позднее связывание. Посмотреть числовое значение константы можно просто записав её в коде, начать выполнение кода через F8 и навести курсор мыши на эту константу. Всплывающая подсказка покажет числовое значение. Так же можно отобразить окно Immediate(View -Immediate Window или сочетание клавиш Ctrl + G ), записать вопросительный знак и вставить эту константу и нажать Enter :
?wdLine
ниже будет выведено числовое представление этой константы.
А заменять эти константы их числовыми значениями в случае с поздним связыванием необходимо, т.к. Excel не знает их значений.
Попробую пояснить поподробнее про эти константы и почему их надо заменять какими-то числами: при подключении библиотеки Wordа(Word 12 Object Libbary) мы так же подключаем и все свойства, методы и константы, которые доступны из Wordа. И их использование напрямую становится доступно из Excel и мы можем смело написать что-то вроде wbLine и Excel поймет эту константу. При позднем же связывании мы уже не подключаем библиотеки Word(во избежание ошибок совместимости) и как следствие - методы, свойства и константы Wordа для Excel становятся чем-то неизвестным и не документированным и мы получим ошибку "Variable not defined"(если включена директива Option Explicit) при попытке назначить свойство через wdLine. Если же Option Explicit не включена - то хоть ошибки не будет, но и код будет работать неверно, т.к. для неизвестной для Excel переменной wbLine будет назначено значение 0(Empty). Поэтому и надо все константы другого приложения заменять их числовыми значениями.

Главная ошибка новичка
И хочу так же упомянуть про ошибку, которую очень часто совершают при обращении к одному приложению из другого. Допустим, необходимо скопировать из Word все данные в Excel. Часто начинающие делают это так:

Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Word все данные, обращаясь к объекту Range документа Range.Copy 'вставляем скопированное в ячейку А1 активного листа Excel ActiveSheet.Paste 'закрываем документ Word без сохранения objWrdDoc.Close False 'закрываем приложение Word objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub

На строке Range.Copy обязательно получите ошибку от VBA, указывающую, что нужен аргумент для объекта. Можно попробовать добавить этот аргумент: Range(1).Copy. Но все равно получим ошибку. Можно, конечно, указать даже ячейки: Range("A1").Copy. Но это приведет к тому, что скопирована будет ячейка А1 активного листа Excel.
Все дело в том, что мы хотим скопировать данные из Word-а, выполняя при этом код из Excel. А у Excel тоже есть объект Range с другими аргументами. И если не указать какому приложению, листу или документу принадлежит Range, то по умолчанию он будет отнесен к тому приложению, из которого выполняется код. Т.е. к Excel. Если совсем кратко об этом - всегда надо указывать какому приложению или объекту принадлежит используемый объект или свойство. Правильно код должен выглядеть так:

Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Word все данные, обращаясь к объекту Range документа 'при этом перед Range явно указываем откуда его брать - из документа Word -objWrdDoc("C:\Doc1.doc") objWrdDoc.Range.Copy 'вставляем скопированное из Word в активную ячейку активного листа Excel ActiveSheet.Paste 'закрываем документ Word без сохранения objWrdDoc.Close False 'закрываем приложение Word objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub

Вместо Range ту же ошибку делают и с Selection(потому что Selection часто присутствует в записанных макрорекордером макросах), т.к. этот объект есть и в Excel и в Word и без явного указания приложения будет относится к приложению, в котором записано.

Tips_Macro_OpenWord.xls (49,5 KiB, 5 640 скачиваний)

А в архиве ниже - практически готовое решение заполнения всевозможных бланков Word из Excel. Как это работает. У нас есть таблица Excel с данными для заполнения бланков заявлений на пособия:

Обращаю внимание, что в первой строке расположены метки. Они нужны для того, чтобы код мог понять значения какого столбца в какое место шаблона Word должны попасть. А в самом шаблоне Word мы должны проставить эти самые метки:

Фигурные скобки сделаны для того, чтобы код 100% искал и заменял только метку в шаблоне, исключая при этом замену случайного текста вне скобок(ведь слово "Должность" может встречаться и само по себе).
А здесь я схематично привел то, как будут происходить замены:

Сначала программа создаст новую папку, в которую и будет сохранять создаваемые файлы(имя папки состоит из даты и времени запуска кода). Далее программа циклом пройдется по каждой строке таблицы, создаст на основании шаблона Word( "Шаблон.doc" ) новый файл для этой строки, заполнит этот шаблона данными на основании меток, и сохранит созданный файл под новым именем. Сам файл шаблона при этом не изменяется - все метки в нем сохраняются как были настроены до запуска кода. Конкретно в приложенном коде значение для имени нового файла берется из первого столбца "ФИО с инициалами". Но это можно изменить в коде при необходимости. Делается это в этой строке:

'считываем фамилию с инициалами sWDDocName = .Cells(lr, 1).Value

Что еще важно: файл шаблона Word должен находиться в той же папке, что и файл с кодом. Название файла в приложенном к статье файле должно быть "Шаблон.doc". Но его так же можно изменить, не забыв изменив его в коде в этой строке:

'имя шаблона Word с основным текстом и метками Const sWDTmpl As String = "Шаблон.doc"

И нтеграция с другими программами - Макросы в Excel

Не стоит забывать и о возможностях интеграции VBA с другими приложениями. Монополия это конечно плохо, но в данном случае именно монопольное положение Microsoft дало нам столько возможностей по интеграции различных приложений друг в друга.

Итак, с чем и как можно интегрировать VBA?

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

‘ объявляем о начале использования такого типа объекта

Set fso = CreateObject("scripting.filesystemobject")

‘ открываем нужный файл

Set tS = fso.OpenTextFile(full_name_of_txt-file, 1, True)

‘ присваиваем стринговой переменной весь текст, содержащийся в файле

‘ закрываем текстовый файл

Просмотр файлов в папке

Средствами VBA можно получить список файлов, находящихся в папке, причем не только файлов MS Office, но и всех остальных. Это бывает нужно, например, чтобы открыть все файлы и напечатать, открыть несколько стандартных файлов и агрегировать хранящуюся в них информацию или чтобы просто получить список имеющихся файлов. Для этого можно воспользоваться функцией Dir(путь, тип файла) . Функция возвращает имена файлов из папки.

При первом запуске функция выдает имя первого по порядку подходящего файла из указанной папки. Важно указывать \ на конце папки, иначе функция выдаст ошибку либо пустое значение. После того, как мы получили имя файла, мы можем его открыть и работать с ним. Потом нам нужно открыть следующий файл из папки. Для этого снова вызываем Dir, но без аргументов. А когда файлы закончатся, функция выдаст пустое значение - "".

Работу функции проще понять на примере.

Допустим, в папке "C:\Documents and Settings\" лежит три Excel-файла, и мы хотим их открыть.

Тогда наш макрос будет выглядеть так:

dr = Dir("C:\Documents and Settings\", vbNormal)

Workbooks.Open("C:\Documents and Settings\" & dr)

Подключение VBA к SQL может потребоваться в куче разных мест. Например у меня оно понадобилось совсем внезапно: caption полей в кубе и их key не совпадали. То есть когда я хотела проставить какие-то вещи, я имела их "человеческий" список, а в макрос мне нужно было подставлять "компьютерный". Соответствие одного с другим я и получала в базе.

Сначала запомним в переменную строку подключения, которую будем использовать потом. Её легче всего получить, настроив такое же подключение в Excel и потом просто скопировать оттуда готовую. Она будет примерно такая:

cnstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MyServerName;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MyDatabaseName"

Set cn = New ADODB.Connection

' указываем, что в этом коннекте у нас будет recordset с именем rs

Set rs = New ADODB.Recordset

' открываем коннект с нашей строкой подключения

Затем получаем данные. Обратите внимание, что так как скрипт это всего лишь строка, вы можете подставлять туда разные переменные и др., полученные до этого в коде.

rs.Open «select * from table», cn

Пробег по строчкам recordset-а описывается достаточно странно, работать с ним неудобно. Гораздо удобнее перезаписать данные в массив VBA. Поэтому бежим по строчкам recordset-а

Do While Not rs.EOF

ReDim Preserve arr(i)

Если вам не нужно получать данные, а нужно просто выполнить код, то можно воспользоваться командой execute

StrSql = "insert into ReportByIdList(ID,Name) values(1, report)"

LotusNotes и другие Lotus-приложения.

LotusNotesявляется почтовым клиентом, однако, позволяет делать еще массу других вещей, например, он также является браузером и календарем. Можно совершать различные действия в Lotus, не выходя из Excel’я или Word’а. Скрипт достаточно труден, а подсказок в интернете не так уж и много, но иногда результат оправдывает все потраченные силы.

Начать сессию можно двумя способами.

Способ 1 . Надо поставить галочку в Tools - References на объекты Лотуса, чтоб VBA подгрузил команды и справку Lotus'а. Лучше только одну галку, а то всё будет дублироваться

Dim session As New NotesSession

Способ 2 (в дальнейшем все команды представлены для способа 2)

Dim NotesSession As Object

Dim NotesDB As Object

Dim NotesDOC As Object

Dim rrr As Object

Dim vview As Object

Set NotesSession = CreateObject("Notes.Notessession")

Далее нужно обозначить, в какой базе данных Лотуса мы будем работать. Можно работать в текущей базе, т.е. в последней открытой

Или обозначить базу, используя имя сервера и путь к файлу, который хранит базу

Set db = NotesSession.GetDatabase("Server22 ", "organization.nsf")

Такая база может быть не открыта, тогда её надо открыть (уточнить, open или openmail)

If db.IsOpen = False Then db.OPENMAIL

В базах есть разные представления данных (например, входящие письма, исходящие письма и др). Нужно выбрать, из какого представления нам нужны документы. К представлению можно обратиться по имени

Set vview = db.GetView("($Sent)")

А можно перебрать все имеющиеся в базе представления (здесь именно View, а не абы что, иначе не работает)

For Each View In db.Views

Далее из выбранных вьюх можно вытащить помещенные в них документы

Set rrr = vview.GetFirstDocument

Set rrr = View.GetFirstDocument

Чтобы перейти к следующему документу, пишем

Set rrr = vview.GetFirstDocument(rrr)

Set rrr = View.GetFirstDocument(rrr)

У документа есть поля. Их имена можно посмотреть, щелкнув п.к.м по документу (письму, записи о сотруднике, служебке и т.д.), открыв "свойства документа" и выбрав вторую иконку (треугольничек)

Значение тех или иных полей можно получить, например, так

Поля - это массивы. Отсюда и (0) в конце - это просто обращение к конкретному элементу массива. Каков размер этих массивов можно узнать функцией ubound. Поэтому тот же результат можно получить через присваивание массиву:

Dim doc() As String

Можно создавать новые документы (по крайней мере письма можно, остальное, возможно, зависит от наличия админских прав). Пример создания письма:

Set NotesDOC = NotesDB.CreateDocument

Call NotesDOC.ReplaceItemValue("From", "адрес отправителя")

.sendto = "тут адрес получателя"

.Principal = "тут адрес отправителя"

.subject = "Тема письма"

.body = "Текст письма"

Можно делать вложения файлов в письмо. Для этого после создания письма нужно определить файлы, которые будут вложены. Получим:

Set NotesDOC = NotesDB.CreateDocument

Set NotesAttachment = NotesDOC.CreateRichTextItem("stFileName")

Set NotesEmbedObject = NotesAttachment.EmbedObject(1454, "", stFileName, stFileName2) '1454 - параметр lotus (attachment)

Set NotesAttachment2 = NotesDOC.CreateRichTextItem("stFileName2")

Set NotesEmbedObject2 = NotesAttachment.EmbedObject(1454, "", stFileName2)

После этого идут все те же строки отправки:

Call NotesDOC.ReplaceItemValue("From", "адрес отправителя")

.sendto = "тут адрес получателя"

.Principal = "тут адрес отправителя"

.subject = "Тема письма"

.body = "Текст письма"

Отправлять письмо сразу нескольким получателям можно, записав адреса получателей в массив и указав этот массив в строке sendto. Например:

resipients = Split("test1, test2, test3")

Свойства баз Lotus

Сервер и путь к файлу базы данных можно посмотреть где-то в свойствах, а можно сначала войти в неё через

а потом вытащить её свойства:

Есть еще другие свойства, и если поставлена галочка в Tools - References на объекты Лотуса, то их можно смотреть в ObjectBrowser .

Кроме того, в параметрах Excel(Файл -> Параметры) есть некоторые настройки, связанные с Lotus, которые могут быть полезны.

Dir - одна из функций, доступных в VBA. Функция Dir предназначена для ссылки на каталоги в коде VBA.
Функция, которая возвращает имя файла или каталога, которое соответствует данному атрибуту или строке, в противном случае возвращает первый файл или папку. Проще говоря, если у нас есть папка ABC и внутри ABC есть файл XYZ, то мы можем получить доступ к файлу XYZ с помощью функции DIR в VBA.

Формула для функции DIR в Excel VBA

Функция DIR имеет следующий синтаксис в Excel VBA:

Он состоит из двух частей: Pathname и Attributes.

Путь: по имени каждый может понять, что это путь к файлу, в котором он существует. Если мы не введем какой-либо путь в pathname, он вернет пустую строку.
Атрибут: это необязательный аргумент, мы не используем это много. Мы можем использовать это, когда мы хотим открыть файл с указанными ниже атрибутами, тогда VBA ищет эти файлы.

Как использовать функцию DIR в Excel VBA?

Ниже приведены различные примеры использования функции DIR в Excel с использованием кода VBA.

Вы можете скачать этот шаблон VBA DIR Excel здесь - Шаблон VBA DIR Excel

В этом примере мы увидим, как получить доступ к имени файла с помощью функции DIR в VBA.

Шаг 1. Перейдите на вкладку « Разработчики » и нажмите на Visual Basic

Шаг 2: Откройте модуль из меню «Вставка», как показано ниже.

Шаг 3: Чтобы запустить любой макрос, сначала нам нужно дать имя макросу с ключевым словом «sub», как показано ниже.

Код:

Помните, что мы пишем наш код в «Module1», когда вводим «sub myexample ()», автоматически появляется «End sub».

Шаг 4: Определите строку, используя ключевое слово « Dim », которое относится к измерению. На скриншоте ниже «mystring» - это имя строки.

Код:

Помните, что когда вы определяете имена типов данных или имен программ, между двумя словами не должно быть пробелов. «Mystring» нет пробела между «my» и «string».

Файл доступен в папке примеров, которая доступна на рабочем столе, и имя файла - «KT tracker mine».

C: \ Users \ cba_13 \ Desktop \ Sample \

Шаг 5: Теперь нам нужно сохранить имя файла в «mystring» с помощью функции Dir.

Код:

На приведенном выше снимке экрана в функции Dir я указал путь к файлу, исключая имя файла. Поскольку существует только один файл, он вернет имя файла.

Код:

Шаг 7: Теперь запустите код, нажав на кнопку Play или нажав клавишу F5.

Функция VBA DIR - Пример № 2

В этом примере мы увидим, как открыть файл с помощью функции DIR в Excel VBA.

Шаг 1: Откройте экран кода VBA и начните с присвоения имени программы «example2» или любого имени, которое вы пожелаете.

Код:

Шаг 2: Определите две строки с именами «Foldername» и «Filename».

Код:

Шаг 3: Теперь назначьте путь к папке с именем папки.

Код:

Шаг 4. Назначьте файл «filename» с помощью функции Dir. Здесь мы использовали переменную «Foldername», потому что она имеет путь к папке. С помощью амперсанда мы добавили имя файла в путь.

Код:

Теперь функция Dir возвращает имя файла и будет хранить его в переменной «имя файла».

Код:

Шаг 6: Запустите приведенный выше код, он откроет файл, доступный в папке «C: \ Users \ cba_13 \ Desktop \ Sample \» с именем файла «KT Tracker mine».

Функция VBA DIR - Пример № 3

Теперь мы увидим программу, чтобы узнать, доступна ли папка или нет. У меня есть папка «Данные», как показано ниже.

Мы должны проверить с помощью функции Dir, доступна ли папка «Data» по пути C: \ Users \ cba_13 \ Desktop \ Sample \.

Шаг 1. Создайте имя программы и определите две строки с именами FD и FD1.

Код:

Шаг 2: Назначьте путь к папке переменной «Fd».

Код:

Шаг 3: Теперь используйте функцию Dir, чтобы вернуть имя папки, как показано ниже.

Код:

Результатом функции Dir должно быть имя папки, и она будет храниться в строковой переменной Fd1. Если папка «Данные» недоступна в соответствующей папке, она не вернет ни одной строки.

Шаг 4: Теперь нам нужно проверить, есть ли у Fd1 папка «Данные» или нет. Используйте условие IF, чтобы проверить, есть ли в Fd1 строка «Данные» или нет. Если оно доступно, распечатайте заявление как «Существует».

Код:

Шаг 5: В случае, если Fd1 не совпадает с папкой «Данные», выведите инструкцию «Не существует» в другом состоянии.

Код:

Шаг 6: Завершите цикл «Если», как показано на скриншоте ниже.

Код:

Шаг 8: Просто измените имя папки на Data1.

Шаг 9: Запустите код еще раз. Теперь результат «Не существует», как показано ниже.

То, что нужно запомнить

Аргументы в функции Dir являются необязательными.
Если нам нужны скрытые файлы, папки или каталоги другого типа, укажите ваше требование во втором аргументе.
Используйте «\» в конце имени папки, указав функцию Dir, чтобы войти в папку.
Если мы хотим позвонить или открыть несколько файлов, мы можем использовать подстановочный знак «*» или «?».

Как вызвать процедуру из другой книги Excel?

В арсенале VBA Excel есть два способа вызвать процедуру из другой книги:

Установить связь с книгой и работать как с обычным модулем;
Воспользоваться методом Run с явным указанием пути к книге.

Так же, зададим имя нашего VBA проекта, для этого в окне Project Explorer (ctrl+R), щелкните по названию проекта в нашей книге Library (VBAProject(Library.xls) и в окне свойств (Properties Window (F4)) замените Name на свое имя. Я назвал как "My_Library":

Сохраняем, закрываем книгу.

Теперь переходим к книге Exec_Book.xls. Открываем VBE редактор (alt+F11).

Для того чтоб процедуры и функции другой книги стали доступны, в книге которая будет вызывать процедуры (в нашем случае Exec_Book.xls) необходимо создать ссылку на книгу с процедурами (Library.xls). Ссылка устанавливается следующим образом: Tools - References

В открывшемся окне щелкните по кнопке Browse и укажите наш файл Library.xls. Не забудьте изменить фильтр "Тип файла" на "Microsoft Office . "

В окне References появится новая связь "My_Library"

Жмем ОК. Все. Теперь мы можем вызвать нашу процедуру любым способом, описанным в этой статье.

Читайте также: