Funkcja sprawdzająca istnienie arkusza w zewnętrznym pliku Excela? (kontynuacja)

Kontynuując zagadnienie prezentowane przed kilkoma dniami chciałbym zaprezentować podobne rozwiązanie do tam opisanego lecz tym razem oparte o funkcję użytkownika UDF. Cel- funkcja ma zwracać wartości True/False w odpowiedzi na pytanie czy we wskazanym skoroszycie (tu podamy pełną ścieżkę do pliku) istnieje określony arkusz (tu podamy jego nazwę). Poniżej kod funkcji wraz z dodatkowymi komentarzami. Funkcja tej postaci działać będzie zarówno w środowisku VBA jak również w dowolnej komórce Excela.

view sourceprint?

01	Function IsSheetInWorkbook(shName As String, wbFullName As String) As Boolean

02

03	'Obiekty ADODB i ADOX

04	Dim adoConn As ADODB.Connection

05	Dim adoxCat As ADOX.Catalog

06	Dim adoxTbl As ADOX.Table

07

08	Dim strConnString As String

09	Dim strSheet As String

10

11	'Connection String dla pliku excel, sandardowa konfiguracja

12	strConnString = "Provider=Microsoft.ACE.OLEDB.12.0; " & _

13	"Data Source=" & wbFullName & ";" & _

14	"Extended Properties=Excel 12.0 Xml;"

15	'tworzymy połączenie

16	Set adoConn = New ADODB.Connection

17	adoConn.Open strConnString

18

19	'pozostałe powiązania miedzy ADO i ADOX

20	Set adoxCat = New ADOX.Catalog

21	Set adoxCat.ActiveConnection = adoConn

22

23	'w obszarze obsługi błędów próbujemy przypisac tabelę

24	'do obiektu ADOX.Table

25	'Ważne! arkusze excela wymagaja znaku dolara w nazwie na końcu

26	'aby mogły być traktowane jak tabele baz danych

27

28	On Error Resume Next

29	Set adoxTbl = adoxCat.Tables(shName & "$")

30

31	If Err.Number = 0 Then

32	'tabela/arkusz istnieje

33	IsSheetInWorkbook = True

34

Else

35	'tabela/arkusz nie istnieje

36	IsSheetInWorkbook = False

37

End If

38

39	'zamykamy połączenie i likwidujemy obiekty

40	adoConn.Close

41	Set adoxCat = Nothing

42	Set adoConn = Nothing

43	End Function

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.

view sourceprint?

01	Sub 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

35

End Sub