W najbliższych trzech artykułach spróbuję wykorzystać Excela do pracy w sieci. Wcześniej, czy później (zwykle wcześniej) dochodzimy do wniosku, że w sieci znajduje się mnóstwo potrzebnych naszej aplikacji informacji, które co rano przeklejamy lub co gorsza przepisujemy do naszego arkusza. Równie często zdarza się tak, że nasze firmowe serwery udostępniają różne usługi sieciowe, do których nie mamy dostępu bezpośrednio z naszego arkusza. Szczęśliwie Microsoft postawił sobie za cel coraz lepszą integrację z XML, gdzie "coraz lepszą" niestety nie oznacza pełną. Nie oznacza to jednak, że nasz arkusz nie może zostać zaprzężony do pracy z plikami HTML. Ale po kolei.
Prosty problem -- importowanie XML zamieszczonego w sieci do arkusza
Aby problem uczynić czytelniejszym, załóżmy, że chcemy co rano zaimportować do jakiegoś arkusza tabelę walut NBP, która znajduje się pod tym adresem. Poniższa procedura zaimportuje ją dla nas poczynając od komórki A1 w aktywnym skoroszycie.
Sub czystyXML() Application.DisplayAlerts = False ActiveWorkbook.XmlImport url:= _ "http://nbp.pl/Kursy/xml/a117z060619.xml" _ , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1") Application.DisplayAlerts = False End Sub
Kod jest na tyle prosty, że właściwie nie wymaga komentowania. Linijki DisplayAlerts wstawiłem aby uniknąć niepotrzebnych komunikatów. WAŻNE! Zanim uruchomisz kod musisz ustawić odpowiednią referencję Tools->References do najnowszej jaką masz biblioteki Microsoft XML.
Wiecej na temat importu XML przy pomocy czystego Excela znajdziesz tutaj.
Sytuacja komplikuje się nieco, gdy chcemy nasze dane przetworzyć przed zapisem do pliku.
Wykorzystanie ADO do pracy z XML
Oczywiście najprostszym sposobem przetwarzania XML jest skorzystanie z odpowiedniego parsera Microsoftu. W tym celu trzeba jednak znać albo odpowiedni język zapytań, albo właściwe komendy. Po co jednak uczyć się nowych rozwiązań, gdy wciąż mamy stare, dobre i wydajne ADO. Poniższy kod wykorzystuje ADO do wczytania danych z pliku XML do RecordSetu, dzięki czemu ich dalsza obróbka jest bardzo prosta.
Sub XMLToRecordSET() Dim strConnect As String strConnect = "Provider=MSPersist" Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset 'przykladowy plik z danymi (mozna testowac) rs.Open "http://www.topxml.com/vbopensource/ado/testdata.xml", strConnect Dim i As Long, j As Long With ActiveSheet For j = 0 To i - 1 .Cells(1, j + 1).Value = rst.Fields(j).Name Next j .Range("A2").CopyFromRecordset rs End With rs.Close Set rs = Nothing End Sub
Testując kod nie zapomnij dołączyć referencji do najnowszej jaką masz biblioteki ADO (Microsoft AcitveX Data Object).
O wygodach korzystania z ADO pisać chyba nie trzeba, dość wiedzieć, że obiekt RecordSet zachowuje się jak duża tabela, którą łatwo i szybko można, skopiować do tablicy variantów, by następnie usunąć z niej niepotrzebne elementy i wydajnie zapisać wyniki w Excelu. Niestety, nie ma róży bez kolców. Powyższy kod ma jedną DUŻĄ wadę. Mimo wielu prób nie potrafię zaimportować w ten sposób, każdego poprawnie opisanego arkusza XML. Ba, zaryzykuję, nawet tezę że jestem ograniczony tylko do XML wygenerowanych Excelem lub innym programem należącym do pakietu Office. Próba załadowania danych o kursach walut z przykładu pierwszego kończy się fiaskiem.
Czy to znaczy, że jednak musimy nauczyć się parsować pliki XML, jeżeli chcemy mieć dostęp do dobrodziejstw sieci? Niekoniecznie. W następnej części pokażę jak zmusić Excela do pracy w sieci i wyciągania dla nas istotnych informacji tak ze stron WWW jak i usług sieciowych.