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.

 Een webquery invoegen

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:

Parameter opgeven voor een webquery

Het mooie is, dat ook een celadres opgegeven kan worden als bron voor de parameter:

Een parameter koppelen aan een cel

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:
berichtvenster met aantal gevonden parameters

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:

berichtvenster met aantal gevonden parameters

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.


Vragen, suggesties en opmerkingen

Laatste 8 commentaren van in totaal 11 (Toon ze allemaal):

 


Commentaar van: Peter (3-8-2014 16:16:49) deeplink naar dit commentaar

Goed artikel over webqueries, maar ik loop al veel eerder vast. Wanneer ik 'ophalen externe gegevens' aanklikt, vraagt excel telkens naar een odbc-driver. Huh? Wat zoekwerk op het web brengt me enkel bij betaalde software..... Hoe wat waar los ik dit op?
Vriendelijke groet

Peter


Commentaar van: Jan Karel Pieterse (4-8-2014 16:59:56) deeplink naar dit commentaar

Hoi Peter,

Ik zou Office Setup laten uitvoeren en een detect and repair laten doen. Als je toch in setup bent kan je gelijk even kijken of die ODBC drivers geinstalleerd staan.


Commentaar van: Aad (15-1-2015 13:03:47) deeplink naar dit commentaar

Ik heb een webquery gemaakt in excel om ledengegevens binnen te halen. Dit werkt perfect. Als ik nu het bestand naar een ander gebruiker stuur werkt het niet. Is het eigenlijk wel mogelijk om een webquery mee te sturen in een excel bestand en zoja hoe?


Commentaar van: Jan Karel Pieterse (15-1-2015 13:47:13) deeplink naar dit commentaar

Hoi Aad,

Dit zou moeten werken. Kan het zijn dat er ingelogd moet worden op die site zodat er een cookie aanwezig is op het systeem?


Commentaar van: aad (27-1-2015 11:06:08) deeplink naar dit commentaar

Hallo Jan Karel,

Ik heb de gegevens van de web query ondergebracht in een macro zodat ik de gegevens d.m.v. een druk op de knop kan ophalen.
Dit werkt prima totdat ik Excel afsluit en weer opnieuw opstart opdat moment werkt het niet meer. Hoe kan ik de web query wel laten werken via een macro.
Ik heb een ingelogd op de site dus er is een cookie aangemaakt.

Vriendelijke groet,
Aad


Commentaar van: Jan Karel Pieterse (29-1-2015 11:15:01) deeplink naar dit commentaar

Hallo Aad,

Webqueries naar sites waarop je moet inloggen werken heel vaak niet. Waarschijnlijk is de enige manier om de webquery te bewerken, dan ga je namelijk weer (via "iets" in Excel) naar die website en log je daar in. Dan krijgt Excel dat koekje denk ik en werkt e.e.a. weer. Heel frustrerend.


Commentaar van: Frits (12-1-2018 16:22:11) deeplink naar dit commentaar

Is er ook een oplossing om Web querys vanuit Excel 2003 te openen met een andere browser bijv. Firefox?
Wat je namelijk ook probeert, IE wordt altijd geopend, maar wordt door veel websites niet meer geaccepteerd waardoor je niets binnen kunt halen.


Commentaar van: Jan Karel Pieterse (12-1-2018 16:31:03) deeplink naar dit commentaar

Hoi Frits,

Helaas niet. Excel 2003 wordt ook al enige tijd niet meer ondersteund noch bijgewerkt met updates...


Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: excelexperts.nl/forum/index.php.




Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].