wimgielis.com

Webquery

• 03/08/2007

	Voorbeeldbestanden bij dit artikel:
	Webquery

Inleiding

Ik ga hier letterlijk de code zetten die ik gebruikte om de kalender van Lucyvo op te halen via een webquery. Geen haar op mijn hoofd dat er aan denkt dat ik dat allemaal ga overtypen… Niet alleen duurt dat enorm lang, er sluipen ongetwijfeld fouten in.

Ik had een overzicht nodig van alle matchen van de reeks van Lucyvo. Dan kan ik onderstaande tabel invullen met speeldagen en datums:

Van één of meerdere internetpagina’s moet ik dus de gewenste gegevens ophalen. Nu, een webquery maken is niet moeilijk. Je kiest in het menu Data om Externe gegevens op te halen, via een webquery. Je krijgt dan een soort browser waarin je:

1. eerst naar de gewenste internetpagina gaat
2. daarbinnen moet je de gewenste tabel aanvinken in het groen

Patroon ontdekken

Het blijkt dat de kalender van Lucyvo op de site van het KVV op 22 verschillende pagina's staat. Waarom juist 22? Wel, er zijn 22 speeldagen in deze competitie. We moeten dus met onze VBA-code 22 keer achter elkaar een tabel ophalen. We moeten dan ook proberen een patroon te vinden in de URL’s van de webpagina’s. Dit is de (lange) URL voor de eerste speeldag:

http://www.kvv.be/asp/ResultatenRes.asp?Provincie=Antwerpen
&Reeks=VIERDE%20AFDELING%20B&Seizoen=2007-2008
&Keuze=RES&Speeldag=9/09/2007"

Helemaal achteraan zie je de datum van de speeldag staan. Alle wedstrijden vinden altijd op zondag plaats. Het KVV heeft één grote database met alle gegevens in: alle provincies, reeksen, seizoenen, wedstrijden, scheidsrechters, ploegen, etc. Via de provincie, reeks, seizoen en speeldag (datum) worden dan uit de database de juiste matchen opgehaald en getoond op de site.

Anders gezegd, als je binnen 1 reeks en seizoen blijft, is de datum het enige dat verschilt. Als we nu per speeldag een webquery uitvoeren, en we zeggen VBA welke datum het moet nemen, dan kunnen we alle gegevens ophalen. We maken een lus doorheen elk van de 22 pagina’s op de site. In de code laat ik de variabele iNrSpeeldag lopen van 1 tot 22.

Maar dat is niet voldoende. Ik heb niet 1, 2, 3, …, 22 nodig, wel de datums van de zondagen dat er gevoetbald wordt. Die heb ik onder elkaar getypt in de cellen A1 tot en met A22. De URL wordt dan:

Dim iNrSpeeldag As Integer
Dim sConn As String

For iNrSpeeldag = 1 To 22

    sConn = "URL;http://www.kvv.be/asp/ResultatenRes.asp?"
    sConn = sConn & "Provincie=Antwerpen&Reeks=VIERDE%20"
    sConn = sConn & "AFDELING%20B&Seizoen=2007-2008"
    sConn = sConn & "&Keuze=RES&Speeldag="
    sConn = sConn & Format(Range("A" & iNrSpeeldag), "d/mm/yyyy")
Next

VBA-code

Via Format kan je de datum in het juiste formaat krijgen. sConn is nu dus een String (stuk tekst) die aangeeft op welke URL de gegevens staan. Hier is mijn code:

Sub KalenderMetWebquery()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Code to retrieve competition games for Lucyvo
'    (illustrates VBA-code for webqueries)
' 03/08/07
'''''
Dim sConn As String
Dim rBeginCell As Range
Dim iNrSpeeldag As Integer

Set rBeginCell = Range("B1")

Application.ScreenUpdating = False

For iNrSpeeldag = 1 To 22

    Application.StatusBar = "Speeldag " & iNrSpeeldag & "…"

    sConn = "URL;http://www.kvv.be/asp/ResultatenRes.asp?"
    sConn = sConn & "Provincie=Antwerpen&Reeks=VIERDE%20"
    sConn = sConn & "AFDELING%20B&Seizoen=2007-2008"
    sConn = sConn & "&Keuze=RES&Speeldag="
    sConn = sConn & Format(Range("A" & iNrSpeeldag), "d/mm/yyyy")

    With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=rBeginCell.Offset((iNrSpeeldag - 1) * 10))

        .Name = CStr(iNrSpeeldag)
        .WebTables = "10"
        .FieldNames = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .Refresh BackgroundQuery:=False
    End With

Next

With Application
    .StatusBar = False
    .ScreenUpdating = True
End With
End Sub

Wat gebeurt er in de code?

In de code worden de eigenschappen van de 22 nieuwe webqueries ingesteld. Over het algemeen is dat niet echt nodig. Wat wel heel belangrijk is, zeker als jullie zelf deze code gaan toepassen voor andere websites en tabellen, is de regel .WebTables = "10". Dat kan nl. verschillen voor andere toepassingen. De tabel op de pagina's die we ophalen, is telkens de 10de tabel op die pagina. Pas aan indien dit anders is in jouw situatie.

Waar worden de gegevens gezet? Zie het stukje: rBeginCell.Offset((iNrSpeeldag - 1) * 10). We beginnen in cel B1 (zie bovenaan in de code), en dan is er 10 regels voorzien per speeldag. Je hebt dus B1 voor speeldag 1, B11 voor speeldag 2, B21 voor speeldag 3, enz. Offset(…) zorgt daarvoor.

Als je dan de gegevens hebt, moet je ze nog wat opkuisen en je bent klaar. Je kan o.a. de wedstrijden kopiëren en dan op een nieuw blad Plakken Speciaal > Waarden. Dan zijn de webqueries verdwenen.

Het lijkt veel werk als je dit zo leest, maar dat is het echt niet. In ieder geval véél minder werk dan alles overtypen. Ik gebruik analoge code voor Lierse. In eerste klasse zijn er 240 matchen; begin maar eens met typen ;) Voor Lierse (tweede klasse Jupiler League) zijn de verschillen:

'meer speeldagen
For iNrSpeeldag = 1 To 34

'andere website en links, geen datum achteraan (wel een nummer)
sConn = "URL;http://www.sport.be/nl/voetbal/lagere/"
sConn = sConn & "fixture.html?comp=2405&period=" & 17213 + i

'meer rijen tussen laten
Destination:=rBeginCell.Offset((iNrSpeeldag - 1) * 20)

'eerste tabel op de pagina
.WebTables = "1"

Hopelijk zet dit jullie aan tot gelijkaardige codes. Veel succes en tijdsbesparing gewenst!

• « Vorig Excel artikel
• Volgend Excel artikel »