Операции с Microsoft Excel обекти

Създаване на работен лист "Products.xls"

Първо, нека да разгледаме как можете да извършвате операции върху работен лист на Microsoft Excel от Access. За да направим това, имаме нужда от файл Products.xls, съдържащ работна книга на Excel с един работен лист „Продукти“, който е списък с продукти от демонстрационната база данни на Northwind. Можете да използвате готовия файл на придружаващия компактдиск или да го създадете сами, като експортирате таблицата „Продукти“ в Excel с помощта на командата от менютоИнструменти, връзки към Office, анализирайте го с Microsoft Excel(вижте раздела „Бързо експортиране на данни към други приложения на Microsoft Office“ в Глава 3).

За да подготвите файла Products.xls за по-нататъшни експерименти, изпълнете следните стъпки:

  1. Отворете файла Products.xls, като стартирате Microsoft Excel.
  2. Изберете клетки A4 до D12 в работния лист. Изберете командата от менютоВмъкване, Име, Присвояване(Вмъкване, Име, Дефиниране). В диалоговия прозорецОпределяне на име> въведете името на диапазона: workRange (фиг. 15.25) в текстовото полеИме(Имена в работната книга) и щракнете върху бутонаOK.Това създава именуван диапазон, който ще се използва в следващите примери.

обекти

Фиг. 15.25.Създаване на именуван диапазон в таблицата "Products.xls"

  1. ИзберетеДобавки(Добавки) от менютоУслуги(Инструменти) и премахнете отметките от всички квадратчета вСписък с добавки(Налични добавки) в диалоговия прозорецДобавки(Добавки). Премахването на добавките намалява времето, необходимо за стартиране на Excel. За да затворите диалоговия прозорец, щракнете върху бутонаOK.
  2. Запазете промените, като стартиратеФайл,Запиши(Файл, Запиши) илиФайл, Запиши като(Файл, Запиши като).
  3. Затворете Microsoft Excel.

Йерархия на обекти Microsoft Excel VBA

За да работите програмно с обекти на Excel, трябва да имате разбиране за обектния модел на Microsoft Excel. Тук няма да описваме подробно този модел, тъй като е доста сложен, ще представим само основните му обекти.

    Обектът Application е екземпляр на самия Microsoft Excel. Прилагайки различни методи към този обект, можете да използвате почти всяка команда от менюто на Excel. Обектът Application има свойствата на ActiveWorkbook и

ActiveSheet, които сочат към текущите обекти Workbook (работна книга) и Worksheet (работен лист). Можете да посочите Excel.Application като стойност на аргумента на функциите CreateObject() и GetObjectO, както и в командата Dim objPlMH As New.

  • Основният обект на Excel е Workbook (работна книга). Този обект предоставя достъп до свойствата и структурата на работна книга на Excel. Обектите на работната книга са файлове, които се състоят от няколко обекта, създадени от Microsoft Excel: Worksheet (работен лист) и Chart (диаграма). Обектите Worksheet и Chart се съдържат в семействата Worksheets и Charts.
  • Обектите на работния лист са подчинени на обекта на работната книга и осигуряват достъп до свойствата и структурата на работните листове в работна книга на Excel. Основният обект на взаимодействие между Microsoft Access 2002 и Microsoft Excel 2002 е обектът Worksheet. Възможно е да се прехвърля информация от редовете и колоните на обекта Microsoft Access Recordset към клетките на обекта Microsoft Excel Worksheet и обратно. If като стойност на аргумента на функцията GetObject().или в оператора Dim obj Name As New задайте Excel.Sheet, тогава по подразбиране ще се отвори първият обект от фамилията Worksheets - първият работен лист (ActiveSheet) от обекта Workbook.
  • Обектите Sheet са подобни на обектите Worksheet, но са част от семейството Sheets, което включва както обекти Worksheet, така и обекти Chart, които представляват листове с диаграми в работна книга.
  • Обектът Range е диапазон от клетки в работен лист на Excel. Това може да бъде една клетка, ред, колона, правоъгълна област на работен лист или несвързан (произволен набор от клетки) или 3D диапазон от клетки, който включва клетки в множество работни листове. Можете да получите или зададете стойностите на диапазон от клетки, като дефинирате обект Range. Такива обекти като клетка, ред, колона просто не съществуват. Обектът Range с неговите свойства и методи осигурява работа както с една клетка, така и с техния диапазон. Можете да използвате името на диапазона, който съдържа тези клетки, за да посочите желаната група клетки. Ако в работния лист няма наименувани диапазони, можете да използвате метода Cells на обекта на работния лист, за да зададете координатите на клетка или група от клетки. Следват два начина за промяна на стойността на клетка A1:

    • Microsoft Excel предоставя и много други обекти за използване като обекти на сървърно приложение, но типовете, описани по-горе, са най-често използваните в технологията за автоматизация на Access VBA.

    Отваряне и работа със съществуващ работен лист в Excel

    Преди да работим с автоматизирани обекти на Microsoft Excel, нека създадем препратка към библиотеката с обекти на Microsoft Excel. За това:

    обекти

    Удобно е да изучавате команди за автоматизация, като използвате прозореца за отстраняване на грешкиНезабавно.Следователно изходтози прозорец към екрана, ако не се показва. За да направите това, просто щракнете върху съответния бутон в лентата с инструменти или клавишната комбинация + .

    За да отворите програмно работния лист на работния лист „Продукти“:

    1. Затворете Excel, ако работи.
    2. Създайте нов модул, като изпълните командата от менютоВмъкване, Модул(Вмъкване, Модул).
    3. Добавете следните декларации на променливи към раздела за декларации:
    1. Въведете следния оператор в прозореца за отстраняване на грешки (Фигура 15.27):

    Забележка

    В това твърдение аргументът на Excel. Листът не е задължителен. Ако не е посочено, тогава типът на създадения обект ще се определя автоматично от разширението на файла, посочен в първия аргумент.

    microsoft

    Фиг. 15.27.Команди за автоматично стартиране на Microsoft Excel

    1. За да се уверите, че работната книга е отворена и имаме достъп до нея, въведете следната команда в прозореца за отстраняване на грешки ?xlwProd.Name

    Свойството Name на създадения обект на работна книга съдържа името на файла на Excel: Tova-pbi.xls (Фигура 15.28).

    операции

    1. Файлът Products.xls съдържа само един обект Worksheet, така че работният лист Products е активният работен лист, обект ActiveSheet. За да проверите това, въведете командата в прозореца за отстраняване на грешки:

    Свойството Име на този обект съдържа името на работния лист: Продукти.

    1. Сега опитайте да се обърнете към първата клетка на работния лист. Въведете ?xlwProd.ActiveSheet.Celled, 1). След кратка пауза ще се появи очакваният резултат - редът "Код на продукта". Това е заглавието на първата колона на таблицата.
    2. Методът Cells ви позволява достъп до всяка клетка в работния лист. Въведете ?xlwProd. ActiveSheet. Клетки (R, C), където R е номерът на реда, а C е номерът на колоната от даденотоклетка, т.е. нейните координати (фиг. 15.28).
    3. Можете да промените съдържанието на клетка, като въведете израз като: xlwProd.ActiveSheet.Cells(2,2).Value = "кафяв ориз". Точно както много обекти на Access връщат стойността на обекта, когато въведете името на обекта, методът Cells не изисква изрично да посочите свойството Стойност по подразбиране. За да проверите дали съдържанието на клетката се е променило, въведете ?xlwProd.ActiveSheet.Cells(3,2), без да добавяте израза .Value.

    Можете също да използвате свойството Formula, за да зададете стойността на клетка. Предимството на използването на свойството Формула е, че може да се използва за въвеждане на формули, като се използва естественият синтаксис на Microsoft Excel, т.е. като препратки към клетки, като например "=A2+C6".

    Използване на именувани диапазони от клетки

    Ако сте създали именуван диапазон от клетки в работен лист на Excel, можете да получите стойностите на клетките, съдържащи се в този диапазон, като се обърнете към свойството Range на обекта Worksheet. Първо, нека видим какви именувани диапазони присъстват в обекта, който отворихме. Въведете командата в прозореца за отстраняване на грешки (фиг. 15.29)

    Семейството имена представлява всички имена, дефинирани в работната книга. В този случай първият елемент от това семейство съдържа името на диапазона: WorkRange.

    Можете да видите не само името, но и какво представлява този диапазон. Въведете команда

    правоъгълна област A4-D12 на работния лист Продукти.

    На фиг. 15.29 показва изрази за управление на обекта Range.

    операции

    Фиг. 15.29.Примери за именуван диапазон

    Нека променливата xlsProd препраща към работния лист „Продукти“. За да направите това, въведете командата:

    За да посочите конкретна клетка в рамките на именуван обект Rangeможете да използвате следния оператор:

    Това използва свойството Range на обекта Worksheet за достъп до наименования диапазон и след това свойството Cells на обекта Range, за да посочи конкретна клетка в диапазона. Първото число обозначава реда, а второто обозначава колоната.

    За да се обърнете към обект, който е едно ниво по-високо в йерархията на моделния обект, можете да използвате свойството Parent. На фиг. Фигура 15.29 показва как да осъществите достъп до работната книга на Excel, съдържаща текущия работен лист, и как да настроите променливата на обекта xlaProd да препраща към обекта Microsoft Excel Application:

    Затваряне на обектиWorkbookиApplication

    Обектът на Microsoft Worksheet не може да бъде затворен. Методът Close може да се използва за затваряне на обект на Excel Workbook, а методът Quit може да се използва за излизане от приложението. Следните инструкции затварят обекта Workbook и след това излизат от приложението Automation Server, освобождавайки системни ресурси:

    Ако програмно сте направили промени в работния лист, тогава, когато затворите обекта Workbook, ще бъдете попитани дали искате да запазите промените. Ако не искате този въпрос да бъде задаван на потребителя, въведете аргумента False за метода Close. За да се гарантира, че всички ресурси са освободени, всички използвани обектни променливи трябва да бъдат освободени.

    Забележка

    След задаване на променлива, сочеща към обект Application, на Nothing, съответното приложение не се затваря, въпреки че паметта, заета от променливата, се освобождава. Следователно е необходимо да затворите приложението с метода Quit, преди да освободите съответната обектна променлива.

    Създаване на работен лист в Excel с код за автоматизация

    Същите действия катовъзникне, когато щракнете върхуАнализиране в MS Excel,може да се направи с помощта на код за автоматизация на VBA. Предимството на този метод е възможността за форматиране на създадения обект специално за нуждите на конкретно приложение. Помислете за функцията CreateCustomSheet(), която създава нов обект Worksheet и го попълва с данни от таблицата Products в база данни на Microsoft Access:

    Типът данни, върнат от израза rstProd(intCol-l), трябва специално да бъде променен от вариант на низ с помощта на функцията cstr(), в противен случай Microsoft Excel ще покаже #N/A# в съответната колона вместо желаната стойност. Ако обектът Recordset съдържа полета, чиито типове не са Text, тогава използвайте подходящата функция CType(), за да определите типа данни в колоната.

    microsoft

    Фиг. 15.30.Стойности на вградени константи xlConst в прозорецДостъп до браузъра на обекти

    Въвеждане от оператор? CreateCustomSheet() в прозореца за отстраняване на грешки изпълнява функцията, която обсъдихме по-горе. На фиг. 15.31 показва работната книга "ToBapy_2.xls" с работен лист, създаден с помощта на функцията CreateCustomSheet () и отворен в Microsoft Excel.

    операции

    Фиг. 15.31.Част от работен лист на Excel, създаден от таблицата "Продукти".