piątek, 17 kwietnia 2015

Jak sprawdzić nazwy arkuszy w zamkniętym pliku Excel?

Szukając odpowiedzi na pytanie z tytułu nasuwa się jedna prosta- otworzyć plik :) ...jednak zdecydowanie nie tego rozwiązania poszukujemy w tym momencie. Chciałbym zaprezentować technikę, która umożliwia odczyt i weryfikację nazw wszystkich arkuszy we wskazanym skoroszycie, ale bez otwierania tego pliku. Aby wykonać powyższe zadanie będziemy wykorzystywać zaawansowaną technikę z obszaru ADODB okreśalną mianem ADOX (Activex Data Objects Extension). Wykorzystanie ADOX umożliwia nam odczyt parametrów bazy danych oraz modyfikację struktury bazy danych przez usuwanie tabel, dodawanie nowych tabel o określonych polach, itp. Szereg informacji na temat ADOX znaleźć można na stronie Microsof MSDN, np. tutaj. W celu stworzenia skutecznego rozwiazania naszego zadania pierwszy krok naszego działania to ustawienie referencji do biblioteki ‘Microsoft ADO Ext. 2.8 for DDL and Security’ (przypomnę, referencję ustawiamy w środowisku VBA >> Menu >> Tools >> References... >> odnajdujemy bibliotekę, zaznaczamy i akceptujemy OK). Potrzebna będzie nam także podstawowa biblioteka ADO w celu ustanowienia połączenia z naszym plikiem Excela, czyli dodatkowo ustawiamy referencję do zasobów ‘Microsoft ActiveX Data Objects 2.8 Library’. Poniżej prezentuję rozwiązanie, które listuje do okna Immediate wszystkie arkusze wskazanego pliku XLSM. Dodatkowe informacje na temat nowych obiektów i ich rola zostały opisane w formie komentarzy w kodzie poniżej.
01Sub ListaWszystkichArkuszy()
02     
03    Dim adoConn As ADODB.Connection
04    'Catalog to nadrzędny obiekt reprezentujący
05    'schemat bazy danych (tu: pliku Excel)
06    Dim adoxCat As ADOX.Catalog
07    'Table to obiekt odpowiadający tabeli w bazie danych
08    'w naszym przykładzie tabela to arkusz
09    Dim adoxTbl As ADOX.Table
10     
11    Dim strConnString As String
12     
13    'Connection String dla pliku Excel, standardowa konfiguracja
14    strConnString = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
15               "Data Source=" & ThisWorkbook.Path & "\test_file.xlsm" & ";" & _
16               "Extended Properties=Excel 12.0 Xml;"
17    'tworzymy połączenie
18    Set adoConn = New ADODB.Connection
19        adoConn.Open strConnString
20         
21    'tworzymy referencję do obiektu Catalog
22    'i tworzymy powiązanie z obiektem connection
23    Set adoxCat = New ADOX.Catalog
24    Set adoxCat.ActiveConnection = adoConn
25     
26    'listujemy wszystkie arkusze do okna Immediate
27    For Each adoxTbl In adoxCat.Tables
28        Debug.Print adoxTbl.Name
29    Next adoxTbl
30     
31    'zamykamy połączenie i likwidujemy obiekty
32    adoConn.Close
33    Set adoxCat = Nothing
34    Set adoConn = Nothing
35End Sub

Brak komentarzy:

Prześlij komentarz