2 семестр / vba_2002
.pdf
Cnct = Cnct & "Data Source=" & DBFullName & ",- "
Connection.Open ConnectionString:=Cnct
'Создание объекта RecordSet
Sec Recordset = New ADODB.Recordset
With Recordset
1Фильтр
Src = "SELECT * FROM Budget WHERE Пункт = 'Рента' " Src = Src & "and Подразделение = 'С. Америка'"
.Open Source:=Src, ActiveConnection:=Connection
'Запись имен полей
|
For Col = 0 To Recordset.Fields.Count - 1 |
|
Range{"Al").Offset(0, Col).Value = Recordset. _ |
|
Fields(Col).Name |
|
Next |
1 |
Запись данных |
|
Range("Al").Offset(1, 0).CopyFromRecordset Recordset |
|
End With |
|
Set Recordset = Nothing |
|
Connection.Close |
|
Set Connection = Nothing |
End |
Sub |
|
Этот пример, а также база данных Access, доступны на Web-узле издательства, |
/'ис. 20.У0. Отображенныеданныеподучены из базы данныхAccess
ЧастьV.Совершенныеметодыпрограммирования |
539 |
Использование метода SendKeys
Не все приложения поддерживают автоматизацию. В некоторых случаях вы можете управлять такими приложениями. Для этого воспользуйтесь методом Excel SendKeys, который а результате отправки комбинаций клавиш эмулирует действия пользователя.
Несмотря на то, что использование метода SendKeys является довольно удачным решением, его применение связано с рядом трудностей. Потенциальная проблема заключается в том, что метод применяется по отношению к конкретному пользовательскому интерфейсу. Если более поздняя версия программы имеет другой интерфейс, то приложение, использующее метол SendKeys, перестанет работать. Следовательно, использовать метод SendKeys можно только в качестве последнего средства.
Следующий пример демонстрирует применение метода SendKeys по отношению к программе Windows Calculator (Калькулятор), Процедура S e n d K e y s T o C a l c u l a t o r начинается с запуска программы Calculator (Калькулятор), после чего программе передаются комбинации клавиш для выполнения простых расчетов.
Suto SendKeysToCalculator()
Shell "calc.exe", vbNormalFocus AppActivate "Calculator"
SendKeys "60*12=" , True
End Sub
Документация по использованию метода SendKeys содержится в справочном руководстве, в котором описаны вопросы отправки нестандартных комбинаций клавиш (например, комбинаций с клавишей <Alt>).
Процедура C e l l T o D i a l e r в листинге 20.5 отображает использование метода SendKeys.
Листинг 20.5. Использование Excel для набора телефонного номера
Sub CellToDialer()
|
Передача содержимого активной ячейки а программу Dialer |
|
1 |
и набор телефонного номера |
|
' |
Получение тел. номера |
|
|
CellContents = ActiveCell.Value |
|
|
If CellContents = "" Then |
|
|
MsgBox "Укажите ячейку с тел. номерок." |
|
|
Exit Sub |
|
|
End If |
|
|
Активизация (или запуск) Dialer |
|
|
Appname = |
"Dialer" |
|
AppFile = |
"Dialer.exe" |
|
On Error Resume Next |
|
AppActivate (Appname)
If Err <> 0 Then
Err = 0
TaskID = Shell(AppFile, 1)
If Err <> 0 Then MsgBox "Невозможно запустить " & AppFile
End If
1Передача содержимого ячейки в Dialer Application.SendKeys "%n" & CellContents, True
'Щелчок на кнопке Вызов
540 |
Глава 20. Взаимодействие с другими приложениями |
Application.SendKeys "%d"
' Application.SendKeys "{TAB}-", True
End Sub
При запуске из рабочего листа процедура активирует программу Windows Dialer (Телефон), которая набирает номер телефона. Если программа Dialer не запушена, то процедура ее запускает. Макрос использует метод SendKeys для передачи содержимого активной ячейки в программу Dialer, после чего выполняется щелчок на кнопке Вызов.
Резюме
Вэтой главе были изложены вопросы автоматизации работы Excel с другими приложениями. В число рассмотренных методов входит запуск и активизация приложения, а также метод SendKeys, который позволяет передавать программам комбинации клавиш.
Вследующей главе будут описаны способы создания надстройки на основе рабочей книги
Excel.
Чаете. V. Совершенные методы программирования |
541 |
Создание и использование надстроек
О дной из самых полезных возможностей Excel, которой пользуются разработчики, является создание надстроек.
В этой главе рассмотрены преимущества использования надстроек. Речь пойдет о создании собственных надстроек с помощью инструментов, встроенных в Excel.
Что такое надстройка
Надстройка — это средство, добавляемое к листу для обеспечения дополнительной функциональности. Например, Excel поставляется с несколькими заранее созданными надстройками. Одной из самых популярных является Пакет анализа. Эта надстройка добавляет в программу инструменты анализа и статистической обработки данных, которые по умолчанию отсутствуют в Excel.
Некоторые надстройки (такие, как Пакет анализа) предоставляют новые функции рабочих листов, которые можно добавлять в создаваемые формулы. Новые средства обычно эффективно дополняют исходный интерфейс, а потому становятся неотъемлемой частью программы (этот метод называется бесшовной интеграцией).
Сравнение надстройки со стандартной рабочей книгой
Любой опытный пользователь Excel может создать надстройку нл основе рабочей книги XLS. Для этого не требуется дополнительное программное обеспечение н специальные средства. Любой файл XLS можно преобразовать в надстройку, но не любые файлы подходят на роль надстройки. В Excel надстройка представлена обычной рабочей книгой, за небольшим исключением.
Свойство I s A d d i n объекта ThisWorkbook установлено в значение True .
Окно рабочей книги скрыто таким образом, что его нельзя отобразить с помощью команд меню Окно. Это означает, что отобразить рабочую книгу или листы диаграмм надстройки невозможно (кроме случаев создания кода для явного копирования этих объектов в стандартные рабочие книги).
Надстройка не является членом коллекции Workbooks — она входит в коллекцию Addlns . При этом сохраняется возможность доступа к надстройке с помощью коллекции Workbooks (дополнительная информация приводится в разделе "Членство в коллекциях" далее в этой главе).
Надстройки можно загружать и выгружать посредством команды Сервис^Надстройки. Диалоговое окно Макрос не отображает имена макросов, которые содержатся в надстройках,
Созданные пользователем функции, которые сохранены в надстройке, могут использоваться в формулах без указания имени исходной рабочей книги.
По умолчанию надстройки имеют расширение файла .эе!в. Однако этонеобязательное требование. Файл надстройки может иметь любое разрешение.
Основные причины создания надстройки
Ниже приведены причины, которые могут заставить пользователя конвертировать документ XLS в надстройку.
•Необходимо ограничить доступ к коду и рабочим листам. Когда приложение распространяется в виде надстройки, оно защищается с помощью пароля. Чтобы пользователи не смогли получить доступ к листай и модифицировать конфиденциальные методы, можно предотвратить копирование кода или, как минимум, усложнить такую возможности
•Необходилю внести ясность. Если пользователь загружает приложение в виде надстройки, то файл остается невидимым, поэтому вероятность того, что начинающий пользователь на него наткнется, намного снижается. В отличие от скрытых рабочих книг XLS, надстройку невозможно отобразить.
•Необходимо упростить доступ к функциям рабочего .чиста. Создаваемые пользователем функции рабочего листа, заданные в надстройке, не требуют указания имени рабочей книги перед вызовом. Например, если в рабочей книге Mewfuncs содержится функция MOVAVG, то для ее использования в другой рабочей книге необходимо обратиться к следующему синтаксису.
=Wewfuncs.xls!MOVAVG{Al:A50)
Если функция будет храниться в открытой надстройке, то можно использовать более простой синтаксис, так как отсутствует необходимость добавления ссылки на файл: MOVAVG(AX:A50)
•Необходимо предоставить пользователям более простой доступ. Как только расположение надстройки будет указано, она отобразится в диалоговом окне Надстройки с дружественным именем и описанием назначения.
•Необходимо получить больший контроль над процессом загрузки. Надстройки могут открываться автоматически при запуске Excel, независимо от папки, в которой они расположены.
Часть V. Совершенные методы программирования |
543 |
О надстройках СОМ
Excel 2000 и более поздние версии программы поддерживают надстройки COM (Component Object Model), Эти файлы имеют расширенно DLL или ЕКЕ. Надстройки СОМ создаются таким образом, что работают во всех приложениях Office, поддерживающих эту возможность. Дополнительным преимуществом является компилируемость кода, что увеличивает безопасность. В отличие от надстроек XLA, надстройки СОМ не могут содержать листы или диаграммы Excel. Надстройки СОМ разрабатываются с помощью Visual Basic 5 (и более поздних версий) или пакета Office Developer Edition. Изучение методов создания надстроек СОМ выходитзапределывопросов, рассматриваемыхвэтойкниге.
• Необходимо избежать отображении сообщений при выгрузке. При закрытии надстройки не отображаются сообщения 'Сохранить изменения в ххх?'\
Использование менеджера надстроек Excel
Наиболее эффективным способом загрузки и вьирузки надстроек является использование диалогового окна Excel Надстройки, доступ к которому можно получить с помощью команды Сервис^Надстройки. Эта команда приведет к отображению диалогового окна Надстройки, которое показано на рис. 21.1. Область списка содержит имена всех надстроек, которые известны Excel. Кроме того, флажки указывают на уже открытые надстройки. Надстройки можно подключать и отключать только в этим диалоговом окне. Вам необходимо установить или сбросить флажок напротив представленной в списке надстройки.
Диалоговое окно Надстройки в Excel 2002 имеет новую кнопку — Автоматизация. Эта кнопка используется для установки надстроек СОМ, Несмотря на, что Excel 2000 поддерживает надстройки СОМ. непосредственного способа их установки Excel 2000 не предоставляет.
Большинство файлов надстроек также можно открыть с помощью команды Файле? Открыть. Так как надстройка никогда не станет активной рабочей книгой, ее невозможно закрыть с помощью команды Файл1^ Закрыть. Надстройка удаляется только в результате перезапуска Excel или выполнения кода VBA, который закрывает надстройку. Подключение надстройки с помощью команды Файл^Открыть приводит к открытию файла, но официально надстройка не считается "установленной".
При открытии надстройки новые возможности, а также инструменты можно и не заметить. В большинстве случаев пользовательский интерфейс незначительно, но изменяется: Excel отображает новый элемент меню или новую панель инструментов. Например, если открыть надстройку Пакет анализа, то появится новый элемент в меню Сервис: Анализ данных. При открытии надстройки
Пересчет в евро появится новая панель инструментов EuroVafue. Если надстройка содержит только пользовательские функции рабочего листа, то новые функции добавляются в диалоговое окно Мастер функций.
|
Рис. 21.1. Диалоговое окно Надстройки |
544 |
Глава 21. Создание и использование надстроек |
Создание надстройки
Как отмечалось ранее, в надстройку можно превратить любую рабочую книгу, но не все рабочие книги являются подходящим материалом для создания надстройки. От превращения
внадстройку более всего "выигрывают" рабочие книги, содержащие макросы, особенно те, которые включают процедуры универсального назначения. Рабочая книга, которая состоит только из рабочих листов, окажется недоступной после создания надстройки, так как листы
внадстройке скрываются от пользователя. Однако вы можете создать код, который будет копировать все или часть листов из надстройки в отображаемую на экране рабочую книгу.
Создание надстройки на основе рабочей книги— это очень простая задача. Следующие инструкции описывают создание надстройки на основе файла стандартной рабочей книги.
1.Создайте приложение и удостоверьтесь, что оно работает правильно.
Не забудьте определить способ запуска макросов (или макроса), которые содержатся в надстройке. Может возникнуть необходимость в добавлении элемента меню, нескольких опций меню или дополнительной панели инструментов. В главе 23 изложена дополнительная информация о модификации меню, а в главе 22 содержатся сведения о создании собственных панелей управления.
2.Протестируйте приложение, запустив его в тот момент, когда активной является другая рабочая книга.
Таким образом, будет проверено поведение приложения в качестве надстройки (так как надстройка никогда не является активной рабочей книгой).
3.Запустите редактор VBE и выберите рабочую книгу в окне Project. Выполните команду ToolS^xxx Properties (Сервис^Свойства ххх), после чего щелкните на вкладке Protection (Защита). Установите флажок Lock project for viewing (Блокировать проект для просмотра) и введите пароль (дважды). После этого щелкните на кнопке ОК.
Этот шаг необходим для того, чтобы ограничить доступ конечных пользователей к исходному коду макросов и диалоговых окон.
Несколькослов о безопасности
Microsoft никогда не рекламировала Excel как программный продукт, который предназначен для создания приложений с защищенным исходным кодом. Возможность добавления пароля в Excel представлена для ограничения доступа обычных пользователей к определенным фрагментам приложения. Excel 2002 предоставляет более высокий уровень безопасности, по сравнению с предыдущими версиями, но вероятность взлома установленной защиты все еще велика. Если вы не хотите, чтобы кто-либо видел исходный код или формулы приложения, то не стоит избирать Excel в качестве инструмента разработки.
4.Повторно активизируйте Excel и выберите Файл^Свойствз. Перейдите на вкладку Документ и введите краткий описательный заголовок в поле Название. Более полное описание можно ввести в поле Заметки.
Этот шаг не является обязательным, но делает надстройку проще в использовании, так как введенный текст описания отображается в диалоговом окне Надстройки.
5.Выполните команду Файл^Сохранить как.
6.В диалоговом окне Сохранение документа выберите Надстройка Microsoft Excel в выпадающем списке Тип файла.
7.Щелкните на кнопке Сохранить. Копия рабочей книги будет сохранена в виде файла с расширением * . x l a . Первоначальная рабочая книга будет оставаться открытой.
ЧастьV.Совершенныеметодыпрограммирования |
545 |
Рабочая книга, которая превращается в надстройку, должна содержать как минимум один рабочий лист. Например, если в рабочей книге расположены только листы диаграмм или диалоговые листы Excel 5/95, то в диалоговом окне Сохранение документа нельзя выбрать тип файла Надстройка Microsoft Excel. Более того, данный тип файла доступен только тогда, когда при выборе команды Файл1*Сохранить как активен рабочий лист.
Пример надстройки
В этом разделе будут рассмотрены инструкции по созданию надстройки {надстройка получена на основе утилиты Text Tools, которая рассматривалась в главе 16).
XLS-версия утилиты Text Tools доступна на Web-узле издательства, Можно использовать этот файл для создания описанной ниже надстройки.
Настройка рабочей книги
В приведенном далее примере будет использоваться уже отлаженная рабочая книга, состоящая из нескольких элементов.
•Рабочий лист, который называется ЛИСТСПРАВКИ, ЭТОТ ЛИСТ содержит текст справочной системы, который описывает утилиту.
•Пользовательское диалоговое окно — FormHelp. Это диалоговое окно применяется для отображения справочных сведений. Модуль кода данного диалогового окна содержит несколько процедур обработки событий.
•Пользовательское диалоговое о к н о — FormMain. Это диалоговое окно применяется для предоставления основного пользовательского интерфейса. Модуль кода для данного диалогового окна содержит несколько процедур обработки событии.
•Модуль кода VBA, который называется modMenus. В этом модуле содержится код, создающий и удаляющий элемент меню (Сервис^Текстовые инструменты).
•Модуль кода VBA, который называется modMain. Этот модуль содержит несколько процедур, включая процедуру отображения диалогового окна FormMain.
Кроме того, в модуле кода ThisWorkbook находятся две процедуры обработки событий (Workbook_Open и Workbook_Bef o r e C l o s e ) , которые вызывают другие процедуры для создания и удаления опций меню.
В главе 16 изложена дополнительная информация о принципах работы утилиты Text Tools.
Тестирование рабочей книги
Перед преобразованием этой рабочей книги в надстройку первую необходимо протестировать. Для создания условий, в которых будет работать книга после преобразования в надстройку, проверять ее работоспособность необходимо в то время, когда активной является другая рабочая книга.
546 |
Глава 21. Создание и использование надстроек |
Откройте новую рабочую книгу и воспользуйтесь некоторыми возможностями утилиты Text Tools. Выполните любые действия, чтобы заставить утилиту выдать сообщение об ошибке. Более удачным вариантом будет привлечение пользователя, который никогда ранее не работал с этим приложением. Необходимо, чтобы он содействовал неудачному завершению работы приложения.
Добавление описательной информации
Рекомендуется всегда добавлять к надстройке описаниеВыполните команду ФайлО Свойства, чтобы открыть диалоговое окно Свойства. После этого перейдите на вкладку Документ, которая показана на рис. 21.2.
Введите заголовок надстройки в поле Название. Этот текст представлен в диалоговом окне Надстройки. В поле Заметки необходимо ввести более полное описание надстройки. Эта информация будет отображена в нижней части диалогового окна Надстройки при выборе этой надстройки.
Рис. 21.2. Воспользуйтесь диалоговым окнам Свойства длявведенияинформации,описывающейнадстройку
Создание надстройки
Для того чтобы создать надстройку, выполните следующие действия.
1. Активизируйте редактор VBE и выберите рабочую книгу в окне проектов.
2. Выполните команду Debug "^Compile (Отладка11* Компилировать). Этот шаг приведет к компиляции кода VBA, что позволит обнаружить явные ошибки в исходном коде. После сохранения файла в виде надстройки Excel создаст надстройку даже в том случае, если исходный код содержит ошибки.
3.Выберите Tools^Text Tools Properties (Сервис=^Свойства Text Tools) и перейдите на вкладку Protection (Защита) в появившемся диалоговом окне. Установите флажок Lock project for viewing (Блокировать просмотр проекта) и введите пароль (дважды). Щелкните на кнопке ОК. Если необходимость в защите проекта отсутствует, то этот шаг можно опустить.
По умолчанию все проекты VBA называются VBProject . В этом примере имя проекта изменено на T e x t T o o l s .
4.Сохраните рабочую книгу.
5.Активизируйте рабочую книгу и выполните команду Файл^Сохранить как. Excel отобразит диалоговое окно Сохранение документа.
6.В выпадающем списке Тип файла выберите Надстройка Microsoft Excel.
7.Щелкните на кнопке Сохранить. Будет создана новая надстройка, при этом первоначальная версия рабочей книги будет оставаться открытой.
Часть V. Совершенные методы программирования |
547 |
