VBA Jak pobierać dane ze stron web do Excel

1. W jaki sposób pobrać dane ze strony internetowej do Excela?

W tym artykule dowiesz się w jaki sposób pobierać dane ze stron www do Excela bez VBA oraz z wykorzystaniem VBA.  Excel bez instalacji dodatków posiada możliwość importu danych ze stron sieci web. By skorzystać z tej funkcjonalności wchodzimy w Excelu w zakładkę dane i w lewym górnym rogu ekranu klikamy Pobieranie danych zewnętrznych, a następnie Z sieci Web.

Po kliknięciu tej opcji powinno wyświetlić nam się okno przeglądarki, w której wpisujemy właściwy adres strony. Nie przejmuj się wyskakującymi błędami – one będą występować w tej przeglądarce. Nie jest to przecież Google Chrome. W momencie, gdy nasza strona jest już wgrana w Excelowej przeglądarce możemy od razu kliknąć przycisk Importuj w prawym dolnym rogu ekranu, co zaimportuje zawartość strony do arkusza Excel. Po kliknięciu przycisku importuj zostaniesz zapytany też, w które miejsce w skoroszycie chcesz pobrać zawartość strony.

Dane zaimportowane w ten sposób możesz już obrabiać w Excel w dowolny sposób. Podczas importu możesz także wybrać, czy zaimportowane dane powinny zawierać formatowanie strony, czy też powinny być zwykłym tekstem. Możesz to ustawić w prawym górnym rogu w opcjach przeglądarki:

2. Importowanie zawartości strony makrem VBA Excel

Jeśli chciałbyś zautomatyzować działanie importowania treści ze stron www do Excela, najlepiej skorzystać jest z VBA w Excelu. Makro oparte na VBA przyda się także, gdy Twoja strona ma wiele podstron, a Ty chcesz zaimportować je wszystkie. By zaimportować stronę web do Excela za pomocą VBA najlepiej, gdy nagrasz jednorazowo makro importujące stronę, a następnie przerobisz kod tego makra. Kod takiego makra dla mojej strony będzie wyglądał następująco:

'tomaszkenig.pl
Sub ImportStronWeb()
'
' ImportStronWeb Makro
'

'
    With ActiveSheet.QueryTables.Add(Connection:="URL;https://tomaszkenig.pl", _
        Destination:=Range("$A$1"))
        '.CommandType = 0 ta linia została wykluczona
        .Name = "tomaszkenig.pl"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Jak widzisz w powyższym kodzie linia .CommandType została zakomentowana. Mogła ona powodować błędy przy imporcie danych.

3. Zapętlenie kodu VBA – wgrywanie wielu podstron

Zapewne zastanawiasz się, w jaki sposób mógłbyś zaimportować dane ze stron www, które są zamieszczone na wielu podstronach, jak np katalog, bądź lista. Jest to oczywiście możliwe i nie wymaga dużego wysiłku. Jak widzisz linki na mojej stronie głównej wyglądają następująco:

• https://tomaszkenig.pl/page/2/
• https://tomaszkenig.pl/page/3/
• https://tomaszkenig.pl/page/4/
• (…)
• https://tomaszkenig.pl/page/n/

Nic prostszego, wystarczy zapętlić makro. Pamiętaj, że wgrywane do arkusza treści stron nie powinny na siebie nachodzić. Powinieneś wgrywać dane jedne pod drugimi. Możesz w tym wypadku zrobić przy pomocy pętli odstęp pomiędzy danymi w postaci liczby wierszy. Możesz też skorzystać z kodu makra wyszukującego ostatni wypełniony wiersz, który publikowałem w tym artykule: ostatni wiersz VBA. Teraz jedyne, co powinieneś zrobić to zapętlić kod i dodać licznik odnoszący się do numeru podstrony. Dla przykładu, gdybym chciał ściągnąć 8 kolejnych stron na moim adresie (tomaszkenig.pl), modyfikuję kod w następujący sposób:

Sub ImportStronWeb()
'
' ImportStronWeb Makro
'

'

Dim intLicznik As Long
intlicznikpodstron = 1 'np 8 podstron
intLicznikWierszy = 1 'np kolejne dane w odstępie 500 wierszy

Do While intlicznikpodstron < 8
    With ActiveSheet.QueryTables.Add(Connection:="URL;https://tomaszkenig.pl/page/" & intlicznikpodstron & "/", _
        Destination:=Range("A" & intLicznikWierszy))
       ' .CommandType = 0
        .Name = "tomaszkenig.pl"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
intlicznikpodstron = intlicznikpodstron + 1
intLicznikWierszy = intLicznikWierszy + 500
Loop
End Sub

Podobne przykłady praktycznego użycia języka VBA w Excelu zamieszczam na tej stronie: VBA Przykłady. Przy okazji zapraszam do przejścia kursu VBA mojego autorstwa, znajdziesz go pod tym linkiem: Kurs VBA. Poniżej plik Excel z omawianym przykładem:

excel_vba_dane_ze_stron_web.xlsm