Vba excel querytable что это
Обновлено: 08.07.2024
Represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database.
The QueryTable object is a member of the QueryTables collection.
QueryType property
Different Types of Query
ADO Recordset
DAO Recordset
ODBC Query
OLE DB Query
Text Import
Web Query
AdjustColumnWidth property
BackgroundQuery property
True if queries for the PivotTable report or query table are performed asynchronously (in the background). Read/write Boolean.
For OLAP data sources, this property is read-only and always returns False.
This example causes queries for the first PivotTable report on worksheet one to be performed in the background.
CancelRefresh Method
Cancels all background queries for the specified query table. Use the Refreshing property to determine whether a background query is currently in progress.
Delete Method
Deletes the object.
Destination property
Returns the cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTable object. Read-only Range.
This example scrolls through the active window until the upper-left corner of query table one is in the upper-left corner of the window.
EnableEditing property
True if the user can edit the specified query table. False if the user can only refresh the query table. Read/write Boolean.
EnableRefresh property
True if the PivotTable cache or query table can be refreshed by the user. The default value is True. Read/write Boolean.
The RefreshOnFileOpen property is ignored if the EnableRefresh property is set to False.
For OLAP data sources, setting this property to False disables updates.
FetchedRowOverflow property
True if the number of rows returned by the last use of the Refresh method is greater than the number of rows available on the worksheet. Read-only Boolean.
FieldNames property
True if field names from the data source appear as column headings for the returned data. The default value is True. Read/write Boolean.
FillAdjacentFormulas property
True if formulas to the right of the specified query table are automatically updated whenever the query table is refreshed. Read/write Boolean.
ListObject property
Returns a ListObject object for the Range object or QueryTable object. Read-only ListObject object.
MaintainConnection property
True if the connection to the specified data source is maintained after the refresh and until the workbook is closed. The default value is True. Read/write Boolean.
You can set the MaintainConnection property only if the QueryType property of the query table or PivotTable cache is set to xlOLEDBQuery.
If you anticipate frequent queries to a server, setting this property to True might improve performance by reducing reconnection time. Setting the property to False causes an open connection to be closed.
Name Property
Returns or sets the name of the object. Read/write String.
Parameters property
Returns a Parameters collection that represents the query table parameters. Read-only.
PreserveColumnInfo property
True if column sorting, filtering, and layout information is preserved whenever a query table is refreshed. The default value is False. Read/write Boolean.
This property has an effect only when the query table is using a database connection.
You can set this property to False for compatibility with earlier versions of Microsoft Excel.
PreserveFormatting property
This example demonstrates how setting PreserveFormatting to False causes the AutoFormat to be set to xlRangeAutoFormatNone instead of the specified xlRangeAutoFormatColor1 format.
Recordset property
Refresh Method
Updates an external data range (QueryTable). Boolean.
The following remarks apply to QueryTable objects that are based on the results of a SQL query.
If the query requires parameters, the Parameters collection must be initialized with parameter binding information before the Refresh method is called. If not enough parameters have been bound, the Refresh method fails with the Parameter Error exception. If parameters are set to prompt for their values, dialog boxes are displayed to the user regardless of the setting of the DisplayAlerts property. If the user cancels a parameter dialog box, the Refresh method halts and returns False. If extra parameters are bound with the Parameters collection, these extra parameters are ignored.
The Refresh method returns True if the query is successfully completed or started; it returns False if the user cancels a connection or parameter dialog box.
To see whether the number of fetched rows exceeded the number of available rows on the worksheet, examine the FetchedRowOverflow property. This property is initialized every time the Refresh method is called.
This example refreshes the PivotTable cache for the first PivotTable report on the first worksheet in a workbook.
Refreshing property
RefreshOnFileOpen property
True if the PivotTable cache or query table is automatically updated each time the workbook is opened. The default value is False. Read/write Boolean.
Query tables and PivotTable reports are not automatically refreshed when you open the workbook by using the Open method in Visual Basic. Use the Refresh method to refresh the data after the workbook is open.
RefreshPeriod property
Returns or sets the number of minutes between refreshes. Read/write Long.
Setting the period to 0 (zero) disables automatic timed refreshes and is equivalent to setting this property to Null.
The value of the RefreshPeriod property can be an integer from 0 through 32767.
RefreshStyle property
Returns or sets the way rows on the specified worksheet are added or deleted to accommodate the number of rows in a recordset returned by a query. Read/write XlCellInsertionMode.
ResultRange property
RobustConnect property
Returns or sets how the PivotTable cache connects to its data source. Read/write XlRobustConnect.
RowNumbers property
SaveData property
True if data for the PivotTable report is saved with the workbook. False if only the report definition is saved. Read/write Boolean.
For OLAP data sources, this property is always set to False.
SavePassword property
True if password information in an ODBC connection string is saved with the specified query. False if the password is removed. Read/write Boolean.
This property affects only ODBC queries.
SourceConnectionFile property
Returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the PivotTable. Read/write.
Web Query
EditWebPage property
WebConsecutiveDelimitersAsOne property
WebDisableDateRecognition property
WebDisableRedirections property
True if Web query redirections are disabled for a QueryTable object. The default value is False. Read/write Boolean.
WebFormatting property
WebPreFormattedTextToColumns property
Returns or sets whether data contained within HTML <PRE> tags in the Web page is parsed into columns when you import the page into a query table. The default is True. Read/write Boolean.
This property is used only when the QueryType property of the query table is xlWebQuery and the query returns a HTML document.
WebSelectionType property
WebSingleBlockTextImport property
WebTables property
Web Queries
What are QueryTables ?
Returns the QueryTables collection that represents all the query tables on the specified worksheet. Read-only.
Each QueryTable object represents a worksheet table built from data returned from an external data source.
Refreshing
This example refreshes all query tables on worksheet one.
FillAdjacentFormulas
QueryType
Indicates the type of query used by Microsoft Excel to populate the query table or PivotTable cache. Read-only XlQueryType .
Connection
Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:
- если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
- если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
- если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.
В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:
- самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:
Dim oRange As Range
А на диапазон ячеек с A1 по D10 — так:
Dim oRange As Range
С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:
Set oRange1 = Worksheets("Лист1").Range("C1")
пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).
- второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:
Dim oRange As Range
Set oRange = Worksheets("Лист1").Cells(1, 4)
Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:
Set oRange = Range(Cells(1, 1), Cells(5, 3))
- третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.
Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:
Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:
- Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.
На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:
sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))
sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, "$") + 1))
На первый взгляд кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().
If Selection.Areas.Count > 1 Then
Debug.Print "Диапазон с несмежными областями"
- Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
- Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:
Dim oRange, oRange2 As Range
Set oRange = Range(Cells(2, 2), Cells(5, 3))
Set oRange2 = oRange.Cells(1, 1) 'Вместо A1 получаем ссылку на B2
Debug.Print oRange2.Address 'Так оно и есть
Точно такие же особенности у свойств Row и Rows, Column и Columns.
- Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом
Dim oRange As Range
Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.
- Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
- CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом
- Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
- Worksheets("Лист1").Activate
- ActiveCell.Dependents.Select
Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.
Некоторые сведения относятся к предварительной версии продукта, в которую до выпуска могут быть внесены существенные изменения. Майкрософт не предоставляет никаких гарантий, явных или подразумеваемых, относительно приведенных здесь сведений.
Представляет таблицу таблицы, построенную из данных, возвращаемой из внешнего источника данных, например SQL сервера или базы данных Microsoft Access. Объект QueryTable является членом QueryTables коллекции.
Комментарии
Используйте QueryTables(), где находится номер индекса таблицы запросов, чтобы вернуть один index объект index QueryTable.
Свойства
Верно, если ширина столбца автоматически корректируется в лучшую форму при каждом обновлении указанной таблицы запросов или XML-карты. False, если ширина столбца не настраивается автоматически с каждым обновлением. Значение по умолчанию — True. Для чтения и записи, Boolean.
Возвращает Application объект, который представляет Microsoft Excel приложение. Только для чтения.
True, если запросы для отчета pivotTable или таблицы запросов выполняются асинхронно (в фоновом режиме). Для чтения и записи, Boolean.
Возвращает или задает строку команды для указанного источника данных. Объект Read/write.
Возвращает или задает XlCmdType константы, описывая тип команды, связанный с CommandText свойством. По умолчанию значение xlCmdSQL. Чтение и написание XlCmdType .
Возвращает или задает строку, содержаную сведения о подключении к таблице запросов. Объект Read/write.
Возвращает константу в XlCreator переумериях, которая указывает приложение, в котором был создан этот объект. Если объект был создан в Microsoft Excel, это свойство возвращает строку XCEL, эквивалентную hexadecimal number 5843454C. Только для чтения XlCreator .
Возвращает ячейку в верхнем левом углу диапазона назначения таблицы запросов (диапазон, в котором будет размещена таблица запросов). Диапазон назначения должен быть на таблице, которая содержит QueryTable объект. Диапазон только для чтения.
Возвращает или задает локатор единого ресурса веб-страницы (URL-адрес) для веб-запроса. Объект Read/write.
True, если пользователь может изменить указанную таблицу запросов. False, если пользователь может обновить только таблицу запросов. Для чтения и записи, Boolean.
True, если кэш pivotTable или таблица запросов может быть обновлена пользователем. Значение по умолчанию — True. Для чтения и записи, Boolean.
True, если число строк, возвращаемого последним использованием метода, превышает количество строк, доступных Refresh(Object) на таблице. Только для чтения, Boolean.
True, если имена полей из источника данных отображаются в заголовках столбцов для возвращенных данных. Значение по умолчанию — True. Для чтения и записи, Boolean.
Верно, если формулы справа от указанной таблицы запросов автоматически обновляются при обновлении таблицы запросов. Для чтения и записи, Boolean.
Зарезервировано для внутреннего использования.
Возвращает объект ListObject для Range объекта или QueryTable объекта. Объект ListObject только для чтения.
True, если подключение к указанному источнику данных сохраняется после обновления и до закрытия книги. Значение по умолчанию — True. Для чтения и записи, Boolean.
Возвращает или задает имя объекта. Для чтения и записи, String.
Возвращает Parameters коллекцию, представляюную параметры таблицы запросов. Только для чтения.
Возвращает родительский объект для указанного объекта. Только для чтения.
Возвращает или задает строку, используемую с помощью метода ввода данных в веб-сервер для возврата данных из веб-запроса. Для чтения и записи, String.
True, если сведения о сортировке, фильтрации и макете столбцов сохраняются при обновлении таблицы запросов. Значение по умолчанию — False. Для чтения и записи, Boolean.
Это свойство True, если к новым строкам данных в таблице запросов применяется любое форматирование, общее для первых пяти строк данных. Неиспользованые ячейки не форматированы. Свойство false, если последний autoFormat, примененный к таблице запросов, применяется к новым строкам данных. Значение по умолчанию значение True (если таблица запросов не была создана в Microsoft Excel 97 и свойство True , в этом случае HasAutoFormat PreserveFormatting является false). Для чтения и записи, Boolean.
Указывает тип запроса, используемого Microsoft Excel для заполнения таблицы запросов или кэша PivotTable. Только для чтения XlQueryType .
Возвращает или задает объект Recordset, используемый в качестве источника данных для указанной таблицы запросов или кэша PivotTable. Для чтения и записи.
True, если для указанной таблицы запросов имеется фоновый запрос. Для чтения и записи, Boolean.
True, если кэш или таблица запросов pivotTable автоматически обновляется при каждом открываемом книге. Значение по умолчанию — False. Для чтения и записи, Boolean.
Возвращает или задает количество минут между обновлениями. Для чтения и записи, Integer.
Возвращает или задает путь к строкам указанного таблицы, которые добавляются или удаляются для размещения количества строк в наборе записей, возвращаемом запросом. Чтение и написание XlCellInsertionMode .
Возвращает объект, представляюющий область таблицы, занятой указанной Range таблицей запросов. Только для чтения.
Возвращает или задает, как кэш PivotTable подключается к источнику данных. Чтение и написание XlRobustConnect .
True, если строки добавляются в качестве первого столбца указанной таблицы запросов. Для чтения и записи, Boolean.
True, если данные для отчета PivotTable сохраняются в книге. False, если сохранено только определение отчета. Для чтения и записи, Boolean.
True, если сведения о паролях в строке подключения ODBC сохраняются с указанным запросом. False, если пароль удален. Для чтения и записи, Boolean.
Возвращает критерии сортировки для диапазона таблицы запросов. Только для чтения.
Возвращает или задает строку с указанием Microsoft Office или аналогичного файла, который использовался для создания PivotTable. Для чтения и записи.
Возвращает или задает строку, указывающее исходный файл данных для таблицы запросов.
Зарезервировано для внутреннего использования.
Создает таблицу данных на основе значений ввода и формул, которые определяются на таблице.
Возвращает или задает упорядоченный массив констант, которые указывают типы данных, применяемые к соответствующим столбцам в текстовом файле, импортируемом в таблицу запросов. Константа по умолчанию для каждого столбца — xlGeneral. Объект Read/write.
Верно, если запятая является делимитером при импорте текстового файла в таблицу запросов. False, если вы хотите использовать другой символ в качестве делимитера. Значение по умолчанию — False. Для чтения и записи, Boolean.
Верно, если последовательные делимитеры рассматриваются как один делимитер при импорте текстового файла в таблицу запросов. Значение по умолчанию — False. Для чтения и записи, Boolean.
Возвращает или задает символ десятичных сепараторов, который Microsoft Excel при импорте текстового файла в таблицу запросов. По умолчанию — это символ десятичных сепараторов системы. Для чтения и записи, String.
Возвращает или задает массив наборов, соответствующих ширине столбцов (в символах) в текстовом файле, импортируемом в таблицу запросов. Допустимая ширина — от 1 до 32 767 знаков. Объект Read/write.
Возвращает или задает символ, используемый в качестве делимитера при импорте текстового файла в таблицу запросов. По умолчанию значение Null. Для чтения и записи, String.
Возвращает или задает формат столбца для данных в текстовом файле, импортируемом в таблицу запросов. Чтение и написание XlTextParsingType .
Возвращает или задает происхождение текстового файла, импортируемого в таблицу запросов. Это свойство определяет, какая страница кода используется во время импорта данных. Значение по умолчанию — это текущий параметр параметра "Происхождение файлов" в мастере импорта текстовых файлов. Чтение и написание XlPlatform .
True, если необходимо указать имя импортируемого текстового файла при каждом обновлении таблицы запросов. Диалоговое окно Import Text File позволяет указать путь и имя файла. Значение по умолчанию — False. Для чтения и записи, Boolean.
Верно, если заполилон является делимитером при импорте текстового файла в таблицу запросов и если значение свойства TextFileParseType xlDelimited. Значение по умолчанию — False. Для чтения и записи, Boolean.
True, если символ пространства является делимитером при импорте текстового файла в таблицу запросов. Значение по умолчанию — False. Для чтения и записи, Boolean.
Возвращает или задает номер строки, с которого начнется разреза текста при импорте текстового файла в таблицу запросов. Допустимые значения — это значения от 1 до 32 767. Значение по умолчанию равно 1. Для чтения и записи, Integer.
True, если символ вкладки является делимитером при импорте текстового файла в таблицу запросов. Значение по умолчанию — False. Для чтения и записи, Boolean.
Возвращает или задает квалификатор текста при импорте текстового файла в таблицу запросов. В текстовом квалификаторе указывается, что закрытые данные в текстовом формате. Чтение и написание XlTextQualifier .
Возвращает или задает символ сепаратора тысяч, который Microsoft Excel при импорте текстового файла в таблицу запросов. По умолчанию — это символ сепаратора системных тысяч. Для чтения и записи, String.
True для Microsoft Excel числа, импортируемые как текст, которые начинаются с символа "-" как отрицательное число. False для Excel для того, чтобы рассматривать номера, импортируемые в виде текста, которые начинаются с символа "-" в виде текста. Для чтения и записи, Boolean.
Возвращает или задает констант, которая указывает, является ли визуальная схема импортируемого текста слева направо или справа XlTextVisualLayoutType налево.
Верно, если последовательные делимитеры рассматриваются как один делимитер при импорте данных из тегов HTML PRE на веб-странице в таблицу запросов и если данные необходимо разрезать на < > столбцы. False, если вы хотите рассматривать последовательные делимитеры как несколько делимитеров. Значение по умолчанию — True. Для чтения и записи, Boolean.
Верно, если данные, похожие на даты, анализируется как текст при импорте веб-страницы в таблицу запросов. False, если используется распознавание дат. Значение по умолчанию — False. Для чтения и записи, Boolean.
True, если перенаправления веб-запросов отключены для QueryTable объекта. Значение по умолчанию — False. Для чтения и записи, Boolean.
Возвращает или задает значение, определяющий, сколько форматирования с веб-страницы применяется при импорте страницы в таблицу запросов. Чтение и написание XlWebFormatting .
Возвращает или задает, анализируют ли данные, содержащиеся в ТЕГАХ HTML PRE на веб-странице, в столбцы при импорте страницы в < > таблицу запросов. По умолчанию используется значение True. Для чтения и записи, Boolean.
Возвращает или задает значение, определяющий, импортируется ли в таблицу запросов вся веб-страница, все таблицы на веб-странице или только определенные таблицы на веб-странице. Чтение и написание XlWebSelectionType .
Верно, если данные с тегов HTML PRE на указанной веб-странице обрабатываются одновременно при импорте страницы < > в таблицу запросов. False, если данные импортируется в блоки соотестных строк, чтобы строки заглавной строки были признаны как таковой. Значение по умолчанию — False. Для чтения и записи, Boolean.
Возвращает или задает запятую список имен таблиц или номеров индекса таблицы при импорте веб-страницы в таблицу запросов. Для чтения и записи, String.
Возвращает WorkbookConnection объект, который используется в таблице запросов. Только для чтения.
Методы
Отменяет все фоновые запросы для указанной таблицы запросов. Используйте Refreshing свойство, чтобы определить, находится ли фоновый запрос в настоящее время.
Обновляет внешний диапазон данных ( QueryTable ). Логическое значение.
Сбрасывает время обновления для указанной таблицы запросов или отчета PivotTable до последнего интервала, заданного с помощью RefreshPeriod свойства.
Сохраняет источник кэша PivotTable в качестве Microsoft Office подключения к данным.
События
Возникает после завершения или отмены запроса.
Происходит до обновления таблицы запросов. Это включает обновление в результате вызова метода Обновления, действий пользователя в продукте и открытия книги, содержащей таблицу запросов.
В примерах этого раздела используется файл Database.accdb , который должен быть расположен в корневом каталоге диска C.
Для работы с базами данных могут быть использованы различные инструменты. Одним из распространенных инструментов такого взаимодействия являются QueryTable - таблицы, которые отображают информацию, полученную из базы данных .
16.3.1. OpenDatabase и QueryTable
Самый простой и доступный способ импортировать информацию из базы данных в Microsoft Excel, это - воспользоваться специальным методом рабочего листа. Речь идет о методе OpenDatabase . Он предназначен для создания новой книги, которая содержит лист с информацией, полученной из базы данных. Получение информации из базы данных в Excel может быть полезным, например, для анализа этой информации средствами Excel.
Полный вызов метода выглядит так:
Рассмотрим параметры метода.
- Filename - имя и расположение базы данных.
- CommandText - Текст запроса к базе данных. Здесь можно указать имя таблицы базы данных, которая должна быть открыта.
- CommandType - тип запроса - xlCmdCube (куб), xlCmdList (список), xlCmdSql (SQL), xlCmdTable (таблица).
- BackgroundQuery - если установлен в True - обработка данных ведется в фоновом режиме, если в False - в обычном.
- ImportDataAs - способ импорта данных. Может принимать два значения - первое - xlPivotTableReport (данные будут импортированы в виде сводной таблицы - Pivot Table ), второе - xlQueryTable (данные будут импортированы с помощью QueryTable - в виде обычной таблицы).
Чтобы рассмотреть пример использования этой команды, создадим простую базу данных, состоящую из двух таблиц. Первая таблица представляет собой список клиентов, вторая - список их покупок, где учитывается лишь сумма покупки на определенную дату. Таблица клиентов имеет имя Клиенты , таблица покупок - имя Покупки . Импортируем с помощью метода OpenDatabase таблицу Покупки в документ MS Excel . Предположим, что база данных хранится на диске C :, ее имя - Database.accdb . Добавим на лист MS Excel кнопку, содержащую такой код (листинг 16.5.)
Листинг 16.5. Импорт данных из БД с использованием QueryTableПосле нажатия на кнопку будет создана новая книга, лист которой, названный по имени базы данных, будет содержать импортированные данные (рис. 16.1.).
Чтобы импортировать данные как PivotTable , нам понадобится такой код (листинг 16.6.) - его мы добавим в обработчик события Click другой кнопки на рабочем листе книги-примера.
Листинг 16.6. Импорт данных из БД с использованием PivotTableНа рис. 16.2. вы можете видеть результат выполнения команды - сводную таблицу, с которой можно продолжать дальнейшую работу.
Теперь рассмотрим еще один метод получения информации из БД.
16.3.2. ADO
QueryTable можно добавить на рабочий лист, предварительно настроив ее параметры.
Объекты QueryTable объединены в коллекцию QueryTables . Важнейший метод этой коллекции - Add - он добавляет новую таблицу в указанную позицию на листе. Вызов метода Add выглядит так:
WorkBook .QueryTables.Add(Connection, Destination)
В качестве параметра Connection обычно используют объект ADODB.Recordset , о котором ниже, а Destination - это объект Range , который указывает на диапазон (или ячейку), куда будет добавлена QueryTable . Если в Destination задана ячейка, левая верхняя ячейка вставляемой таблицы таблицы совпадет с ячейкой.
Для работы с базами данных используется объектная модель ADO . Чтобы подключить ее к проекту, выберите в окне References пункт Microsoft ActiveX Data Object 2.8 Library - обращаться к ней можно, используя имя объекта ADODB .
ADO - это очень мощный механизм для доступа к источникам данных. Здесь мы рассмотрим методику получения информации из БД с использованием ADO . Нас будут интересовать несколько ключевых объектов ADO .
Во-первых - это объект ADODB.Connection , который позволяет установить соединение с базой данных и работать с ней. У объекта Connection есть свойство ConnectionString - оно представляет собой строку, содержащие параметры подключения к базе данных , в частности - адрес файла базы данных и имя драйвера. Метод Open объекта Connection используется для открытия соединения, заданного свойством ConnectionString .
Во-вторых - объект ADODB.RecordSet - он позволяет получать из открытой базы данных определенные порции информации.
Для получения данных используется метод объекта Open , которому передается запрос на получение данных, а так же - открытое соединение.
Давайте рассмотрим пример. Здесь мы подключаемся к базе данных и создаем Query Table на основе объекта RecordSet , в котором хранится информация, полученная из базы (листинг 16.7.).
Листинг 16.7. Импорт данных из БД с использованием ADOЕсли вы хотите эффективно работать с базами данных - вам придется научиться строить SQL-запросы, изучить особенности взаимодействия с различным видами БД и так далее.
16.4. Работа с диаграммами
Для работы с диаграммами используют объект Chart . Чтобы добавить диаграмму на лист можно применить методом AddChart коллекции Shapes ..
Такой код (листинг 16.8.) добавляет диаграмму на активный лист :
Когда диаграмма добавлена, можно настроить ее свойства, в частности, при помощи метода SetSourceData задать диапазон ( объект типа Range ), содержащий информацию, которая должна быть визуализирована. Этот метод принимает два параметра. Первый - Source - отвечает за источник данных , второй - PlotBy - определяет, как берутся данные для диаграммы - по столбцам ( xlColumns ) или по строкам ( xlRows ).
Так же после добавления диаграммы обычно настраивают ее тип - это делается с помощью свойства CharType . Оно может принимать одно из более чем 70 значений типа xlChartType . Например, xlConeCol - это трехмерная коническая диаграмма , xlPie - круговая диаграмма , xlLineMarkers - график с маркерами.
Рассмотрим пример (листинг 16.9.). Добавим на рабочий лист обычную линейную диаграмму , используя диапазон значений, выделенных пользователем.
16.5. Выводы
В этой лекции мы рассмотрели некоторые дополнительные возможности программирования для MS Excel . Наше следующее занятие посвящено практическим примерам программирования для MS Excel .
Читайте также: