Microsoft Office Access
uređuje Branislav Mihaljev, MVP

Čitanje sadržaja XLS datoteke

Nivo:
NIVO 2 - Među ovim prilozima se nalaze preporuke koje se uglavnom odnose na bolje snalaženje i efikasniji rad: dodatni trikovi koji će biti svakom od pomoći i koji upućuju na prag napredne upotrebe programa.

Pored funkcija za izvoz i uvoz podataka Excel datoteka, Access može direktno da čita, piše, pa čak i formatira sadržaj XLS datoteka. Nažalost, ove tehnike nisu jednostavne i zahtevaju napredno znanje VBA programiranja, korektnu sintaksu i obavezno zatvaranje objekta nakon završetka rada. Ujedno ovo je jedan od veoma čestih problema, jer korisnici koriste uporedo Access za čuvanje veće količine podataka i Excel za analizu podataka i njihov grafički prikaz, a pritom nemaju očigledan i dovoljno fleksibilan način da povežu ova dva programa na nivou razmene pojedinačnog podatka ili određene grupe podataka. U ovom prilogu ćemo se pozabaviti jednim načinom čitanja sadržaja XLS datoteke, dok ćemo u drugom prilikom obraditi izmenu sadržaja, oblikovanje i čuvanje izmenjene XLS datoteke.

Osnovni način povezivanja radnog lista Excela, uz zadržavanje dinamičke veze sa povezanom datotekom, je mogućnost Accessa da poveže radni list sa bazom podataka metodom spoljne veze sa radnim listom: pokrenite komandu File / Get External Data / Link Tables, a zatim u listi tipova datoteka odaberite Microsoft Excel. Nad radnim listom povezanim na ovaj način sada možete primeniti sve operacije koje se odnose i na Access tabele, osim što nikako ne možete menjati njihov sadržaj.

Potpunu kontrolu nad sadržajem XLS datoteke možete ostvariti jedino VBA programiranjem. Pre nego što započnete pisanje programa, morate Accessovoj bazi pridružiti biblioteku Microsoft Excel 11.0 Object Library (zapravo, verzija biblioteke zavisi od verzije Microsoft Officea). Pokrenite Access, kreirajte novu bazu podataka i pređite u prozor za pisanje VBA programa (CTRL+G). Otvorite dijalog komandom menija Tools / References, pronađite u listi navedenu biblioteku i priključite je programu tako što ćete uključiti polje za potvrdu ispred naziva biblioteke. Skup priključenih biblioteka je jedinstven za svaku bazu podataka, kako bi se izbegli konflikti sa drugim bibliotekama upotrebljenim u bazama podataka. Stoga ne zaboravite da priključite ovu biblioteku za svaku bazu podataka u kojoj želite da ostvarite vezu sa XLS radnim listovima na predloženi način.

Postoje dva načina da ostvarite vezu sa XLS datotekom. Prvi se obično koristi u ranoj fazi programiranja, dok drugi zamenjuje prvi kada program proradi onako kako se očekuje. U prvoj fazi možete koristiti sledeći program:

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application

Kasnije, mada se načini ne isključuju, možete koristiti:

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

Kako biste razumeli automatizaciju povezivanja podataka ova dva programa, potrebno je da znate nešto o objektnom modelu programa sa kojim radite. U ovom slučaju radite sa objektnim modelom Excela, pa ćemo se zadržati na njemu. Npr. da biste pročitali sadržaj ćelije (Cell) dovoljno je da znate da se Excel sastoji od kolekcije radnih svezaka (Workbooks) koje se sastoje od jednog ili više objekta tipa Workbook. Svaka zasebna XLS datoteka je jedna radna sveska. Dalje, Workbook je kolekcija koja se sastoji jednog ili više radnih listova (Worksheets). Jedna od osobina objekta Worksheet je osobina Cell, što zapravo predstavlja skup ćelija ili rang ćelija. Ispitivanjem ove osobine možete pročitati sadržaj pojedinačne ćelije, grupe ili svih ćelija jednog radnog lista.

U narednom primeru pretpostavljamo da imate na raspolaganju datoteku Primer.xls koja sadrži radni list pod nazivom Podaci. Na početku definišete promenljive koje ćete koristiti i pronalazite punu putanju do tražene XLS datoteke:

Sub CitanjeXLS()

Dim objWbk As Object
Dim objWksh As Object
Dim objXL As Object
Dim strWbkNaziv As String

strWbkNaziv = CurrentDb().Name
strWbkNaziv = Left$(strWbkNaziv, _
  Len(strWbkNaziv) - Len(Dir$(strWbkNaziv)) & _
  "Primer.xls"

Ako radna sveska pod nazivom Primer.xls ne postoji, Access će na ovom mestu prijaviti grešku. Ne zaboravite da dodate proceduru za obradu greške, ako je to potrebno. Postojanje radne sveske Primer.xls možete proveriti na sledeći način:

If Len(Dir(strWbkNaziv)) = 0 Then
  MsgBox strWbkNaziv & " ne postoji."
Else
  ... 'nastavak programa

Umesto Else možete napisati komandu za napuštanje procedure (Exit Sub) i završetak ispitivanja uslova (End If). Sada kreirate instancu programa Excel, otvarate radnu svesku, a zatim čitate sadržaj ćelije radnog lista:

CreateObject("Excel.Application")
objXL.Application.Workbook.Open strWbkNaziv
Set objWbk = _
  objXL.Application.ActiveWorkbook
Set objWksh =  _
  objWbk.Worksheets("Podaci")

MsgBox objWksh.Cells(1, 1)

U ovom primeru koristimo komandu za izdavanje poruke sadržaja ćelije A1. Prvi broj (cifra u ovom slučaju) označava kolonu, dok drugi broj označava redni broj reda. Npr. oznaka (3, 4) označava ćeliju C4. Excelov objektni model je veoma fleksibilan i postoji nekoliko načina da uradite istu operaciju. Npr. u poslednjoj liniji dosadašnjeg programa možete napisati:

MsgBox objWksh.Cells("A1")

i dobićete sadržaj navedene ćelije.

Ovo što sledi je veoma važno. Za sada ste kreirali instancu Excela i pročitali sadržaj ćelije. Sve se odvija u pozadini i ako pogledate listu aktivnih procesa (otvorite Task Manager prečicom CTRL+ALT+DEL), videćete da je pokrenut Excel. Ako eksplicitno ne zatvorite radnu svesku i instancu Excela, može se desiti curenje memorije - što više puta pokrenete sličan program, toliko će više instanci Excela ostati pokrenuto. Nakon završetka rada morate zatvoriti XLS datoteku i navesti parametar koji kazuje da odustajete od čuvanja sadržaja, kako biste osigurali da će upotrebljena XLS datoteka ostati neizmenjena. Naredne linije programa su obavezne!

objWbk.Close SaveChanges:=False
Set objWbk = Nothing
objXL.Application.Quit
set objXL = Nothing

End Sub