Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Bestand crasht Excel!!

Red mijn werkmap!
De beste tool voor Excel bestanden met problemen.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > WebQuery
This page in English

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 Excel 2003 en ouder vindt u deze optie onder Data, Externe gegevens importeren, Nieuwe webquery.

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;http://www.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;http://www.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.


Vragen, suggesties en opmerkingen

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

 


Comment by: Jan Karel Pieterse (6/18/2012 6:49:10 AM)

Hallo Gerard,

Ik neem aan, dat die links naar verschillende web pagina's verwijzen. Dit betekent dat de indeling van die pagina's verschillend zal zijn. Dit maakt het lastig om de gegevens van die pagina's geautomatiseerd binnen te halen.
Wel zou je een macro kunnen maken die de koppelingen allemaal in Excel importeerd. Maar daarna zal je alsnog de gewenste info handmatig uit ieder blad moeten halen. Niet veel sneller dan die koppelingen direct in IE openen en het vanaf daar kopieren en plakken in Excel.

 


Comment by: Peter (8/3/2014 4:15:46 PM)

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

 


Comment by: Peter (8/3/2014 4:16:49 PM)

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

 


Comment by: Jan Karel Pieterse (8/4/2014 4:59:56 PM)

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.

 


Comment by: Aad (1/15/2015 1:03:47 PM)

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?

 


Comment by: Jan Karel Pieterse (1/15/2015 1:47:13 PM)

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?

 


Comment by: aad (1/27/2015 11:06:08 AM)

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

 


Comment by: Jan Karel Pieterse (1/29/2015 11:15:01 AM)

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.

 


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: www.eileenslounge.com.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

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