Het gebruik van Parameters in Web Queries

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.