8/3/2019 Integrarea Visual Basic Cu Excel

1/14

Integrarea Visual Basic cu Microsoft Excel

Punctul de pornire n realizarea acestui articol l constituie problemarealizrii de rapoarte profesionale n formate accesibile diverilor utilizatori.De regul, fiecare mediu de programare a aplicaiilor care se respect

pune la dispoziia programatorilor componente specifice pentru realizareade rapoarte. Sunt arhicunoscute generatoarele de rapoarte din VisualFoxPro, Magic sau Visual Basic. Fiecare dintre ele are puncte tari i puncteslabe, ns n practic se simt mai ales punctele slabe, determinate deflexibilitatea destul de redus n abordarea unor anumite tipuri deprobleme.

Visual Basic deine utilitarul "Data Report" pentru realizarea rapoartelor.Utilizarea acestuia este foarte simpl, dar de cele mai multe ori n practic,

facilitile oferite de "Data Report" sunt insuficiente. Soluia pentru acesteinconveniente o poate reprezenta achiziionarea contra cost a unorcomponente specializate, sau utilizarea altor medii, cum este MicrosoftExcel pentru exportarea rapoartelor n formatul dorit. Dei presupune unvolum ceva mai mare de munc pentru realizarea rapoartelor, aceastsoluie ofer n schimb o mare flexibilitate (s ne gndim numai la graficelei multitudinea de funcii de care dispune Excel-ul i care vor putea fifolosite prin intermediul Visual Basic).

Utilizarea n Visual Basic a obiectelor ce aparin altor medii necesitstabilirea unei referine la acestea (meniul Project / References). nfereastra care se deschide se bifeaz bibliotecile de obiecte dorite (pentruExcel se bifeaz Microsoft Excelx.x Object Library unde x.x esteversiunea bibliotecii. Dac aceast bibliotec nu apare n list nseamn cnu este instalat Microsoft Excel pe maina local).

Fig. 1 - Stabilire referin ctre biblioteca de obiecte Microsoft Excel.

Dup stabilirea referinei la o bibliotec de obiecte se poate folosi opiunea

Object Browser (meniul View / Object Browser sau tasta F2) pentru avedea membrii acesteia. S vedem n continuare care sunt cele maiimportante clase, precum i atributele i metodele speifice lor.

1.Clasa Application

Clasa Application ncorporeaz toate funcionalitile aplicaiei MicrosoftExcel. Instanierea fiecrui obiect din aceast clas va lansa n execuie onou instan a aplicaiei Excel, chiar dac aceasta nu va fi vizibil pentruutilizator. Aceste instane vor rula pn la invocarea metodei quit.

Este bine de tiut c n momentul opririi aplicaiei, instanele Excelcreatese vor nchide automat doar dac au fost nchise toate documentele

1

http://www.agora.ro/images/126/atelier2a.gifhttp://www.agora.ro/images/126/atelier2a.gif

8/3/2019 Integrarea Visual Basic Cu Excel

2/14

create sau deschise de acestea. Este recomandat ca la nchidereaaplicaiei s invocm explicit metoda quit pentru toate instanele Excelcreate, altfel riscm s ocupm inutil resursele sistemului cu instane Excelcare nu mai sunt referite de nici o aplicaie.

Principalele proprieti ale clasei APPLICATIONProprietate Descriere

ActiveCellReturneaz un obiect de tip RANGE(grup de celule) ce reprezint celulacurent din fereastra activ.

ActiveSheetReturneaz un obiect de tip SHEET(foaie de lucru) ce reprezint foaiade lucru activ.

ActiveWindowReturneaz un obiect de tipWINDOW (fereastr) ce reprezintfereastra activ.

ActiveWorkbookReturneaz un obiect de tipWORKBOOK (registru de lucru) cereprezint registrul activ.

CellsReturneaz un obiect de tip RANGEce reprezint toate celulele din foaia

de lucru activ.

ColumnsReturneaz un obiect de tip RANGEce reprezint toate coloanele dinfoaia de lucru activ.

Path Returneaz calea spre MicrosoftExcel.

RangeReturneaz un obiect de tip RANGEce reprezint o celul sau un grup decelule.

RowsReturneaz un obiect de tip RANGEce reprezint toate rndurile dinfoaia de lucru activ.

SheetsReturneaz o colecie de tipSHEETS ce reprezint toate foile delucru din registrul activ.

Windows

Returneaz o colecie de tipWINDOWS ce reprezint toate

ferestrele din toate registrele delucru deschise.

2

8/3/2019 Integrarea Visual Basic Cu Excel

3/14

WorkbooksReturneaz o colecie de tipWORKBOOKS ce reprezint toateregistrele de lucru deschise.

WorksheetsReturneaz o colecie de tipSHEETS ce reprezint toate foile delucru din registrul activ.

Principalele metode ale clasei APPLICATION

Metoda Descriere

CalculateEfectueaz calculele conformformulelor din toate registrelede lucru.

ExecuteExcel4Macro Execut o macrocomandMicrosoft Excel 4.0.

Quit nchide aplicaia.

RunExecut o macrocomandscris n Visual Basic sauMicrosoft Excel 4.0.

Undo Anuleaz ultima modificareefectuat.

Exemplu:

-instanierea unei aplicaii Excel:

Dim appExc As NEW Excel.Applicationsau

Dim appExc As NEW Excel.ApplicationSet appExc = NEW Excel.Application

2.Clasa Workbook

Clasa Workbookface parte din colecia Workbooks i reprezint registrulde lucru (documentul) Microsoft Excel.

Principalele metode ale coleciei WORKBOOKS

Metoda Descriere

AddCreeaz un registru de lucru nou. Deasemenea, noul registru de lucru va deveni

registrul activ.Close nchide toate registrele de lucru deschise.

3

8/3/2019 Integrarea Visual Basic Cu Excel

4/14

Open Deschide registrul de lucru specificat.

O colecie Workbooks conine toate registrele de lucru (obiecteWorkbook) deschise cu o aplicaie Excel (un obiectApplication). Implicit,elementele coleciei Workbooks sunt numerotate de la 1. Astfel, primul

registru de lucru deschis cu o aplicaie Excel va fi acesat prinApplication.Workbooks(1).

Urmtorul exemplu va instania o aplicaie Excel i apoi genereaz un nouregistru de lucru prin intermediul acesteia:

Dim appExl As New Excel.Application

AppExl.Workbooks.Add

sauDim appExl As Excel.ApplicationSet appExl = New Excel.ApplicationAppExl.Workbooks.Add

Urmtorul exemplu va instania o aplicaie Excel i apoi va deschideregistrul de lucru "Registru.xls" prin intermediul acesteia:

Dim appExl As New Excel.ApplicationAppExl.Workbooks.Open "C:\Registru.xls"

sau

Dim appExl As Excel.ApplicationSet appExl = New Excel.ApplicationAppExl.Workbooks.Open "C:\Registru.xls"

ATENIE:

Nu trebuie uitat adugarea n rutina de nchidere a aplicaiei iappExl.Quit, altfel la fiecare execuie a aplicaiei va fi deschis o aplicaieExcel ce nu va mai putea fi nchis dect prin intermediul utilitarului TaskManagerdin Windows sau prin repornirea sistemului de operare.

Pentru a nchide toate registrele de lucru deschise de o aplicaie Excel seapeleaz metoda Close a coleciei Workbooks. Pentru exemplele de maisus, pentru a nchide registrele create sau deschise se va adugaappExl.Workbooks.Close.

Principalele proprieti ale clasei WORKBOOK

Proprietate Descriere

ActiveSheet Foaia de lucru activ din registru de lucru

4

8/3/2019 Integrarea Visual Basic Cu Excel

5/14

specificat.

Path Calea ctre registrul de lucru

Saved

Va fi TRUE" dac registrul de lucru a fost salvat. Pentru a marca ca salvat un

registru de lucru fr a-l scrie efectiv pedisc trebuie setat aceast proprietate pe"TRUE".

Worksheets O colecie ce reprezint toate foile delucru din registrul de lucru.

Principalele metode ale clasei WORKBOOK

Metoda Descriere

ActivateActiveaz registrul de lucru. n cazul ncare activ era un alt registru, respectivuldevine automat inactiv.

Close nchide registrul de lucru.

NewWindow Creeaz o nou fereastr pentruregistrul de lucru.

Save

Salveaz pe disc registrul de lucru.

Pentru a marca ca salvat un registru delucru fr a-l scrie pe disc trebuie setatpe valoarea "TRUE" proprietatea Saved.Prima dat cnd se salveaz un registrude lucru se folosete metoda SaveAs.

SaveAs Salveaz pe disc registrul de lucru, nfiierul specificat ca parametru.

n exemplul urmtor, vom instania o aplicaie Excel, vom crea un nou

registru de lucru pe care apoi l vom salva i l vom deschide n aceeaiaplicaie. n final vom nchide registrul de lucru i aplicaia.

Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookSet appExl = New Excel.ApplicationappExl.Workbooks.Add Set wbkBook = appExl.Workbooks(1)wbkBook.SaveAs "C:\Registru.xls"appExl.Workbooks(1).CloseappExl.Workbooks.Open "C:\Registru.xls"Set wbkBook = appExl.Workbooks(1)wbkBook.CloseappExl.Quit

5

8/3/2019 Integrarea Visual Basic Cu Excel

6/14

sau

Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookSet appExl = New Excel.ApplicationSet wbkBook = appExl.Workbooks.AddwbkBook.SaveAs "C:\Registru.xls"wbkBook.CloseSet wbkBook = appExl.Workbooks.Open("C:\Registru.xls")wbkBook.CloseappExl.Quit

3.Clasa WorkSheet

Clasa WorkSheet face parte din colecia WorkSheets i reprezint foaiade lucru. O colecie Worksheets conine toate foile de lucru dintr-unregistru de lucru (workbook).

Principalele metode ale coleciei WORKSHEETS

Metoda Descriere

AddCreeaz o foaie de lucru nou. Aceasta vadeveni foaia de lucru activ. Un registru delucru conine implicit trei foi de lucru.

CopyCopie o foaie de lucru ntr-o alt poziie nregistrul de lucru.

Delete terge o foaie de lucru.Move

Mut o foaie de lucru ntr-o alt poziie nregistrul de lucru.

Principalele proprieti ale clasei WORKSHEET

Proprietate Descriere

Cells

Returneaz un obiect de tip RANGE ce

reprezint toate celulele din foaia de lucru.

ColumnsReturneaz un obiect de tip RANGE cereprezint toate coloanele din foaia delucru.

Index Numrul de ordine al foii de lucru n cadrulregistrului de lucru.

Name Denumirea foii de lucru.

Next

Returneaz un obiect de tip

WORKSHEET ce reprezint foaia de lucruurmtoare.

6

8/3/2019 Integrarea Visual Basic Cu Excel

7/14

PreviousReturneaz un obiect de tipWORKSHEET ce reprezint foaia de lucruanterioar.

RangeReturneaz un obiect de tip RANGE cereprezint o celul sau un grup de celuledin foaia de lucru.

Rows Returneaz un obiect de tip RANGE cereprezint toate liniile din foaia de lucru.

Principalele metode ale clasei WORKSHEET

Metoda Descriere

Activate Foaia de lucru devine foaia activ dinregistrul de lucru. Foaia de lucru activanterior devine automat inactiv.

Calculate Efectueaz calculele conform formulelor dintoate celulele din foaia de lucru.

Copy Copie foaia de lucru ntr-o alt poziie dinregistrul de lucru.

Delete terge foaia de lucru din cadrul registrului.

Move Mut foaia de lucru ntr-o alt poziie nregistrul de lucru.

SaveAs Salveaz modificrile efectuate n foaia delucru.

Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookDim wshSheet As Excel.Worksheet'initializez o aplicatie ExcelSet appExl = New Excel.Application'creez un registru de lucruSet wbkBook = appExl.Workbooks.Add'in registrul de lucru wbkBook'creez o noua foaie de lucruSet wshSheet = wbkBook.Worksheets.Add'Denumesc foaia de lucru'De remarcat c noua foaie este adaugat la "nceputulregistrului"wshSheet.Name = "Foaie de proba"'Introduc un text in casuta A1 din'foaia de lucru "Foaie de proba"wshSheet.Range("A1").Value = "Am reusit!"'Salvez modificarile din registrul de lucru'in fisierul "Registru.xls"wbkBook.SaveAs "C:\Registru.xls"'Inchid registrul de lucruwbkBook.Close'Inchid aplicatia Excel

7

8/3/2019 Integrarea Visual Basic Cu Excel

8/14

appExl.Quit

Pentru a vedea rezultatul exemplului de mai sus se deschide fiierul"C:\Registru.xls" cu aplicaia Microsoft Excel. Dac nu este specificatcalea atunci cnd se salveaz registrul de lucru, fiierul va fi salvat ndirectorul n care se afl aplicaia (proiectul) realizat n Visual Basic. Oalt variant de a vizualiza aplicaia registrul de lucru nou creat o constituieinvocarea comenzii "Shell" din Visual Basic care va primi ca parametricalea ctre executabilul Excel i numele fiierului care se va deschide.

4.Clasa Range

Un obiect de tip Range reprezint o celul, o linie, o coloan sau un grupde celule.

Principalele proprieti ale clasei RANGEProprietate Descriere

AddressAdresa grupului de celule. Deexemplu adresa csuei dinstnga sus va fi "$A$1".

BordersToate cele patru borduri pentruun grup de celule.

Cells

Celulele ce alctuiesc grupul de

celule.

Column

Numrul primei coloane dingrupul de celule. Pentru coloana

A" va fi 1, pentru B" va fi 2 etc.

Columns

Returneaz un obiect de tipRANGE ce reprezint toatecoloanele din grupul de celule

specificat.ColumnWidth

Limea coloanelor din grupul decelule specificat.

Font Font-ul pentru grupul de celule.

FormulaFormula de calcul pentru grupulde celule.

Height nlimea grupului de celule npuncte (1 punct = 1/72 inch).

HorizontalAlignment Alinierea orizontal a textului.Poate lua ca valoare una din

8

8/3/2019 Integrarea Visual Basic Cu Excel

9/14

urmtoarele constante xlHAlign(xlHAlignCenter,xlHAlignDistributed,xlHAlignJustify, xlHAlignLeft,xlHAlignRight ).

MergeArea

Returneaz un obiect de tipRANGE ce va conine ntregulgrup de celule lipite sau grupate(Merged Cells ) din care faceparte celula specificat. Daccelula nu este lipit atunciaceast proprietate va returnadoar celula specificat.

MergeCellsDac regiunea specificatconine celule lipite atunciaceast proprietate returneazTRUE.

Next Celula urmtoare din cadrulgrupului.

NumberFormat Formatul de numr.

Orientation Orientarea textului in celule.Poate lua valori ntre 90 i 90

Previous Celula anterioar din cadrulgrupului.

Row Numrul primei linii din regiuneaspecificat.

RowHeight nlimea liniilor din regiuneaspecificat, n puncte (1 punct =1/72 inch).

Rows Returneaz un obiect de tipRANGE ce reprezint toate liniiledin grupul de celule specificat.

StyleStilul ce se aplic grupului decelule (ex. Normal", "Percent" etc).

Text Returneaz textul aflat n celulaspecificat.

Value Valoarea din celula specificat.Dac obiectul RANGE coninemai multe celule atunci

9

8/3/2019 Integrarea Visual Basic Cu Excel

10/14

proprietatea Value va returna unvector (Array) de valori.

VerticalAlignment

Alinierea pe vertical a textului.Poate lua ca valoare una dinurmtoarele constante xlVAlign(xlVAlignBottom, xlVAlignCenter,xlVAlignDistributed,xlVAlignJustify, xlVAlignTop).

WidthLimea celulei (grupului decelule) n puncte (1 punct = 1/72inch).

Principalele metode ale clasei RANGEMetoda Descriere

ActivateActiveaz o celul. Aceasta trebuie sfac parte din regiunea selectat (a sevedea i metoda Select).

AutoFit

Modific limea coloanelor dinregiunea specificat astfel nct s vievizibil textul din toate celulele. Sintaxa

pentru aceast metod este:Range(grupcelule).Columns.AutoFit

BorderAround

Adaug o bordur grupului de celulespecificat i seteaz forma bordurii("LineStyle"), grosimea ("Width") iculoarea acesteia ("ColorIndex" sau"Color").

Calculate Efectueaz calculele conformformulelor din regiunea specificat.

Clear Anuleaz toate proprietile celulelor.

ClearFormats Anuleaz formatrile pentru celulelespecificate.

Copy Copie un grup de celule n regiuneaspecificat.

Cut Taie un grup de celule i eventual lemut n regiunea specificat.

Delete terge un grup de celule.Insert Adaug noi celule n regiunea

10

8/3/2019 Integrarea Visual Basic Cu Excel

11/14

specificat.

Merge Lipete celulele specificate.

Select Selecteaz regiunea specificat.

Sort

Ordoneaz valorile din grupul de celule

specificat.

UnMerge "Dezlipete" regiunea selectat ncelule individuale.

n exemplul urmtor avem cheltuielile i ncasrile pe trimestre i pe bazaacestora se vor calcula profitul pe trimestre i totalul cheltuielilor,

ncasrilor i profitului la sfritul anului, dup formula Profit=ncasri-Cheltuieli. Rezultatul final va arta astfel:

Situatia financiara pe trimestreTrimestru Cheltuieli Incasari Profit1 190,021,321 LEI 312,321,432 LEI 122,300,111 LEI2 250,121,332 LEI 645,965,342 LEI 395,844,010 LEI3 213,076,089 LEI 823,332,554 LEI 610,256,465 LEI4 345,266,121 LEI 325,266,664 LEI -19,999,457 LEITotal 998,484,863 LEI 2,106,885,992 LEI 1,108,401,129 LEI

Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookDim wshSheet As Excel.Worksheet'initializez o aplicatie ExcelSet appExl = New Excel.Application'creez un registru de lucruSet wbkBook = appExl.Workbooks.AddSet wshSheet = wbkBook.Sheets(1)'Denumesc prima foaie de lucruwshSheet.Name = "Situatia financiara"'Lipesc primele 4 celule de pe prima linie (titlultabelului)wshSheet.Range("A1:D1").MergewshSheet.Range("A1").Value = "Situatia financiara petrimestre"'Titlul va fi scris cu caractere ingrosate'si cu font de 12wshSheet.Range("A1:D1").Font.Bold = TruewshSheet.Range("A1:D1").Font.Size = 12'Aliniez titlul la centruwshSheet.Range("A1:D1").HorizontalAlignment =xlHAlignCenter'Casutele ce contin titlul vor fi'inconjurate cu bordura continua, subtirewshSheet.Range("A1:D1").BorderAround xlContinuous, xlThin'Antetele de coloana vor fi aliniate la centru,'scrise cu caractere ingrosate si inclinate(italice),'fiecare celula cu bordura continua,subtirewshSheet.Range("A2:D2").HorizontalAlignment =

11

8/3/2019 Integrarea Visual Basic Cu Excel

12/14

xlHAlignCenterwshSheet.Range("A2:D2").Font.Bold = TruewshSheet.Range("A2:D2").Font.Italic = TruewshSheet.Range("A2").BorderAround xlContinuous, xlThinwshSheet.Range("B2").BorderAround xlContinuous, xlThinwshSheet.Range("C2").BorderAround xlContinuous, xlThinwshSheet.Range("D2").BorderAround xlContinuous, xlThinwshSheet.Range("A2").Value = "Trimestru"wshSheet.Range("B2").Value = "Cheltuieli"wshSheet.Range("C2").Value = "Incasari"wshSheet.Range("D2").Value = "Profit"wshSheet.Range("A3").Value = 1wshSheet.Range("A4").Value = 2wshSheet.Range("A5").Value = 3wshSheet.Range("A6").Value = 4wshSheet.Range("A3:A6").BorderAround xlContinuous, xlThinwshSheet.Range("A7").Value = "Total"wshSheet.Range("A3:A7").HorizontalAlignment =xlHAlignCenterwshSheet.Range("A7:D7").Font.Bold = TruewshSheet.Range("A7").BorderAround xlContinuous, xlThinwshSheet.Range("B7:D7").BorderAround xlContinuous, xlThin'Formatez celulele ce vor contine valorilewshSheet.Range("B3:D7").NumberFormat = "#,##0 ""LEI"""'Introduc formula pentru calculul profitului pe primaliniewshSheet.Range("D3").Formula = "=C3-B3"'Formula din D3 va fi copiat n toate celulele pn la D6'Excel va actualiza automat formulele n funcie de noilecoordonatewshSheet.Range("D3:D6").FillDown 'Formulele pentru total cheltuieli,incasari i profitwshSheet.Range("B7").Formula = "=SUM(B3:B6)"wshSheet.Range("C7").Formula = "=SUM(C3:C6)"wshSheet.Range("D7").Formula = "=C7-B7"'Introduc datele de calculwshSheet.Range("B3").Value = 190021321wshSheet.Range("C3").Value = 312321432wshSheet.Range("B4").Value = 250121332wshSheet.Range("C4").Value = 645965342wshSheet.Range("B5").Value = 213076089wshSheet.Range("C5").Value = 823332554wshSheet.Range("B6").Value = 345266121wshSheet.Range("C6").Value = 325266664'Se efectueaz toate calculele din foaie conformformulelorwshSheet.Calculate'Potrivire automat a limii coloanelorwshSheet.Range("A1:D7").Columns.AutoFit'Bordur ngroat n jurul tabeluluiwshSheet.Range("A1:D7").BorderAround xlContinuous,xlMedium'Salvez modificrile din registrul de lucru'n fisierul "Registru.xls"wbkBook.SaveAs "C:\Registru.xls"'nchid registrul de lucruwbkBook.Close'nchid aplicatia ExcelappExl.Quit

Pentru a deschide aplicaia Microsoft Excel prin cod, trebuie introdus lafinal comanda:

12

8/3/2019 Integrarea Visual Basic Cu Excel

13/14

Shell "cale_spre_EXCEL nume_fisier_de_deschis"

Pentru exemplul dat, pe calculatorul autorilor, funcia Shellarat astfel:

Shell "C:\Program Files\Microsoft Office\Office\Excel.exe C:\Registru.xls".

Exemplul de mai sus a fost creat doar pentru familiarizarea cu tehnologiaExcel. n practic datele nu vor fi introduse explicit din cod ci, n cele maimulte cazuri vor fi preluate dintr-o baz de date.

5.Excel, o alternativa pentru Data Report

S presupunem c lucrm pe o baz de date n Microsoft Access ceconine tabela clieni, cu urmtoarea structur:

Fig. 2 - Structura tabelei CLIENTI

n exemplul urmtor, exist un control de tip "Data", care este conectat latabela Clieni. Pe baza acestuia se obine un raport n Excel, n manieraprezentat n secvena ce urmeaz.

Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookDim wshSheet As Excel.Worksheet

Dim intRow As Integer

Dim intCol As IntegerDim i As IntegerSet appExl = New Excel.ApplicationSet wbkBook = appExl.Workbooks.AddSet wshSheet = wbkBook.Sheets(1)With wshSheet

.Name = "Clienti"

.Range("A1:F1").Merge

.Range("A1").Value = "Lista clienti"

.Range("A1:F1").Font.Size = 12

.Range("A1:F1").Font.Bold = True

.Range("A1:F1").HorizontalAlignment = xlHAlignCenter

.Range("A2").Value = "Cod".Range("B2").Value = "Denumire"

.Range("C2").Value = "Cod fiscal"

.Range("D2").Value = "Localitate"

13

8/3/2019 Integrarea Visual Basic Cu Excel

14/14