Dit artikel is ook gepubliceerd op de Microsoft Excel team blog op
MSDN.
Inleiding
Excel biedt een erg handige methode om gegevens van websites te
halen, genaamd web queries. Web queries zijn toegevoegd aan Excel in
versie 97 en zijn verder verbeterd in de navolgende versies. Dit artikel
laat zien hoe een web query zodanig kan worden ingericht, dat de
resultaten afhankelijk zijn van waarden in cellen, waarbij het resultaat
bovendien automatisch wordt vernieuwd als de inhoud van die cellen
wijzigt.
De web query maken
Het maken van een web query is niet lastiger dan het in het lint de
tab Data aanklikken en dan in de groep "Externe gegevens ophalen" de
dropdown "Externe gegevens ophalen" te kiezen en tenslotte "Van Web te
selecteren.
In het dialoogvenster dat vervolgens verschijnt, moet de URL van de
site worden ingevoerd waarvan de data in Excel moet komen. Bijvoorbeeld:
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD,GGLD:2003-44,GGLD:en&q=%22Jan+Karel+Pieterse%22
Om een deel van de URL te laten werken als een parameter, moet dat
deel vervangen worden door wat tekst tussen aanhalingstekens en tussen
rechte haakjes (dit is alleen mogelijk, als de URL met de parameter in
de browser een "normale" pagina oplevert):
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD,GGLD:2003-44,GGLD:en&q=["Parameter1"]
Excel zal het deel (of de delen) tussen rechte haakjes interpreteren
als zijnde een parameter en na het klikken op Importeren of na
vernieuwen van de gegevens zal Excel een dialoogvenster tonen waarmee en
waarde voor de parameter kan worden gegeven:
Het mooie is, dat ook een celadres opgegeven kan worden als bron voor
de parameter:
Door de twee checkboxen aan te vinken wordt ervoor gezorgd, dat Excel
de query tabel automatisch vernieuwd wanneer de waarde van één van de
broncellen wijzigt.
Soms lukt het niet om een parameter in de url op te
nemen. Probeer dan de routine Demo2 hieronder aan te passen en uit te
voeren.
Web Query Parameters In VBA
Excel VBA geeft de programmeur toegang tot web query parameters door
middel van de Parameters collectie of het Parameter object.
Anders dan bij "normale" database queries, kan een parameter niet
worden toegevoegd door de Add methode toe te passen op de Parameters
collectie (alhoewel deze methode ook voor dat type query tamelijk lastig
te gebruiken is).
Alle benodigde parameters moeten via de Connection string worden
ingesteld. Excel zal op basis van het aantal sets [""] bepalen hoeveel
parameters er zijn en deze komen beschikbaar zodra de string is
toegekend aan de Connection eigenschap van het QueryTable object. In het
voorbeeld hieronder wordt een webquery aan het werkblad. Het
berichtvenster toont dat er geen parameters zijn gevonden:
Sub Demo()
Dim oSh As Worksheet
Set oSh = ActiveSheet
With oSh.QueryTables.Add("URL;https://jkp-ads.com",
oSh.Range("A3"))
.BackgroundQuery = False
MsgBox .Parameters.Count
End With
End Sub
Resultaat:
Als deze code wordt aangepast zodat er een parameter syntax in de
connection string staat, dan verandert het resultaat:
Sub Demo2()
Dim oSh As Worksheet
Set oSh = ActiveSheet
'Voer vast een geldige parameterwaarde in
'Range("A1").Value="WebQuery"
With
oSh.QueryTables.Add("URL;https://jkp-ads.com/Articles/[""PageName""].asp",
oSh.Range("A3"))
.BackgroundQuery = False
MsgBox .Parameters.Count
With .Parameters(1)
.SetParam
xlRange, oSh.Range("A1")
.RefreshOnChange = True
End With
End With
End Sub
Nu toont het berichtvenster het volgende:
Merk op, dat in het code voorbeeld hierboven de parameter gelijk is
toegewezen aan een cel (SetParam methode) en dat de query zo is
ingesteld, dat deze zichzelf vernieuwd als de waarde in de cel
verandert. RefreshOnChange eigenschap is True). De SetParam methode is
de enige methode waarmee de instellingen van het parameter object van
bijvoorbeeld "Vragen om invoer" naar een bereik kan worden omgezet en
waarmee het celbereik kan worden veranderd.
Conclusie
Zoals dit artikeltje heeft laten zien, is het niet moeilijk om een
parameter toe te voegen aan een web query. De truc zit hem in het feit
dat parameters alleen maar toegevoeg via de connect string (de URL)
gebruik makend van een zeer specifieke syntax.
