Excel — это наиболее часто используемое с точки зрения программирована приложение Office. По моему опыту преподавания курсов по программированию в Office, в подавляющем большинстве случаев сотрудников предприятий интересует, как автоматизировать выполнение операций именно в Excel Чаще всего на предприятиях встречаются следующие ситуации:
r необходимо автоматизировать загрузку данных в таблицу Excel из базы данных, а затем в автоматическом режиме произвести обработку этой таблицы (расчеты, моделирование и т. п.) и представить эту информацию в стандартном виде. На практике, конечно, намного правильнее было бы перенести выполнение расчетов (группировку, вычисление итогов по группам и т. п.) на сервер баз данных, но обычно у пользователей для этого нет ни необходимых знаний, ни прав для работы с сервером баз данных. По этому Excel в таких ситуациях остается незаменимым средством;
r вариант первой ситуации — приложение, работающее с базой данных, ужк умеет генерировать отчеты в формате файлов Excel. Но со временем потребности в отчетах изменяются, появляется необходимость в новых отчетах или в изменении старых. Чаще всего в этом случае пользователи самостоятельно создают новые отчеты, используя данные из старых. Повторяющихся действий очень много, поэтому автоматизация таких операций бывает просто необходима;
r очень часто пользователи, не имея возможности обратиться к профессиональным программистам, самостоятельно реализуют нужные им приложения в таблицах Excel. Во множестве организаций, например, финансово< планирование или составление смет ведется просто в виде множеств файлов Excel (часто связанных между собой). Excel выполняет и роль базы данных, и роль клиентского приложения, и генератора отчетов. В таких ситуациях, конечно, опять-таки вопросы автоматизации стоят очень остро;
r формат файлов Excel удобен не только для вывода информации из базы данных, но и для загрузки введенной вручную информации в базу данных. Часто на предприятиях информация из филиалов, подразделений, от сотрудников и т. п. собирается в формате Excel. В результате со временем возникает вопрос — как автоматизировать процесс загрузки информации из Excel в базу данных;
r по моему опыту, на предприятиях часто возникает потребность в синхронизации информации между файлами Excel и базами данных (или другими файлами Excel, или файлами DBF и т. п.). Например, нужно сделать так. чтобы при занесении пользователем информации в файл Excel она сразу же добавлялась в базу данных.
Приемы, необходимые для решения подобных задач, рассматриваются в данной главе. Надеемся, что после ее изучения у вас не возникнет проблем с тем. как их решать.
С программной точки зрения Excel, в отличие от Word, чаще всего используется не для вывода и редактирования данных, а для выполнения различных расчетов и отображения их в специальных форматах (график, сводная таблица и т. п.). Если же объем данных большой (например, нужно хранить информацию по заказчикам, договорам или поставкам, то имеет смысл подумать о связке Excel плюс база данных (такая связка может быть очень удобной и производительной).
По сравнению с программным перемещением по документам Word навигацию по книгам и листам Excel производить намного удобнее, поскольку каждой ячейки есть свой адрес (и даже два адреса— в формате А1 и в формате R1C1). Кроме того, в Excel есть возможность присваивать имена диапазонам ячеек, что также очень удобно.
Иерархия стандартных объектов в Excel немного больше. Если в Word все построено вокруг трех объектов: Application— Document — Range, то есть Excel появляется новый элемент— лист, поэтому главная его иерархия выглядит следующим образом: Application— Workbook (книга)— Worksheet (лист) — Range (диапазон).
В Excel предусмотрена очень богатая библиотека встроенных функций (статистических, финансовых, математических и т. п.), которые можно использовать в приложениях. Часто именно наличие такой библиотеки функций оказывается решающим при выборе Excel в качестве платформы для построенная приложения.
В Excel встроено несколько фактически внешних приложений, использование • оторых может быть очень удобным. Например, сводная таблица (объект PivotTable)— интегрированный в Excel OLAP-клиент приобретенной Microsoft фирмы Panorama Software, QueryTable— специальный объект для работы с информацией из базы данных, объект Chart— средство работы с диаграммами и т. п.
11.2. Объект Application
Как и в Word, объект Application в Microsoft Excel представляет все приложение Excel и находится на самом верхнем уровне объектной модели Excel. Если вам потребуется вызвать Excel из другого приложения, вам нужно будет создать объект Excel.Application (не забудьте при этом при помощи меню Tools | Referencesдобавить ссылку на библиотеку Microsoft Excel 11.0 Object Library). Создание этого объекта может выглядеть так:
Dim oExcel As New Excel.Application
oExcel.Workbooks.Add
oExcel.Visible = True
Точно так же, как и в Word, если вы работаете из уже запущенного Excel, создавать объект Application вам не потребуется. Он будет доступен всегда. Если вы обращаетесь к какому-либо свойству без указания вышестоящего объекта, то редактор Visual Basic в Excel будет считать, что вы обращаетесь к свойству объекта Application. Поэтому эти две строки кода в Excel равноначны:
Application.Workbooks.Add
И
Workbooks.Add
Вообще объекты Application в большинстве приложений Office очень похожи между собой, и к ним применяются те же соображения, что и для объекта Word.Application. Точно так же многие разработчики считают, что удобнее и надежнее работать со скрытым окном Excel, что открывать новый экземпляр Excel удобнее, чем разыскивать уже открытый пользователем. Для того чтобы в окне редактора кода для форм появился объект Application, точно так же необходимо в разделе Declarations кода формы объявить объект Application с ключевым словом WithEvents, например, так:
Public WithEvents App As Excel.Application
В этом случае в окне редактора кода для форм у вас появится новый объект Арр, и вы сможете использовать событийные процедуры объекта Application (рис. 11.1).
Рис. 11.1. В списке объектов появился новый объект Арр со своим набором событий
11.3. Свойства и методы объекта Application
Многие свойства, методы и события объекта Excel.Application совпадают с Word. Application. Однако т. к. здесь информация приводится для справки для тех пользователей, которым трудно читать по-английски, приведу наиболее часто используемые свойства и методы объекта Application в Excel, вне зависимости от того, встречались ли они нам в Word или нет.
Вначале о свойствах объекта Application.
r Свойства с префиксом Active... — возвращают активную ячейку (ту, на которую указывает курсор ввода данных), активную диаграмму, активный лист, активную книгу или активное окно. Все эти свойства доступны только для чтения. Собственно говоря, использовать их для создания объектов совсем не обязательно — объекты ActiveCell, ActiveSheet и т. п. создаются автоматически во время работы приложения и доступны всегда. Немного отличается свойство ActivePrinter — оно позволяет не только вернуть, но и установить активный принтер.
r Addins — возвращает одноименную коллекцию надстроек (объектов Addin). В отличие от Word, где в большинстве случаев применение надстроек предназначено для профессиональных программистов, в Excel работа с этим объектом имеет практическое значение для многих пользователей. Вместе с Excel поставляется несколько очень полезных надстроек (на графическом экране они доступны через меню Сервис | Надстройкинапример, Мастер подстановок, Пакет анализа, Поиск решенияи т. п При помощи этой коллекции можно проверить, подключена ли пользователем нужная надстройка (если она нужна в вашей программе) и в случае необходимости подключить ее автоматически.
r AutoRecover — возвращает одноименный объект, который позволяет определить параметры автосохранения Excel. Например, чтобы открытые документы Excel автоматически сохранялись каждые 5 минут, можно использовать код:
Application.AutoRecover.Time =5
Время указывается в минутах, можно использовать значения в интервале от 1 до 120. На графическом экране то же самое можно сделать при помощи меню Сервис | Параметрына вкладке Сохранениеокна Параметры.
r Calculation — позволяет узнать или настроить режим пересчета рабочей книги (по умолчанию установлен автоматический режим, можно также использовать ручной пересчет или полуавтоматический, когда автоматически пересчитывается все, кроме таблиц). Есть смысл отключать автоматический пересчет тогда, когда пересчет значений после каждого изменения ячейки занимает много времени и мешает вводу данных. То же самое на графическом экране можно настроить при помощи меню Сервис | Параметры,вкладка Вычисленияокна Параметры(явно дать команду на пересчет можно клавишей <F9>).
r CalculationState — позволяет проверить, занимается ли Excel пересчетом данных или пересчет уже завершен.
r Cells — одно из самых важных свойств объекта Application. Оно возвращает объект Range, представляющий собой все ячейки в активном листе активной книги. Поскольку свойство по умолчанию (т. е. свойство, название которого можно опускать) для объекта Range — это свойство Item, то обращение к ячейкам активного листа может выглядеть так:
Application.Cellsd(1, 2).Font.Bold = True
В данном случае мы выделили полужирным ячейку на пересечении первой строки и второго столбца.
Очень похоже действуют свойства Сolumns и Rows. Например, чтобы проделать подобную операцию со всем вторым столбцом, можно использовать команду вида:
Application.Columns(2).Font.Bold = True
а для второй строки можно воспользоваться похожей командой:
Application.Rows(2).Font.Bold = True
Еще раз отметим, что свойства Сells, Сolumns и Rows возвращают вовсе не наборы объектов Cell, Column и Row, как считают многие пользователи, а наборы объектов Range. На использовании объекта Range построена в Excel почти вся работа с ячейками и их значениями. Далее в этой главе объект Range будет посвящен отдельный разд. 11.6.
r Cursor — это свойство позволяет поменять внешний вид указателя мыши в Excel (у объекта Application в Word этого свойства почему-то нет). Обычно перед выполнением длинной расчетной операции курсору придают вил песочных часов (xlWait), а потом возвращают обратно. Автоматически по завершению работы макроса курсор не возвращается к нормальному виду, поэтому нужно предусмотреть соответствующий код.
r DataEntryMode — очень интересное свойство, которое может уберечь пользователя от множества ошибок. Оно позволяет перейти в режим ввода данных (data entry mode), когда пользователю разрешается только вводить данные в разблокированные ячейки выбранного диапазона. Всего в вашем распоряжении три варианта: хlOn— включить этот режим, xlStrict — включить и сделать так, чтобы пользователь не мог из него выйти при помощи клавиши <Esc>, xlOff — отключить режим.
r DecimalSeparator и ThousandsSeparator — эти свойства позволяют не полагаться на региональные настройки на компьютере пользователя, а явно назначить символы, которые будут отделять дробную часть числа от целой и тысячи друг от друга. При использовании этих свойств рекомендуется также отключить использование системных установок при помощи свойства UseSystemSeparators:
Application.UseSystemSeparators = False
r Dialogs — возвращает одноименную коллекцию Dialog, которую можно использовать для отображения диалоговых окон Excel (их предусмотрено несколько сотен) и определять реакцию на действия в них пользователей. На этот объект очень похож объект FileDialog, представляющий окна, предназначенные только для работы с файлами (например, окно открытия файла). Работа с ними выглядит точно так же, как в Word.
r DispiayAierts — свойство, про которое мы уже говорили в модуле про Word, но по причине его большой важности повторим еще раз. Это свойство позволяет отключить показ различных предупреждений, которые пользователю обычно в ходе работы приложения показывать не надо (например, подавить предупреждение при закрытии ненужного файла, в котором не были сохранены изменения).
r EnableEvents — позволяет на время отключить события для объекта Application, чтобы они не срабатывали (обычно перед выполнением какого-то действия — открытия файла, сохранения и т. п.).
r ErrorCheckingOptions — возвращает ссылку на одноименный объект, при помощи свойств которого можно настроить параметры автопроверки Excel (сообщать ли пользователю о синтаксически неверных формулах, ссылках на пустые ячейки и т. п.). По умолчанию большинство проверок включено, и к этому объекту есть смысл обращаться только тогда, когда вы хотите их отменить.
r FileDialog— позволяет обратиться к диалоговым окнам открытия и сохранения файлов (то же самое можно сделать при помощи более общего свойства Dialogs). Например, чтобы предоставить пользователю выбрать единственный файл в окне открытия и получить полный путь к нему, можно воспользоваться кодом:
Похожий пример с возможностью выбора сразу нескольких файлов приведен в справке по этому свойству.
r FileSearch — это свойство позволяет провести поиск по указанному вами каталогу и вывести результат.
r Interactive — позволяет полностью заблокировать ввод в приложение Excel со стороны пользователя (как клавиатуру, так и мышь). Обычно используется, чтобы пользователь не смог помешать работе приложения, например, сбить выделение. Это свойство можно также использовать, если ввод пользователя производится из другого приложения, взаимодействующего с Excel.
r International и LanguageSettings — работают точно так же, как и в Word.
r LibraryPath— возвращает путь к каталогу, где лежат файлы надстроек Excel с расширением xla. По умолчанию \Office11\Library.
r MoveAfterReturn— позволяет включить или отключить переход на следующую ячейку после завершения ввода данных и нажатия <Enter> (по умолчанию включен), а свойство MoveAfterReturnDirection позволяет определить направление перехода. В некоторых ситуациях это может сильно упростить ввод данных пользователем. Например, чтобы переход происходил на ячейку справа, можно использовать команду:
Application.MoveAfterReturnDirection = xlToRight
r Names — возвращает коллекцию Names, представляющую собой все именованные диапазоны в активной рабочей книге. При помощи метода Add о коллекции Names вы можете также сами определять в рабочей книге свои именованные диапазоны. На практике именованные диапазоны работают примерно так же, как закладки в Word — с их помощью очень удобно определять наборы данных в сложных таблицах Excel. На графическом экране в Excel определить именованные диапазоны можно при помощи меню Вставка| Имя.
r ODBCErrors и OLEDBErrors — позволяют получить информацию о возникших ошибках при подключении к базам данных ODBC и OLE DB соответственно. Они возвращают .одноименные коллекции, которые состоят из объектов ODBCEггог и OLEDBError соответственно, которые и содержат информацию об ошибке.
r OnWindow — это свойство больше похоже на событие. В качестве его значения указывается имя процедуры, которая должна находиться в модуле уровня книги (по умолчанию такой модуль не создается, его нужно создать вручную). Эта процедура будет вызываться всякий раз, когда пользователь переключился в окно Excel (не важно какой книги и какого листа). Вместо этого свойства можно использовать макросы со специальными: именами Auto_Activate и Auto_Deactivate (если вы определили и то, и другое, первой отработает процедура, определенная при помощи свойства OnWindow).
r Range — очень важное свойство. Возвращает объект Range, который представляет собой диапазон ячеек и используется в Excel практически для любых операций с ячейками.
r Referencestyle — позволяет переключать режим отображения ячеек между А1 (буквы— столбцы, цифры— строки) и R1C1 (когда и строки, столбцы обозначаются цифрами). На графическом экране это можно сделать через меню Сервис | Параметрына вкладке Общиеокна Параметры,установив или сбросив флажок Стиль ссылок R1C1.На практике пользователям ближе стиль вида А1, а программистам, конечно, R1C1. (особенно в тех ситуациях, когда столбцов очень много и приходится использовать столбцы АА, АВ и т. п.). Во многих ситуациях перед выполнением какой-то программной операции бывает удобно вначале перевести, режим отображения в R1C1, а после окончания на радость пользователям вернуть его обратно. Можно этим и не заниматься, а использовать другие способы для отсчета определенного количества столбцов.
r Selection — как несложно догадаться, это свойство возвращает то, что в настоящий момент выбрал пользователь. Если он выбрал обычные ячейки в таблице, то вернется объект Range. Если же пользователь выбрал что-то на диаграмме, то может вернуться объект осей, легенды и т. п., в зависимости от того, что было выбрано.
r Sheets — это свойство мы будем подробнее разбирать в разд. 11.5, посвященном книгам Excel. Оно возвращает коллекцию sheets — набор листе книги и набор диаграмм, которые находятся на отдельных листах. Если используется свойство Worksheets, то вернется та же коллекция Sheets, но уже состоящая только из объектов Worksheet — обычных листов (без диаграмм).
r TempiatesPath — свойство для чтения, при помощи которого можно получить информацию о каталоге с шаблонами Excel (например, для размещения собственного шаблона или открытия шаблона из этого каталога). По умолчанию используется каталог Application Data\Microsoft\Templates в профиле пользователя.
r ThisCeil и ThisWorkbook— очень удобные свойства, которые позволяют обращаться к текущей ячейке и к текущей книге, не обременяя себя созданием объектных переменных. Эти объекты создавать не нужно — они и так изначально существуют в работающем Excel.
r Windows, Workbooks и Sheets — возвращают, соответственно, все открытые окна, книги и листы Excel. Про объекты рабочей книги и листа мы будем говорить в разд. 11.5.
r WorkSheetFunction— позволяет использовать в программе на VBA функции Excel напрямую, без необходимости прописывать их в какую-либо ячейку.
Самые важные методы объекта Excel.Application перечислены далее.
r ActivateMicrosoftApp () — специальный метод, который предназначен для запуска и активизации (или просто активизации, если приложение уже было запущено) приложений Office (Word, Access, PowerPoint) и некоторых других (Project, FoxPro, Schedule Plus).
r AddCustomList() — создает новый пользовательский список. В качестве параметра принимает либо объект Range (элементами списка станут те значения, которые есть в диапазоне), либо стандартный объект Array. Удалить список можно при помощи метода DeleteCustomList().
r Методы с префиксом Сalculate... — позволяют пересчитать значения в ячейках. Простой метод Сalculate() — это обычный пересчет значений (чаще всего нужен, если автопересчет отключен), CaicuiateFull) пересчитывает значения во всех открытых книгах, CalculateFullRebuild() — еще и производит перестроение формул (аналогично занесению всех формул заново). Остановить пересчет можно при помощи метода CheckAbort().
r ConvertFormula() — преобразовывает формулу двумя способами: либо переводит адресацию ячеек в другой режим (например, вместо А1 в R1C1), либо меняет абсолютные ссылки на относительные и наоборот. В качестве параметра принимает, строковую переменную с текстом формулы (должна начинаться с символа '=') и флаги конвертации.
r DoubleСlick() — этот метод эквивалентен двойному щелчку мышью на активной ячейке, т. е. переход в режим ввода данных в эту ячейку.
r Evaluate() — очень полезный и часто используемый метод. Позволяет по имени найти объект книги Excel и преобразовать его в объект или значения для дальнейшего использования. В качестве имен этот метод может принимать:
• имена ячеек в стиле А1 (возвращается объект cell);
• имена диапазонов (возвращается объект Range);
• имена, определенные в макросе (чаще всего названия переменных);
• ссылки на внешние книги, например:
Evaluate("[Bookl.xls]Sheet1!A5"))
Этот метод можно вызвать и неявно, просто заключив имя объекта в квадратные скобки. Например, такие строки будут абсолютно одинаковыми по значению:
[al].Value = 25
Evaluate("Al").Value = 25
Поскольку синтаксис с квадратными скобками короче, чаще всего используется именно он.
Таким образом, метод Evaluate о — это самый простой и естественный метод обратиться к ячейке или диапазону в своей или чужой книге Excel.
r GetOpenFilename() — это упрощенный способ работы с окном открытия файлов. Чтобы открыть диалоговое окно и получить информацию о том. что выбрал пользователь (в виде строковой переменной с информацией с имени файла с полным путем), можно использовать такой код:
Filename = Application.GetOpenFilename()
If Filename <> False Then
Debug.Print Filename
End If
r GetSaveAsFilename() — такой же по функциональности способ, который работает с окном Сохранить как.
r GoTo()— важный и часто используемый метод. Принимает в качестве параметра объект Range, строку со ссылкой на ячейку (в формате R1C1) или имя процедуры VBA, выделяет и активизирует диапазон или ячейку или запускает на выполнение процедуру. В отличие от Select(), этот метол еще и автоматически активизирует лист, на котором расположены диапазон или ячейка.
r Help() — позволяет открыть и показать информацию из файла справки (в том числе пользовательского). В качестве параметра принимает имя файла справки и метку темы в нем.
r Intersect() — возвращает диапазон, который представляет собой область пересечения двух или более диапазонов. Если передаваемые в качестве параметров диапазоны не пересекаются, возвращается Nothing.
r ОпКеу() — этот метод позволяет "посадить" процедуру VBA на определенную клавиатурную комбинацию. Например, чтобы назначить процедуру Msg()из модуля лист клавиатурной комбинации <Alt>+<M>, можно воспользоваться командой:
Application.OnKey "%{m}", "Лист1.Msg"
r OnRepeato — позволяет назначить процедуру, которая будет выполняться при использовании команды Повторитьв меню Правка.Назначение процедуры команде Отменитьв том же меню производится при помощи метода OnUndo().
r RegisterXLL() — подключает файл надстройки Excel с расширением xll и регистрирует его функции и процедуры. Этот метод используется при установке собственного приложения.
r Repeat() — позволяет просто повторить последнее действие, которое было выполнено пользователем (не программным способом). Аналогично команде Повторитьв меню Правка.Отменить действие пользователя можно при помощи метода Undo ().
r Run() — позволяет выполнить процедуру или функцию VBA, макрос Excel или процедуру или функцию в XLL-модуле (и передать до 30 параметров).
r SendKeys() — позволяет эмулировать нажатия клавиш и передать их в активное окно приложения. Обычно используется, если нужно что-то продемонстрировать пользователю или что-то сделать через меню (например, не удалось найти, как это можно выполнить с помощью кода).
r Union() — позволяет объединить два или более диапазонов.
r Volatile() — этот хитрый метод должен вызываться только из пользовательской функции, которая вычисляет значение ячейки. Если он вызван и ему передано значение True, то данная ячейка становится чувствительной (volatile) и будет пересчитываться при любом изменении значений в листе, даже том, которое ее не касается.
r Wait() — этот метод позволяет приостановить работу Excel на указанное вами время, сняв нагрузку с процессора. Используется для демонстраций, чтобы пользователь успел увидеть, что происходит, для ожидания завершения выполнения какой-либо внешней операции и т. п. При этом ввод пользователя блокируется, а указатель мыши приобретает вид песочных часов. Однако некоторые фоновые операции Excel, такие как печать и пересчет значений, будут продолжать выполняться. Например, чтобы взять паузу на пять секунд, можно воспользоваться кодом:
If Application.Wait(Now + TimeValue("0:00:5")) Then
MsgBox "Пять секунд прошло"
End If
r Quit() и onTime() — делают то же самое, что и в Word.
11.4. Коллекция Workbooks и объект Workbook, их свойства и методы
Следующий по иерархии после Application объект в объектной модели Excel — это объект Workbook, который представляет собой книгу Excel. Можно сказать, что объект Workbook занимает в Excel примерно то же место, что и объект Document в Word — он необходим для получения ссылки на нужную нам книгу в наборе открытых книг Excel, для настройки общих свойств и выполнения общих действий со всеми листами книги. Получить этот объект можно очень просто:
r первый способ — воспользоваться коллекцией Workbooks, которая доступна через свойство Workbooks объекта Application. Впрочем, применять это свойство совершенно не обязательно — коллекция Workbooks в Excel и так постоянно доступна. Найти нужную книгу в этой коллекции можно по ее имени или номеру в коллекции, например:
Debug.Print Workbooks("Cметa.xls").FullName
r второйспособ – использовать свойство Application.ActiveWorkbook. При помощи этого свойства мы обращаемся к активной в настоящей момент книге:
Debug.Print ActiveWorkbook.Name
r третийспособ— использовать свойствоApplication.ThisWorkbook. Приэтом мы обращаемся к книге, которой принадлежит данный программный модуль:
Debug.Print ThisWorkbook.Name
На практике чаще всего нам нужно либо создать в Excel новую книгу, либо открыть существующую книгу (или другой файл в формате, который понимает Excel, например, DBF). Для этой цели используются методы Add() и Open() соответственно. Например, создать новую книгу в Excel можно так:
Dim oWbk As Workbook
Set oWbk = Workbooks.Add()
Единственный необязательный параметр, который принимает этот мзетод,— имя шаблона, на основе которого создается новая рабочая книга.
Открытие существующей книги выглядит так:
Dim oWbk As Workbook
Set oWbk = WorkBooks.Open("С:\mybookl.xls")
Кроме стандартных, в коллекции workbooks предусмотрено также три специальных метода.
r OpenDatabase() — открывает базу данных, выполняет запрос к ней (или напрямую открывает таблицу или представление), а результаты запроса помещает как импортированные внешние данные в новую автоматически созданную рабочую книгу Excel;
r OpenText() — почти то же самое, но в качестве источника здесь выступает текстовый файл. Дополнительные параметры позволяют определить его формат.
r ОреnXML() — в качестве источника данных будет выступать файл в формате XML.
Как и метод InsertDatabase() в Word, эти методы следует использовать только в самых простых случаях. Рекомендуется по возможности применять более мощные и стандартные средства объектной модели ADO.
Теперь о самых важных свойствах объекта Workbook — самой рабочей книги.
r Name, CodeName, FulLName— разные имена этой книги. Самое простое имя — Name, которое совпадает с именем файла книги. FullName — это имя файла книги вместе с полным путем к нему в операционной системе. CodeName — как эта книга называется в коде. CodeName можно посмотреть в окне Project Explorerили, если открыть свойства книги в окне Properties,кодовое имя книги будет представлено в строке (Name).Все три свойства доступны только для чтения, менять их можно другими способами (например, сохраняя файл под другим именем или изменив свойство в окне Properties).
Определенное отношение к именам имеет также свойство Path, которое возвращает полный путь, в файловой системе к книге Excel.
r Charts, Sheets, ActiveChart, ActiveSheet, CustomViews, BuiltinDocumentProperties и CustomDocumentProperties, Windows, WebOptions — возвращают одноименные коллекции соответствующих объектов. Некоторые из них будут рассматриваться в следующих разделах.
r СonflictResoiution— определяет, как будут разрешаться конфликты изменения данных, если книга открыта сразу несколькими пользователями (shared workbook). Есть возможность сделать так, чтобы локальный пользователь автоматически выигрывал, автоматически проигрывал или возникало диалоговое окно с возможностью разобраться в конфликте вручную. Существует большое количество свойств, которые позволяют настроить параметры совместной работы с книгой, но по причине того, что такая работа не рекомендуется (данные для совместного доступа необходимо переносить в базу данных), рассматриваться они здесь не будут, за исключением:
• запрещать или разрешать общий доступ к рабочей книге можно при помощи методов SaveAs() или ExclusiveAccess();
• по умолчанию возможность совместного редактирования для книги отключена (проверить можно при помощи свойства MultiUserEditing);
• получить список всех пользователей (а также информацию, когда они открыли файл и в каком режиме) можно при помощи свойства UserStatus.
r FileFormat— возвращает формат книги (доступен напрямую только для чтения, можно изменять при сохранении книги). Форматов очень много: разные версии Excel, DBF, Lotus 1-2-3, форматы TXT, CSV, XML— всего несколько десятков.
r Names — возвращает коллекцию всех именованных диапазонов в данной рабочей книге. Получить информацию о таких диапазонах можно, например, так:
For Each Item In ThisWorkbook.Names
Debug.Print Item.Name Next
Это свойство удобно использовать для предварительных проверок для устранения потенциальных ошибок времени выполнения.
Методов у объекта Workbook также очень много, однако самые часто используемые из них — это Activate(), Close(), Save(), SaveAs(), PrintOut() Protect() и Unprotect(), которые очевидны и действуют аналогично одноименным методам объекта Document в Word.
■
11.5. Коллекция Sheets и объект Worksheet, их свойства и методы
В Word на уровне ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом: Selection, Range и т. п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект— WorkSheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.
Чаще всего для ввода данных в Excel (напрямую или из базы данных) нам потребуется, в первую очередь, определиться с листом, на который будет выполняться ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать.
Процесс создания нового листа выглядит очень просто:
Dim oExcel As New Excel.Application 'Запускаем Excel
oExcel.Visible = True 'Делаем его видимым
Dim oWbk As Excel.Workbook
Set oWbk = oExcel.Workbooks.Add() 'Создаем новую книгу
Dim oSheet As Excel.Worksheet
Set oSheet = oWbk.Worksheets.Add() 'Создаем новый лист
oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"
Метод Add() для коллекции WorkSheets может принимать несколько необязательных параметров, главная задача которых — определить, между какими существующими листами книги будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.
Часто встречается и другая задача — найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция worksheets умеет работать с именами листов. Далее приведен пример, в котором мы запускаем Excel и создаем новую книгу, но при этом находим лист с именем "Лист1" и переименовываем его в "Новый лист":
Dim oExcel As New Excel.Application 'Запускаем Excel
oExcel.Visible = True 'Делаем его видимым
Dim oWbk As Excel.Workbook
Set oWbk = oExcel.Workbooks.Add() 'Создаем новую книгу
Dim oSheet As Excel.Worksheet
Set oSheet = oWbk.Worksheets.Item("Лист1")'Находим Лист1
oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"
Обратите внимание, что в английской версии Excel этот код не пройдет, поскольку листы там по умолчанию называются "Sheetl", "Sheet2" и т. п. Если вы используете в коде имена листов, заданные по умолчанию, и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.
r Visible— позволяет спрятать лист от пользователя (например, если он используется для служебных целей).
Некоторые важные методы объекта worksheet представлены далее.
r Activate(), Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select (), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect()— эти методы нам уже знакомы. Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
r PivotTables() — возвращает коллекцию очень интересных объектов PivotTable (сводная таблица), которые будут рассматриваться в разд. 11.8.
r Scenarios() — возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных вариантов (разные суммы продаж, уровни налогов, расходов и т. п.).
r SetBackgroundPicture() — позволяет назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачным, как "водяной знак", иначе на его фоне будет трудно читать текст в ячейках).
r ShowAllData() — показывает все скрытые и отфильтрованные данные на листе.
Самое важное событие объекта WorkSheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения в ячейке должно приводить к изменению значения в ячейке другого листа или рабочей книги Excel, или даже в базе данных. Другая ситуация, в которой используется это событие, — сложная проверка вводимого пользователем значения (например, когда это значение сверяется со значением в базе данных). Эта событийная процедура работает со специальным параметром Target, т. е. с объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение, и т. п.
У объекта WorkSheet есть еще два очень удобных события (их сильно не хватает объекту Document в Word)— ЭТО BeforeRightClick() И BeforeDoubleclick(). Как понятно из названий, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе событие — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т. п.) на действия пользователя.
11.6. Объект Range, его свойства и методы
Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Он может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:
r если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
r если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
r если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — это получить объект Range, представляющий эту группу ячеек.
В базе знаний Microsoft (www.microsoft.com/support) есть статья под номером 291308, в которой описываются 22 способа получения объекта Range e Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные.
r Самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, WorkSheet и длясамого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку А1, можно так:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Range("Al")
А ссылка на диапазон ячеек с Al no D10 создается так:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Range("A1:D10")
С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:
Set oRangel = Worksheets("Лист1").Range("C1")
Set oRange2 = oRangel.Range("Bl")
oRange2.Value =20
пропишет значение 20 не в ячейку Bl, как можно было понять из кода, а в ячейку D1 (т. е. в ячейку В1 по отношению к виртуальному листу, начинающемуся c C1)
r Второй способ— воспользоваться свойством Cells объекта WorkSheet. Возможностей у этого свойства меньше — возвращается диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т. п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Cells(1, 4)
Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:
Dim oRange
Set oRange = Range(Cells(1, 1), Cells(5, 3))
r Третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены далее в этом разделе.
Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:
oRange.Value = "Мое значение"
Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Далее представлены некоторые самые часто употребимые свойства.
r Address — позволяет вернуть адрес текущего диапазона. Например, для предыдущего примера вернется $А$1:$С$5. Если в диапазоне только одна ячейка, то вернется значение вида $А$ 1. Этому свойству можно передать много разных параметров для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т. п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.
На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций и знака доллара.
Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:
На первый взгляд это кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он всегда идет первым символом) и вторым, а для номера строки — все, что находится после второго знака доллара. Найти второй знак доллара можно при помощи встроенной функции InStr(), a взять нужное количество символов, начиная с определенной позиции, проще всего при помощи встроенной функции Mid().
r AllowEdit — это свойство, доступное только для чтения, позволяет определить, сможет ли пользователь вносить исправления в данную ячейку (набор ячеек) на защищенном листе. Используется для проверок.
r Areas — очень важное свойство. Дело в том, что объект Range может состоять из несмежных наборов ячеек. Многие методы применительно к таким диапазонам ведут себя совершенно непредсказуемо или возвращают ошибки. Свойство Areas позволяет разбить подобные нестандартные диапазоны на набор стандартных. Созданные таким образом объекты Range будут помещены в коллекцию Areas. Это свойство можно использовать и для проверки "нестандартности" диапазона:
If Selection.Areas.Count > 1 Then
Debug.Print "Диапазон с несмежными областями"
End If
r Borders — возвращает ссылку на коллекцию Borders, при помощи которой можно управлять рамками для диапазона.
r Cells — это свойство, как мы уже видели, есть и у объекта WorkSheet. Для объекта Range оно работает точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:
Dim oRange, oRange2 As Range
Set oRange = Range (Cells (2,- 2), Cells (5, 3))
Set oRange2 =oRange.Cells(1, 1) 'Вместо Al получаем ссылку на В2
Debug.Print oRange2.Address 'Проверка
Точно такие же особенности есть у свойств Row и Rows, Column и Columns.
r Сharacters — это простое с виду свойство позволяет решать непростую задачу: как изменить фрагменты текста или их формат в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку А1 и изменить цвет первой буквы, можно воспользоваться кодом:
Dim oRange As Range
Set oRange = Range("Al")
oRange.Value = "Мой текст"
oRange.Characters(1, 1).Font.Color = vbRed
Если же вам просто нужно изменить значение, то лучше воспользоваться свойством value — как в третьей строке примера.
r Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
r CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании или экспорте данных, полученных из внешнего источника (когда нам изначально неизвестно, сколько будет данных). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (т. е. непустую область, в которую входит исходный диапазон или ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом:
Затем, к примеру, можно программным образом создать новый столбец справа от последнего имеющегося, или под последней заполненной строкой создать новую строку с итогами.
r Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области), представляющий ячейки, которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код:
Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использоватьсвойстваDirectDependents и DirectPrecedents.
r End— еще одно часто используемое свойство. Оно позволяет получить объект Range, представляющий последнюю ячейку исходного диапазона. В каком направлении будет возвращаться последняя ячейка, можно определить при помощи передаваемого параметра.
r Errors — свойство, которое через коллекцию Errors позволяет получить доступ к объектам Error, представляющим обнаруженные ошибки в диапазоне.
r Font — как и в Word, это свойство позволяет получить доступ к объект) Font, при помощи которого можно настроить особенности оформление текста в ячейке (цвет, шрифт, размер букв и т. п.).
r FormatСonditions — позволяет создать собственный объект, представляющий вариант оформления ячеек, который затем можно применять к разным ячейкам и диапазонам.
r Formula — одно из самых важных свойств объекта Range. Доступно и для чтения, и для записи. Возвращает текст формулы, прописанной в ячейку (а не вычисленное значение) или позволяет записать формулу в ячейку. Если применить это свойство для диапазона из нескольких ячеек, то формула будет прописана по всей ячейке диапазона. Пример использования этого свойства может выглядеть так:
r FormulaHidden— позволяет спрятать формулы от пользователя в данном диапазоне. Работает только на защищенных листах.
r HasFormula— проверяет диапазон на наличие вычисляемых значений (формул).
r Hidden— позволяет спрятать диапазон. Будет работать только в случае, если диапазон включает в себя хотя бы одну строку или столбец целиком, в противном случае вернется ошибка.
r Interior — еще одно свойство, связанное с форматированием. Позволяет выделить цветом ячейки диапазона.
r Item— возвращает еще один объект Range, который определяется путем смещения исходного диапазона.
r Locked — позволяет заблокировать ячейки диапазона при защите листа.
r Name — позволяет получить ссылку на специальный объект именованного диапазона Name. На графическом экране с его возможностями можно познакомиться при помощи меню Вставка| Имя.Он позволяет обращаться к диапазонам и формулам по именам и несколько напоминает по функциональности объект Bookmark в Word.
r Next — позволяет перейти на следующую ячейку. Если лист не защищен, то следующей ячейкой будет считаться ячейка справа, если лист защищен — то следующая незаблокированная ячейка.
r NumberFormat— устанавливает один из предопределенных форматов дл; чисел. Соответствует возможностям вкладки Числов меню Формат Ячейкина графическом экране.
r Offset — это свойство позволяет получить новый объект Range с определенным смещением относительно исходного диапазона. Например, чтобы получить ячейку со смещением на три ячейки вверх и три ячейки влево, можно использовать код:
r Orientation— позволяет сориентировать текст в ячейках. Определяет угол наклона в градусах. Например, чтобы расположить текст по диагонали, можно использовать код:
oRange.Orientation = -45
r PageBreak — это свойство обычно используется для программной вставки разрывов страницы. Его применение может выглядеть так:
r свойства с префиксом Pivot... — относятся к работе с объектом PivotTable (сводная таблица). Особенности работы с ним будут рассмотрены в разд. 11.8.
r QueryTable — это важное свойство позволяет получить ссылку на объект QueryTable — полученные с внешнего источника данные, которые находятся в данном диапазоне. Подробнее про объект QueryTable будет рассказано в разд. 11.7.
r Range — это свойство, как уже говорилось ранее, позволяет создать новый диапазон на основе уже существующего. Необходимо помнить про особенности нумерации ячеек в этом случае.
r Resize— позволяет изменить текущий диапазон. Например, увеличение его на один столбец вниз и на одну строку вправо можно выполнить так:
r ShrinkToFit— это свойство позволяет автоматически настроить размер текста в диапазоне таким образом, чтобы текст помещался в ширину столбца.
r Style — позволяет вернуть объект Style, представляющий стиль для указанного диапазона. На графическом экране то, что выполняет объект style, можно сделать через меню Формат| Стиль.
r Text — получает значение первой ячейки диапазона в виде значения типа String. Для объекта Range это свойство доступно только для чтения.
r Validation — это свойство позволяет вернуть объект Validation, при помощи которого можно настроить проверку вводимых в диапазон данных.
r Value — наиболее часто используемое свойство объекта Range. Позволяет получить или назначить значение (числовое, текстовое или какое-либо другое) ячейкам диапазона. Точно также используется свойство Value2, но с единственным отличием — оно не поддерживает типы данных Currency и Date.
r WrapText — позволяет включить или отключить перевод текста на следующую строку в ячейках диапазона.
Информация о методах объекта Range представлена далее.
r Activate() — выделяет текущий диапазон и устанавливает курсор ввода на его первую ячейку.
r AddComment() — добавляет комментарий к ячейке. Ячейка будет помечена красным уголком, а текст комментария будет показываться в виде всплывающей подсказки. Этот метод можно вызвать только для диапазона, состоящего из одной ячейки. То же самое на графическом экране можно сделать при помощи меню Вставка | Примечание.
r AutoFill() — позволяет использовать автозаполнение для диапазона (например, если первые две ячейки будут заполнены как 1 и 2, то дальше в автоматическом режиме будет продолжено: 3, 4, 5 и т. п.).
r AutoFit() — автоматически поменяет ширину всех столбцов и высоту всех строк в диапазоне, чтобы туда уместился текст ячеек. Можно применять только к тем диапазонам, которые включают в себя хотя бы одну строку или столбец целиком, иначе вернется ошибка.
r AutoFormat() — позволяет использовать один из стилей автоформатирования, которые на графическом экране доступны через меню Формат| Автоформат.
r BorderAround() — позволяет поместить диапазон в рамку с выбранными вами параметрами.
r Методы с префиксом Clear... — позволяют очистить содержимое диапазона от значений, форматирования, комментарий и т. п.
r Consolidate() — сливает данные нескольких диапазонов (в том числе на разных листах) в один диапазон, используя при этом выбранную вами агрегатную функцию.
r Cору() — копирует диапазон. Если место назначения не указано, то он копируется в буфер обмена. Аналогично работает метод Cut(), при котором данные исходного диапазона удаляются.
r CopyFromRecordset() — очень удобный метод, который позволяет вставить данные из объекта ado.Recordset на лист Excel, начиная с верхнего левого угла указанного диапазона.
r DataSeries() — метод, который поможет сэкономить множество времени и избежать возни с функциями даты и времени. Он позволяет увеличить значения дат на указанный вами временной интервал. Например, если у вас в ячейке стоит 1 января, то при помощи этого метода можно сгенерировать первое число любого другого месяца.
r Delete() — удаляет данные текущего диапазона. С помощью необязательного параметра можно определить, с какой стороны будут сдвигаться ячейки на место удаленных.
r Dirty() — помечает ячейки диапазона как "грязные". Такие ячейки будут пересчитаны при следующем же пересчете. Этот метод обычно используется, когда Excel сам не может догадаться, что их нужно пересчитать. Также пересчитать ячейки диапазона можно и принудительно при помощи метода Calculate().
r Методы с префиксом Fill... (FillDovn(), FillUp(), FillLeftt), FillRight()) — позволяют размножить одно и то же значение по ячейкам диапазона в указанном вами направлении.
r Find() — позволяет произвести поиск по ячейкам диапазона и вернуть новый объект Range, который представляет собой первую ячейку, в которой было найдено нужное значение. У этого метода есть множество необязательных параметров, которые позволяют определить направление поиска, чувствительность к регистру, искать ли значение ячейки целиком или как часть и т. п. Методы FindNext() и FindPrevious() позволяют продолжить поиск, начатый методом Find(), в разных направлениях.
r Goaiseek() — позволяет применить автоподбор значений для функции Excel программным способом. На графическом экране то же самое можно сделать при помощи меню Сервис | Подбор параметра.
r Insert() — позволяет вставить ячейки в диапазон, сдвинув остальные вправо или вниз.
r Justify() — позволяет равномерно распределить текст по диапазону. Если в данный диапазон текст не помещается, то он будет распространен на соседние ячейки (с перезаписью их значений).
r Merge() — позволяет слить все ячейки диапазона в одну. При этом останется только одно значение — верхней левой ячейки. Разбить обратно такую слитую ячейку на несколько обычных можно при помощи метода UnMerge().
r Parse() — позволяет разбить одну ячейку на несколько по указанному вами шаблону (например, чтобы отделить код города от номера телефона).
r PasteSpecial() — операция, дополняющая Cору() и Cut(). Она позволяет вставить то, что лежит в буфере обмена, с указанием специальных параметров вставки (вставлять с добавлением к существующим данным, с умножением, вычитанием, делением и т. п.).
r Printout() и PrintPreview() — позволяют вывести диапазон на печать или открыть режим просмотра перед печатью.
r Replace() — метод, дополняющий метод Find(). Позволяет проводить поиск и замену значений в диапазоне.
r Select() — выделяет указанный диапазон. Объекта Selection в Excel нет. вместо него есть возможность получить объект Range, представляющий выделенную область.
r Show() — экран будет пролистан таким образом, чтобы показать весь указанный диапазон.
r ShowDependents() — позволяет пометить стрелками те ячейки, которые зависят от указанного диапазона (только первый уровень зависимости) или убрать эти стрелки. Обратный метод — ShowPrecedents().
r ShowErrors() — показывает источник ошибки для указанной ячейки.
r Sort() — производит сортировку ячеек в диапазоне. Можно использовать большое количество необязательных параметров для настройки сортировки. SortSpecial() — сортировка с учетом особенностей азиатских языков.
r Speak() — удивительный метод, который позволяет прочитать вслух содержимое диапазона (можно определить, в каком направлении и будут ли зачитываться формулы). К сожалению, в локализованной версии Excel не работает.
r SpecialCells() — очень удобный метод, который позволяет вернуть объект Range, включающий в себя все ячейки определенного типа (пустые, с ошибками, с комментариями, последние, с константами, с формулами c определенным форматированием) и с определенным значением. Например, чтобы вернуть объект Range, состоящий из всех пустых ячеек диапазона, можно использовать код:
Set oRange2 = oRange.SpecialCells(xlCellTypeBlanks)
oRange2.Select 'Проверяем, так ли это
r SubTotal() — позволяет посчитать итоговое значение для диапазона (можно выбрать агрегатную функцию и множество других параметров).
r Table() — позволяет создать таблицу на основе передаваемого столбца, строки и функции, которую нужно использовать для вычисления ячеек таблицы. Пример в документации по этому методу позволяет автоматически сгенерировать таблицу умножения.
r TextToCoiuims() — сложный метод, который позволяет создать из значения одного столбца несколько столбцов в соответствии с определенные алгоритмом. Принимает множество необязательных параметров.
11.7. Коллекция QueryTables и объект QueryTable
Для большинства практических задач вполне хватает возможностей объектов Application, Workbook, Worksheet и Range. Например, для вставки информации из базы данных вы можете пройти циклом по объекту ado.Recordset и вставить все нужные записи в лист Excel, а затем средствами VBA прописать в нижние строки итоги по вставленным данным. Однако в Excel встроено несколько специальных объектов, которые могут сильно упростить работу в различных ситуациях. Например, ту же операцию по вставке информации из базы данных удобнее будет провести при помощи специального объекта QueryTable, который будет рассматриваться в этом разделе. Еще два таких специальных объекта PivotTabie и Chart будут рассматриваться в разд. 11.8 и 11.9.
Основное назначение объекта QueryTable — работа с набором значений, возвращаемых из базы данных. Этот объект доступен в Excel из графического интерфейса через меню Данные | Импорт внешних данных | Импортировать данные.При помощи объектов QueryTable вы можете разместить набор записей, полученных с источника данных, на листе Excel для выполнения с ним различных Операций (например, анализа при помощи богатой библиотеки функций, для построения диаграмм, отчетов и т. п.). QueryTable удобно использовать для "односторонней" работы с источником данных, когда данные только скачиваются с источника в Excel, но изменять данные и сохранять изменения на источнике не нужно. В принципе, в Excel такую возможность синхронизации изменений реализовать можно (например, при помощи перехвата события Change объекта WorkSheet), но намного проще и правильнее будет использовать для этой цели возможности Access. В этом разделе мы будем рассматривать только такую "однонаправленную" передачу данных из базы в Excel.
Как обычно, для того чтобы создать объект QueryTable и разместить его на листе, нужно использовать специальную коллекцию QueryTables, которая принадлежит рабочему листу (объекту WorkSheet) и доступна через его одноименное свойство. Свойства и методы объекта QueryTables стандартные, как у большинства рассмотренных нами коллекций. Подробного рассмотрения заслуживает только метод Add(), при помощи которого и создается объект QueryTable (с одновременным добавлением в коллекцию). Этот метод принимает три параметра:
r Connection— источник данных для QueryTable (в видеобъекта типаVariant). В качестве источника данных можно использовать:
• строку подключения OLE DB или ODBC (см. гл. 9);
• готовый объект Recordset, созданный стандартными средствами ADO или DAO. При этом можно изменять Recordset, на который ссылается QueryTable и обновлятьQueryTable. По многоим причинам это самыйудобный вариант при работе с QueryTable;
• другой объект QueryTable (вместе со строкой подключения и текстом запроса);
• текстовый файл;
• результаты Web-запроса или запроса Microsoft Query (в виде файла DQY или IQY). Создать такой файл запроса можно при помощи графических средств Excel в меню Данные | Импорт внешних данных Создать запрос.
r Destination — куда вставлять полученную QueryTable. Передается объект Range, и вставка производится, начиная с верхнего левого угла диапазона.
r SQL— при помощи этого необязательного параметра можно определить SQL-запрос, который будет выполняться на источнике данных ODBC Этот же запрос можно определить при помощи одноименного свойства объекта QueryTable.
Конечно, правильнее всего при создании QueryTable использовать готовый объект Recordset. В этом случае у нас будут и самые полные возможности настройки подключения и курсора, и возможность эффективного промежуточного хранения данных в оперативной памяти (в объекте Recordset), куда можно вносить изменения, и все удобные свойства и методы объект; Recordset. Код на создание объекта QueryTable на листе Excel может выглядеть так (мы используем тот же RecordSet на основе таблицы Northwind.Customers, что ив разд. 9.5):
Непосредственно помещение объекта QueryTable на лист производится при помощи метода QueryTable.Refresh(). Без него объект QueryTable будет создан только в оперативной памяти.
Информация о самых важных свойствах и методах объекта QueryTable представлена далее.
r BackgroundQuery— определяет, может ли выполнение запроса производиться в фоновом режиме, пока пользователь совершает в Excel другие действия. По умолчанию установлено в True. В False следует переводить только тогда, когда пользователь своими действиями в Excel может как-то помешать нормальной работе приложения.
r CommandText — текст команды SQL, т. е. текст запроса, который передается на источник. Существует совместно с аналогичным свойством SQL (которое оставлено для обратной совместимости) и имеет перед ним приоритет. При передаче QueryTable готового Recordset это свойство недоступно.
r СommandType — тип передаваемой в CommandText команды (вся таблица, SQL-запрос, имя куба OLAP и т. п.). При работе с готовым Recordset также недоступно.
r Connection— строка подключения, которую можно передать при вызове метода Add() коллекции QueryTables. Также при работе с готовым Recordset недоступно.
r Destination— второй параметр, который передается методу Add(). Возвращает объект Range, представляющий верхнюю левую ячейку диапазона, занимаемого на листе объектом QueryTable. После создания QueryTable доступен только для чтения.
r EnabieEditing — определяет, может ли пользователь изменять на графическом экране свойства объекта QueryTable. По умолчанию установлено в True. Если перевести в False, то пользователь сможет только обновлять QueryTable.
r EnabieRefresh —- определяет, может ли пользователь обновлять QueryTable, получая заново данные (с источника или из Recordset).
r FetchedRowOverflow — это свойство принимает значение True, если записи, полученные с источника, не поместились на листе Excel (т. е. было скачано больше, чем 65 536 записей). Ошибки в такой ситуации не возникает, поэтому если вы работаете с большими наборами записей, имеет смысл реализовать соответствующие проверки.
r FieldNames — очень полезное свойство. Позволяет отключить вставку полученных с источника названий столбцов в первую строку QueryTable. По умолчанию True — вставлять названия столбцов.
r MaintainConnection — определяет, будет ли соединение с источником открыто все время до закрытия листа. По умолчанию True — оптимизировано для выполнения частых обновлений. Если переставить в False, можно сэкономить оперативную память на клиенте за счет уменьшения скорости обновления данных.
r Name— имя объекта QueryTable (на графическом экране его можно посмотреть, если на панели управления Внешние данныенажать кнопку Свойства диапазона данных).По умолчанию задается как ExternalData номер.
r Parameters — позволяет получить доступ к коллекции Parameters (набор параметров запроса). Возможности практически такие же, как у параметров объекта Recordset.
r PreserveColumnlnfo и PreserveFormatting — определяют, сохранять ли информацию о столбцах (сортировке, фильтрации и т. п.) и форматировании после обновления QueryTable. По умолчанию все сохраняется.
r QueryType — позволяет выяснить, что использовалось при создании QueryTabie: Recordset, прямой доступ к таблице, SQL-запрос и т. п. Свойство доступно только для чтения.
r Recordset — возвращает ссылку на объект Recordset, который использовался для создания QueryTable, или позволяет заменить его для объекта QueryTable на другой (изменения вступят в силу только
