Обект на таблица, импортиращ стойности в Excel от база данни с помощта на VBA
Обект Excel.QueryTable, програмно импортиращ стойности в Excel от база данни с помощта на VBA, свойства, методи и събития на обекта Excel.QueryTable
За повечето практически задачи възможностите на обектите Application, Workbook, Worksheet и Range са достатъчни. Например, за да вмъкнете информация от база данни, можете да преминете през обекта ADO.Recordset и да вмъкнете всички необходими записи в лист на Excel и след това да използвате VBA, за да напишете суми за вмъкнатите данни в редовете по-долу. Има обаче няколко други важни специални обекта, вградени в Excel, които могат значително да опростят работата ви в различни ситуации. Например, би било по-удобно да извършите същата операция с вмъкване на информация от база данни, като използвате специален обект QueryTable, който се обсъжда в този раздел. Още два такива специални обекта, обектите PivotTable и Chart, се обсъждат в следващите раздели.
Основната цел на обекта QueryTable е да работи с набор от стойности, върнати от база данни. Този обект е достъпен в Excel и чрез GUI чрезData ->Импортиране на външни данни ->Импортиране на данни. С обектите на 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 (заедно с низа за връзка и текста на заявката);
- текстов файл;
- резултати от уеб заявка или Microsoft Query (като *.dqy или *.iqy файл). Можете да създадете такъв файл със заявка с помощта на графичните инструменти на Excel: менюДанни ->Импортиране на външни данни ->Създаване на заявка.
Разбира се, най-добрият начин да създадете QueryTable е да използвате готов обект Recordset. В този случай имаме най-пълните настройки на връзката и курсора, както и възможността за много ефективно междинно съхранение на данни в RAM (в обекта Recordset), където могат да се правят промени, както и всички много удобни свойства и методи на обекта Recordset. Кодът за създаване на обект QueryTable в лист на Excel може да изглежда така (използваме същия набор от записи, базиран на таблицата Northwind.Customers, както в модула ADO):
Dim cn Като ADODB.Connection
Задайте cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "Потребителски ID=SA;Парола=парола;Източник на данни = LONDON1;" _
& „Първоначален каталог = Northwind“
Dim rs като ADODB.Recordset
Задайте rs = CreateObject("ADODB.Recordset")
rs.Отворете "изберете * от dbo.customers", cn
Затъмняване на QT1 като QueryTable
Задайте QT1 = QueryTables.Add(rs, Range("A1"))
Директното поставяне на обект QueryTable върху лист се извършва с помощта на метода QueryTable.Refresh(). Без него обектът QueryTable ще бъде създаден само в RAM.
Сега - за най-важните свойства и методи на обекта QueryTable:
И този пример генерира формула под първата колона на QueryTable със сумирането на стойностите на тази първа колона:
Задайте c1 = Sheets("Sheet1").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… определят параметрите на данните, получени от заявката към уеб източника.
Обектът QueryTable също има две събития: BeforeRefresh и AfterRefresh. Те се задействат съответно преди началото на зареждането на данни от източника и след края на изтеглянето.