Vba excel querytable что это
The class QueryTable represents a worksheet table built from data returned from an external data source, such as a SQL server or a Microsoft Access database.
The classes ListObject and Range. give access to class QueryTable
To use a QueryTable class variable it first needs to be instantiated, for example
The following procedures can be used to set variables of type QueryTable: ListObject.QueryTable, QueryTables.Add, QueryTables.Item and Range.QueryTable.
For Each
Here is an example of processing the QueryTable items in a collection.
Methods
- Cancels all background queries for the specified query table. Use the Refreshing property to determine whether a background query is currently in progress.
- Deletes the object.
- Updates an external data range in a QueryTable object.
- Resets the refresh timer for the specified query table or PivotTable report to the last interval that you set by using the RefreshPeriod property.
- Saves the QueryTable cache source as a Microsoft Office Data Connection file.
Properties
true if the column widths are automatically adjusted for the best fit each time you refresh the specified query table. False if the column widths are not automatically adjusted with each refresh. The default value is True.
true if queries for the query table are performed asynchronously (in the background).
returns or sets the command string for the specified data source. Read/write Variant.
returns or sets one of these XlCmdType constants: xlCmdCube, xlCmdDefault, xlCmdSql, or xlCmdTable. The constant that is returned or set describes the value of the CommandText property. The default value is xlCmdSQL.
returns or sets a string that contains one of the following:.
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.
returns or sets the webpage Uniform Resource Locator (URL) for a web query.
true if the user can edit the specified query table. False if the user can only refresh the query table.
true if the PivotTable cache or query table can be refreshed by the user. The default value is True.
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.
true if field names from the data source appear as column headings for the returned data. The default value is True.
true if formulas to the right of the specified query table are automatically updated whenever the query table is refreshed.
returns a ListObject object for the QueryTable object.
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.
returns or sets a String value representing the name of the object.
returns a Parameters collection that represents the query table parameters.
returns or sets the string used with the post method of inputting data into a web server to return data from a web query.
true if column sorting, filtering, and layout information is preserved whenever a query table is refreshed. The default value is True.
true if any formatting common to the first five rows of data are applied to new rows of data in the query table. Unused cells aren't formatted. The property is False if the last AutoFormat applied to the query table is applied to new rows of data. The default value is True.
indicates the type of query used by Microsoft Excel to populate the query table.
true if there is a background query in progress for the specified query table.
true if the PivotTable cache or query table is automatically updated each time the workbook is opened. The default value is False.
returns or sets the number of minutes between refreshes.
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.
returns a Range object that represents the area of the worksheet occupied by the specified query table.
returns or sets how the query table connects to its data source.
true if row numbers are added as the first column of the specified query table.
true if data for the QueryTable report is saved with the workbook. False if only the report definition is saved.
true if password information in an ODBC connection string is saved with the specified query. False if the password is removed.
returns the sort criteria for the query table range.
returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the QueryTable.
returns or sets a String value that indicates the source data file for a query table.
returns or sets an ordered array of constants that specify the data types applied to the corresponding columns in the text file that you are importing into a query table. The default constant for each column is xlGeneral. Read/write Variant.
true if the comma is the delimiter when you import a text file into a query table. False if you want to use some other character as the delimiter. The default value is False.
true if consecutive delimiters are treated as a single delimiter when you import a text file into a query table. The default value is False.
returns or sets the decimal separator character that Microsoft Excel uses when you import a text file into a query table. The default is the system decimal separator character.
returns or sets an array of integers that correspond to the widths of the columns (in characters) in the text file that you are importing into a query table. Valid widths are from 1 through 32767 characters.
returns or sets the character used as the delimiter when you import a text file into a query table. The default value is null.
returns or sets the column format for the data in the text file that you are importing into a query table.
returns or sets the origin of the text file that you are importing into the query table. This property determines which code page is used during the data import.
true if you want to specify the name of the imported text file each time the query table is refreshed. The Import Text File dialog box allows you to specify the path and file name. The default value is False.
true if the semicolon is the delimiter when you import a text file into a query table, and if the value of the TextFileParseType property is xlDelimited. The default value is False.
true if the space character is the delimiter when you import a text file into a query table. The default value is False.
returns or sets the row number at which text parsing will begin when you import a text file into a query table. Valid values are integers from 1 through 32767. The default value is 1.
true if the tab character is the delimiter when you import a text file into a query table. The default value is False.
returns or sets the text qualifier when you import a text file into a query table. The text qualifier specifies that the enclosed data is in text format.
returns or sets the thousands separator character that Microsoft Excel uses when you import a text file into a query table. The default is the system thousands separator character.
true for Microsoft Excel to treat numbers imported as text that begin with a - (minus) symbol as a negative symbol. False for Excel to treat numbers imported as text that begin with a - symbol as text.
returns or sets an XlTextVisualLayoutType enumeration that indicates whether the visual layout of the text being imported is left-to-right or right-to-left.
true if consecutive delimiters are treated as a single delimiter when you import data from HTML tags on a webpage into a query table, and if the data is to be parsed into columns. False if you want to treat consecutive delimiters as multiple delimiters. The default value is True.
true if data that resembles dates is parsed as text when you import a webpage into a query table. False if date recognition is used. The default value is False.
true if web query redirections are disabled for a QueryTable object. The default value is False.
returns or sets a value that determines how much formatting from a webpage, if any, is applied when you import the page into a query table.
returns or sets whether data contained within HTML tags on the webpage is parsed into columns when you import the page into a query table. The default is True.
returns or sets a value that determines whether an entire webpage, all tables on the webpage, or only specific tables on the webpage are imported into a query table.
true if data from the HTML tags on the specified webpage is processed all at once when you import the page into a query table. False if the data is imported in blocks of contiguous rows so that header rows will be recognized as such. The default value is False.
returns or sets a comma-delimited list of table names or table index numbers when you import a webpage into a query table.
returns the WorkbookConnection object that the query table uses.
Related Classes
- Represents a list object in the ListObjects collection.
- A collection of Parameter objects for the specified query table.
- Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.
- Represents a sort of a range of data.
- A connection is a set of information needed to obtain data from an external data source other than a Microsoft Excel workbook.
Некоторые сведения относятся к предварительной версии продукта, в которую до выпуска могут быть внесены существенные изменения. Майкрософт не предоставляет никаких гарантий, явных или подразумеваемых, относительно приведенных здесь сведений.
Свойства
Верно, если ширина столбца автоматически корректируется в лучшую форму при каждом обновлении указанной таблицы запросов или 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 подключения к данным.
Для большинства практических задач вполне хватает возможностей объектов Application, Workbook, Worksheet и Range. Например, для вставки информации из базы данных вы можете пройти циклом по объекту ADO.Recordset и вставить все нужные записи в лист Excel, а затем средствами VBA прописать в строки внизу итоги по вставленным данным. Однако в Excel встроено еще несколько важных специальных объектов, которые могут сильно упростить работу в различных ситуациях. Например, ту же операцию с по вставке информации из базы данных удобнее будет провести при помощи специального объекта QueryTable, который рассматривается в этом разделе. Еще два таких специальных объекта — объекты PivotTable и Chart рассматриваются в следующих разделах.
Основное назначение объекта QueryTable — работа с набором значений, возвращаемых из базы данных. Этот объект доступен в Excel и при помощи графического интерфейса через меню Данные -> Импорт внешних данных -> Импортировать данные. При помощи объектов QueryTable вы можете разместить набор записей, полученных с источника данных, на листе Excel для выполнения с ним различных операций (например, анализа). QueryTable удобно использовать для "односторонней" работы с источником данных, когда данные только скачиваются с источника в Excel, но изменять их с сохранением изменений на источнике не нужно. В Excel такую возможность синхронизации изменений реализовать можно (например, при помощи перехвата события Change объекта Worksheet), но намного проще (и правильнее) использовать для этой цели возможности Access. Обычно данные помещаются в Excel для проведения анализа (при помощи богатой библиотеки функций), для построения диаграмм, иногда — отчетов и т.п. В этом разделе мы будем рассматривать только такую "однонаправленную" передачу данных из базы данных в Excel.
Как обычно, для того, чтобы создать объект QueryTable и разместить его на листе, нужно использовать специальную коллекцию QueryTables, которая принадлежит рабочему листу (объекту Worksheet) и доступна через его одноименное свойство. Свойства и методы объекта QueryTables — стандартные, как у большинства рассмотренных нами коллекций. Подробного рассмотрения заслуживает только метод Add(), при помощи которого и создается объект QueryTable (с одновременным добавлением в коллекцию). Этот метод принимает три параметра:
- Connection — источник данных для QueryTable (в виде объекта типа Variant). В качестве источника данных можно использовать:
- строку подключения OLE DB или ODBC (строка подключения ODBC должна начинаться с " ODBC;", а в остальном — все точно так же, как в главе про ADO);
- готовый объект Recordset, созданный стандартными средствами ADO или DAO. При этом можно изменять Recordset, на который ссылается QueryTable и обновлять QueryTable. По многим причинам это — самый удобный вариант при работе с QueryTable;
- другой объект QueryTable (вместе со строкой подключения и текстом запроса);
- текстовый файл;
- результаты Web-запроса или запроса Microsoft Query (в виде файла *. dqy или *. iqy). Создать такой файл запроса можно при помощи графических средств Excel: меню Данные ->Импорт внешних данных ->Создать запрос.
Конечно, правильнее всего при создании QueryTable использовать готовый объект Recordset. В этом случае у нас — и самые полные возможности настройки подключения и курсора, и возможность очень эффективного промежуточного хранения данных в оперативной памяти (в объекте Recordset), куда можно вносить изменения, и все очень удобные свойства и методы объекта Recordset. Код на создание объекта QueryTable на листе Excel может выглядеть так (мы используем тот же Recordset на основе таблицы Northwind.Customers, что и в модуле про ADO):
Dim cn As ADODB.Connection
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "User Source = LONDON1;" _
& "Initial Catalog = Northwind"
Dim rs As ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.Open "select * from dbo.customers", cn
Dim QT1 As QueryTable
Set QT1 = QueryTables.Add(rs, Range("A1"))
Непосредственно помещение объекта QueryTable на лист производится при помощи метода QueryTable.Refresh(). Без него объект QueryTable будет создан только в оперативной памяти.
Теперь — о самых важных свойствах и методах объекта QueryTable:
- BackgroundQuery — может ли выполнение запроса производится в фоновом режиме, пока пользователь выполняет в Excel другие действия. По умолчанию true, в false следует переводить только тогда, когда пользователь действиями в Excel может как-то помешать нормальной работе приложения.
- CommandText — текст команды SQL, то есть текст запроса, который передается на источник. Сосуществует совместно с аналогичным свойством SQL (которое оставлено для обратной совместимости) и имеет перед ним приоритет. При передаче QueryTable готового Recordset недоступно.
- CommandType — тип передаваемой в CommandText команды (вся таблица, SQL-запрос, имя куба и т.п.). При работе с готовым Recordset также недоступно.
- Connection — строка подключения, та самая, которую можно передать при вызове метода Add() коллекции QueryTables. Опять-таки при работе с готовым Recordset недоступно.
- Destination — второй параметр, который передавался методу Add(). Возвращает объект Range, представляющий первую (верхнюю левую ячейку) диапазона, занимаемого на листе объектом QueryTable. После создания QueryTable доступен только на чтение.
- EnableEditing — может ли пользователь изменять на графическом экране свойства объекта QueryTable. Если перевести в false (по умолчанию true), то пользователь сможет только обновлять QueryTable.
- EnableRefresh — может ли пользователь обновлять QueryTable, получая заново данные (с источника или Recordset);
- FetchedRowOverflow — это свойство принимает значение true, если записи, полученные с источника, не уместились на листе Excel (было скачано больше, чем 65536 записей). Ошибки в такой ситуации не возникает, поэтому если вы работаете с большими наборами записей, то есть смысл реализовать соответствующие проверки.
- FieldNames — очень полезное свойство. Позволяет отключить вставку полученных с источника названий столбцов в первую строку QueryTable. По умолчанию true (вставлять названия столбцов).
- MaintainConnection — это свойство определяет, будет ли соединение с источником открыто все время до закрытия листа. По умолчанию true — оптимизировано под выполнение частых обновлений. Если переставить в false, можно сэкономить оперативную память на клиенте за счет скорость обновления данных.
- Name — имя объекта QueryTable (на графическом экране его можно просмотреть, если в панели управления Внешние данные нажать на кнопку Свойства диапазона данных). По умолчанию — ExternalData_номер.
- Parameters — возможность получить доступ к коллекции Parameters, набору параметров запроса. Возможности практически такие же, как для работы с параметрами объекта Recordset.
- PreserveColumnInfo и PreserveFormatting — сохранять ли информацию о столбцах (сортировке, фильтрации и т.п.) и форматировании после обновления QueryTable. По умолчанию — все сохранять.
- QueryType — возможность выяснить (свойство доступно только на чтение), что использовалось при создании QueryTable — Recordset, прямой доступ к таблице, SQL-запрос и т.п.
- Recordset — возможность получить ссылку на объект Recordset, который использовался для создания QueryTable или сменить его для объекта QueryTable (изменения вступят в силу только после вызова метода Refresh()).
- Refreshing — это свойство принимает значение true на момент выполнения фонового запроса к источнику. Если выполнение запроса слишком затянулось, его можно прервать при помощи метода CancelRefresh().
- RefreshOnFileOpen — обновлять ли данные автоматически при открытии листа или можно обойтись уже скачанными значениями (по умолчанию).
- RefreshPeriod — через какие интервалы времени автоматически обновлять информацию в QueryTable данными с источника. По умолчанию 0 — то есть автоматическое обновление отключено.
- RefreshStyle — определить, что делать с существующими ячейками, на место которых вставляются ячейки QueryTable при обновлении.
- ResultRange — пожалуй, самое важное свойство объекта QueryTable. Как правило, данные из базы данных перекачиваются в Excel для дальнейшей обработки. Это свойство позволяет получить диапазон, который включает в себя все ячейки, вставленные на лист из объекта QueryTable, чтобы потом применить к ним различные функции (обычно по столбцам или по строкам). Чтобы этот метод сработал, обязательно нужно провести вставку данных QueryTable на лист при помощи метода Refresh. После этого можно использовать то, что возвращает это свойство, как обычный диапазон. Самый простой способ продемонстрировать работу эту метода — воспользоваться кодом
А такой пример генерирует под первым столбцом QueryTable формулу с суммированием значений этого первого столбца:
Set c1 = Sheets("Лист1").QueryTables(1).ResultRange.Columns(1)
c1.End(xlDown).Offset(1, 0).Formula = "=SUM(Column1)"
- RowNumbers — свойство, которое может сильно упростить работу с данными, полученными при помощи QueryTable. Позволяет сгенерировать еще один столбец в QueryTable (слева), который будет состоять из номеров записей, полученных через QueryTable.
- SaveData — сохранять ли данные, полученные через QueryTable, вместе с книгой Excel. По умолчанию True. В False есть смысл переводить для того, чтобы изначально гарантировать работу пользователя только с самыми последними данными, полученными из источника.
- SavePassword — сохранять ли пароль вместе со строкой подключения (это свойство можно использовать только для источников ODBC). Если переставить его в False, можно повысить уровень безопасности вашего приложения.
- SourceDataFile — полный путь и имя файла источника (для Access, DBF и прочих настольных СУБД). Для клиент-серверных систем (таких, как SQL Server), возвращает Null.
- многочисленные свойства, которые начинаются на Text…, определяют параметры текстового файла, если этот файл выбран в качестве источника для QueryTable.
- свойства Web… определяют параметры данных, получаемых от запроса к Web-источнику.
Методы объекта QueryTable (Refresh(), CancelRefresh(), Delete()) очевидны и каких-либо комментариев не требуют. Метод ResetTimer() позволяет обнулить таймер автоматического обновления, а метод SaveAsODC() позволяет сохранить определение источника данных в виде файла Microsoft Query (если источником был объект Recordset, то этот метод вернет ошибку).
У объекта QueryTable есть также два события: BeforeRefresh и AfterRefresh. Они срабатывают соответственно перед началом загрузки данных с источника и после окончания загрузки.
Некоторые сведения относятся к предварительной версии продукта, в которую до выпуска могут быть внесены существенные изменения. Майкрософт не предоставляет никаких гарантий, явных или подразумеваемых, относительно приведенных здесь сведений.
Представляет таблицу таблицы, построенную из данных, возвращаемой из внешнего источника данных, например 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.1.).
Чтобы импортировать данные как PivotTable , нам понадобится такой код (листинг 16.6.) - его мы добавим в обработчик события Click другой кнопки на рабочем листе книги-примера.
На рис. 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.).
Если вы хотите эффективно работать с базами данных - вам придется научиться строить 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 .
Читайте также: