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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

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 > English site > Articles > WebQuery
Deze pagina in het Nederlands

Using Parameters With Web Queries

This article has been posted on the Microsoft Excel team blog on MSDN too.

Introduction

Excel provides a very useful option to gather data from websites, called web queries. These have been introduced with Excel 97 and have been further enhanced in the versions released after Excel 97. This article shows how you can setup a web query so that you can make the worksheet dynamically update based on values in so called parameter cells.

Setting up the web query

Setting up a web query is as simple as clicking the Data tab and then clicking the "From Web" button on the Get External Data tab:

Inserting a web query in Exccel 2007

For Excel 2003 and earlier you need Data, Get External data, New Web Query.

You’ll get this screen:

Inserting a web query in Excel

Enter www.bing.com and click the Go Button.

In the search box that follows, enter the word Question and hit enter. The screen refreshes and a new url appears in the address box:

http://www.bing.com/search?q=Question&form=QBLH&filt=all

Don’t do anything yet, first click the "Options" button and set the Web Query to return full html results (if so desired):Setting a WebQueries options

If you want these results in your sheet, just hit the import button now. However, if you want an interactive result in your sheet, enabling you to enter new search criteria in a cell, modify the url so it looks like this:

http://www.bing.com/search?q=["Question"]&form=QBLH&filt=all

You have now made part of the url work as a parameter, by replacing that part of the url with some text between quotes and square brackets (this will only work when using the URL would open a normal page in a web browser). The string you entered will be used as both the name of the parameter and the prompt. Excel will interpret the part between the square brackets as a parameter and prompt you for a value once you click the Import button or when you refresh the query table. Now we’re ready to click "Import". If the page takes time to load, you’ll see a progress screen: 

Waiting for results

Next Excel asks where to put the results; put them in cell A3 so we have room above the table:

Tell Excel where to put the results

Excel detects we have a parameter and now asks what value you want. As you can see you can select a cell for the parameter, lets use cell A1.

The fun thing is, that you can either fill in a value or you can select a cell as an input for the parameter:

Excel prompts for a parameter value

By checking the two checkboxes shown above, you ensure that Excel will automatically update the query table when you change the content of the cell(s) you selected.

Sometimes, inserting the parameter part in the url fails. In such cases, try modifying the routine called Demo2 as shown below so it has the URL you need and the parameters at the proper positions. Make sure you have the proper cell addresses in place as well. When done, run the routine. You should now be able to use the dynamic web query.

Working With Web Query Parameters In VBA

Excel VBA offers the programmer access to web query parameters through the Parameters collection of Parameter objects.

Unlike "normal" database queries, it is not possible to add a parameter to the Parameters collection using the Add method (though even for that type of query, doing so is an awkward process).

Instead one has to add all parameters one needs to the Connection string. Excel will determine the number of parameters from that string once it has been applied to the Connection property and then the Parameters collection becomes available to VBA. In the example below, a web query is added to a worksheet. The message box shows, that there are no parameters:

 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

Result:
Showing the number of parameters

If the code shown above is changed to add a parameter in the connection string, things change:

 Sub Demo2()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'Make sure we already have a valid value for the parameter
    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

Now the messagebox shows:

Showing umber of parameters

Note that in the code sample the parameter has been tied to a cell (SetParam method) and that the query is set to update when that cell changes (RefreshOnChange property set to True). The SetParam method is the only way to change the setting of the parameter object from e.g. prompt to Range and to change the range the query parameter uses as its source.

Conclusion

As this article shows, adding a parameter to a web query is relatively easy. The tricky part is the fact that you need to know that parameters can only be added through the connect string (the URL) by using a very specific syntax and that parameters can only be added by changing the connection string.


Comments

All comments about this page:


Comment by: Jan Karel Pieterse (3/8/2006 11:52:02 AM)

Dear Reader,

I invite you to comment to this page. It will help me to improve its quality!

Thanks.

Jan Karel Pieterse

 


Comment by: Mahesh (3/10/2006 10:58:55 PM)

Thanks for the info

 


Comment by: Willem (4/14/2006 6:34:43 AM)

Great, but I do miss some info:
1 like where/how do the mentioned ranges A1 and H11
2 Each time I run thsi macro I do have an extra entry in the QueryTables but they don´t show up the the DATA/Get External Data menu - so how do I use it in practice?

 


Comment by: Jan Karel Pieterse (4/15/2006 9:51:50 AM)

Hi Willem,

Question1: Seems the question got messed up, could you rephrase it?

Question 2: The Querytable ends up in your worksheet and if you enter data into cell H11, the Querytable in cell A1 should update automatically.

 


Comment by: John (4/25/2006 7:57:24 PM)

This may be just what I'm looking for. I just need to know if it is possible to do what I need to with it, and a step in the right direction.

Simple example, I want to use vba to extract data from something like an ebay page. Each item has the same words with a colon, then the value I want. Can I extract this info only?

I'm actually using access to do all this as well. I'm willing to do whatever work is necessary but just need some direction. Thanks :)

-John

 


Comment by: Jan Karel Pieterse (4/25/2006 10:35:04 PM)

Hi John,

Why would you want to use VBA to extract the data? You could set up one page to extract the data from the web, using the technique shown in this article. If your data is well structured and always the same width, you can then place formula's to the right of the imported data that extract the info you need. In the settings of the webquery, you can specify that Excel has to copy formulas alongside the data.

Regards,

Jan Karel

 


Comment by: George (5/29/2006 4:10:43 PM)

If I create a query as follows:
http://finance.yahoo.com/q?s=["Ticker"]
it does not allow me to select the appropriate table or set parameters

 


Comment by: Jan Karel Pieterse (5/29/2006 9:18:54 PM)

Hi George,

Well, it worked fine for me using that address (though I did not sign in). What Excel version are you using?

 


Comment by: George (5/30/2006 7:58:22 AM)

I am using Excel 2003. When selcting New Web Query... I get the yahoo finance page which is my home. I then replace the URL to select a stock,e.g.IBM. I select the table - replace IBM with ["Ticker"] Press Go
Get incorrect results.

 


Comment by: Jan Karel Pieterse (5/30/2006 8:26:25 AM)

Hi George,

I did it like this:

- Data, get external data, new web query
- Enter the URL: http://finance.yahoo.com/q?s=IBM
- Click Go, wait for results and click Import
- Select cell A3 to receive the result.
- Go back to Data, Get external data, edit query, change the url to
http://finance.yahoo.com/q?s=["Ticker"]
and hit "Import"
- eventually you'll be prompted to select a cell that holds the ticker code, select A1, check the boxes you want and hit OK. Now enter a ticker code in A1.

What I could not achieve was to get a specific table from that page.

 


Comment by: George (5/30/2006 11:31:57 AM)

Thnaks for your comments. I was finally able to coax the table I wanted. I typed ["Ticker"] whne the query was in a string format
http://finance.yahoo.com/q/cq?d=v1&s=["ticker"]
I suspect the original query
http://finance.yahoo.com/q?s=IBM
only allows the whole page Perhaps you have abetter explanation. Thanks anyway

 


Comment by: Jason (8/12/2006 10:12:21 PM)

Is there a way to setparam without a msg box?

I have a query I want to have look in Cell A1 for the "parameter." If I use your exact code but change the cells and URL to suit my needs it works but pops up that msgbox.
If I dont put the msgbox in the code it gives an error "object required" or something similar and doesnt run at all.

 


Comment by: Jan Karel Pieterse (8/14/2006 1:24:30 AM)

Hi Jason,

Removing the entire line of code starting with "MsgBox" should do the trick.

 


Comment by: Sudhee (11/19/2006 10:27:07 PM)

I've been trying to import the CSV file containing the historical prices in yahoo finance using web query. But the problem is that all the column values of the original csv file fall under a single column on refresh. Is there any solution for this???
Thanks!

 


Comment by: Jan Karel Pieterse (11/19/2006 10:42:06 PM)

Hi Sudhee,

I think this is a case where a webquery won't help. You'll need a macro, which uses the Workbook.Open method.

Switch on your macro recorder and select File, Open and simply enter the entire url to the CSV file and click Open.

 


Comment by: Francis (12/16/2006 10:40:54 AM)

Hi Jan,
Thanks for the info. This is great for single info such as getting IBM stock quote but how can I made it work for getting a series of different identifiers by reading column A and the output result in Column B.

cheers, francis

 


Comment by: Jan Karel Pieterse (12/17/2006 3:19:57 AM)

Hi Francis,

Well, that would mean you'd have to create a web query for each one, or (alternatively) create one web query and use a macro to update the parameter and after refreshing copy the result.

 


Comment by: Francis (12/19/2006 6:48:25 AM)

Hi Jan,

Would you show me an example of how to create one web query and use a macro to update the parameter and after refreshing copy the result. Thanks

cheers, francis

 


Comment by: Jan Karel Pieterse (12/19/2006 8:22:07 AM)

Hi Francis,

I haven't got code at hand, but you could try modifying this code. It goes in the thisworkbook module:

Option Explicit

Private WithEvents oQT As QueryTable


Private Sub oQT_AfterRefresh(ByVal Success As Boolean)
    If Success Then
'Here you can place your code to copy results
        MsgBox oQT.ResultRange.Address
    End If
End Sub

Private Sub Workbook_Open()
    Set oQT = ActiveSheet.QueryTables(1)
End Sub

 


Comment by: Harry (1/26/2007 9:35:58 AM)

Hi

Any way to change other values apart from the http-string in the iqc file?

For example the string following
Selection=

I tried parameters, but these are not recognised, and I haven't a clue which properties I would need to set todo this in VBA.

Many thanks

 


Comment by: Mark (4/5/2007 3:20:04 AM)

Hello Jan,
I have been attempting to find Excel Query Paramater assistance for days - geared toward the novice. I have the same problems with your example as George sent on 5/30/06 - the string does not work. In my case I am struggling with individual MSN MoneyCentral pages for particular companies.

Able to set the query to their site, with a "Symbol" reference added to the end of the URL string - as you suggest. But any attempt to subsequently change the URL to prompt my paramater cell fails...with selection of the "Import" key as you suggest...hitting "Import" results in a completely different web page. I am not "eventually be prompted to select a cell that holds the ticker code" as you suggest to George above.

This is perplexing, but would be very helpful if solved. Am I wandering into VBA territory.

Your assistance would be greatly appreciated.


Mark
Sacramento, CA

 


Comment by: Jan Karel Pieterse (4/5/2007 4:49:10 AM)

Hi Mark,

Could you post a working sample URL that works, along with an indicaton of what part needs to become a parameter?

 


Comment by: Ralph W Lundvall (5/18/2007 10:11:51 AM)

An existing web query's base URL changed.
It now has a port number. How do I change existing (over 100) web queries all at once?

 


Comment by: Jan Karel Pieterse (5/18/2007 11:05:25 AM)

Hi Ralph,

Maybe my Flexfind can do that for you?

 


Comment by: Lialydayhah (5/22/2007 5:34:56 PM)

Hi

Greetings!!!Cool page!Cool work!
Bye







 


Comment by: Ralph W Lundvall (5/23/2007 10:30:07 AM)

Flexfind worked. We had 535 webqueries to change!!
Thanks!!!

 


Comment by: Jordan OBrien (6/1/2007 12:17:14 PM)

Similar to some of the above posts, I am trying to figure out how to create an automatic web query that will retrieve data from hundreds of web pages. Let me describe this hypothetical situation:

I want to gather updated stats weekly from a baseball website. Namely I want to find each teams' slugging percentage leader. This requires me to go to each teams' page, sort by SLG and select the top player and number. Here is the url:

http://www.sportsline.com/mlb/teams/
stats/ARI?&_1:col_1=15

It's probably best if I don't describe all of the things I have tried, but since I am a novice...there are many. Anyway, I want to have an excel spreadsheet with all the teams' abbreviations. Then, have the query or macro read the team abbreviation, plug it into the web query address and eventually pull out the numbers/text that I want and create a table. The benefit would be that I could update it weekly and not go through the process of entering each teams abbreviation each time I need to run all 30+ queries. Please help as this translates into a work project.

Thank you for any assistance you can provide.

 


Comment by: JohnDo (6/28/2007 8:45:56 AM)

I spent last 2 hours browsing your website, it's breathtaking.




 


Comment by: fish_hfd (6/30/2007 1:07:53 PM)

It's nice



 


Comment by: Antique (7/12/2007 1:47:56 PM)

Hi. Me very much to like here. I shall advise this site to the friends.
I am sorry for my English. I only learn this language.

 


Comment by: manthano (8/17/2007 8:06:29 PM)

I have a VBA web query, but it has a problem. The URL itself has brackets ([]) in it, so every time i run the macro Excel asks me to fill in the parameter. Here's a similar URL to the one that's giving me a problem.

http://examplesite.com/search.php?arg[]=excel&arg2=vba

If you have any suggestions I'd love to hear them.

 


Comment by: SQ (8/20/2007 3:58:11 AM)

Hi Jan,

Your website is amazing and I am glad I got a link that leads me to here!

I just started out on VBA and am very new to OOP. I am trying to do a web query using excel and I need to use the beforerefresh and afterrefresh events from the querytable object.

I have taken out this code from Microsoft Support and I need your help to understand the concept:

Public WithEvents qt As QueryTable

Private Sub qt_BeforeRefresh(Cancel As Boolean)

' Declare variables.
Dim a As Integer
Dim My_Prompt As String

' Initialize prompt text for message box.
My_Prompt = "Data will be refreshed."

' Get YES or NO result from the message box
    a = MsgBox("Do you want to refresh the data now?", vbYesNo)

' Check to see whether YES or NO was selected.
If a = vbNo Then

     ' Change prompt text for message box.
     My_Prompt = "Data will not be refreshed."

     ' Cancels the Query Refresh.
     Cancel = True

End If

' Displays message box before refresh (or non-refresh) occurs.
MsgBox My_Prompt

End Sub

On the Insert menu, click Module.
Click in the Code window for the module and enter the following code:

Dim X As New Class1

Sub Initialize_It()
Set X.qt = Thisworkbook.Sheets(1).QueryTables(1)
End Sub

From:http://support.microsoft.com/kb/213187

Could you kindly enlighten me the purpose of the procedure Initialize_It?As in what is the meaning of Set X.qt = Thisworkbook.Sheets(1).QueryTables(1)?

And when the example put QueryTables(1), is it the name property of the querytable?

Sorry if this question sounds elementary as I just started out VBA yesterday. Thank you!

 


Comment by: Jan Karel Pieterse (8/20/2007 7:00:34 AM)

Hi manthano,

You'll have to escape the brackets:

http://examplesite.com/search.php?arg%5B%5D=excel&arg2=vba

 


Comment by: Jan Karel Pieterse (8/20/2007 7:04:22 AM)

Hi SQ,

To your questions:

1. Could you kindly enlighten me the purpose of the procedure Initialize_It?As in what is the meaning of Set X.qt = Thisworkbook.Sheets(1).QueryTables(1)?

Well, The "Public WithEvents"... line at the top tells VBA you want an object variable of which you want to listen to it's events. The routine INitialise_It tells VBA what object the VBA has to watch for you. Otherwise there is no way for VBA to know which querytable to look at.

2. And when the example put QueryTables(1), is it the name property of the querytable?

No, it is just it's index, you might be better off using the name, since that ensures you are addressing the proper object:

QueryTables("Name Of QueryTable")

 


Comment by: manthano (8/20/2007 9:43:50 AM)

I tried escaping the brackets, but it still brings up a box asking for a parameter. Thanks.

 


Comment by: Jan Karel Pieterse (8/20/2007 9:53:45 PM)

Hi manthano,

Could you give me a working example URL (one that gives results in Internet explorer but fails with Excel)?

 


Comment by: manthano (8/21/2007 6:07:43 PM)

This URL returns information from the Library of Congress by giving an ISBN to the term argument. I use a form in excel to enter the ISBN then create the web query in vba. As you can see the brackets are escaped (host%5B%5D=z3950.loc.gov)

http://indexdata.com/phpyaz/demo/ccl.php?host%5B%5D=z3950.loc.gov%3A7090%2Fvoyager&term=0596527411&action=Search

 


Comment by: Jan Karel Pieterse (8/22/2007 11:20:31 AM)

Ho Manthano,

You should just assign an empty cell to the first parameter. IN the url you gave, (in Excel, whilst defining the Web query) replace the ISBN part in the URL with ["ISBN"]

Excel should pick that up as a second parameter. Next, click the parameters button and assign ISBN to a cell.

 


Comment by: Surya (9/11/2007 9:58:04 AM)

Hi,
The information provided is very informative but i have a question, i have a req. say i will be searching for "Football" in google and the query has to go to each of the search results and get me one field which i am looking for, this it has to do for all the extracted links. Can you please show a way how to do it.
Thanks

 


Comment by: Jan Karel Pieterse (9/11/2007 10:08:24 AM)

Hi Surya,

I have built a solution for a customer once which is very similar to what you are asking here. Get back to mme if you're interested in me developing that for you commercially.

 


Comment by: Omar (9/25/2007 7:28:55 PM)

Is there any similar aplication on MS Access, without using VBA???

Even MS Access is more powerful than MS Excel, the last one is pretty friendly and easy to use!

 


Comment by: Kumar (12/3/2007 9:34:40 AM)

I think I'll benefit from the setParam method. Will visit your site again and refer others. Thanks!

 


Comment by: Pete (12/11/2007 1:44:02 PM)

Please help me on this. Working off of a list of zip codes in an excel 2007 column, I want a web query that can take go to the usps.com website and return the state (or city and state) for each of the zip codes. Thanks.

 


Comment by: Jan Karel Pieterse (12/11/2007 10:22:45 PM)

Hi Pete,

The only way is by setting up a separate web query for each cell.

Once you've set up one web query as I've shown on this page, you CAN copy that web query down to other cells. You must however change the parameter cell of each copied query manually by selecting the query cell and clicking the parameters button on the external data toolbar.

 


Comment by: Pete (12/19/2007 1:02:57 PM)

I'm not sure how to set up even one web query in this case, since the URL does not include the zip code. Please look at the website before answering. Can you show me specifically how to set this us. Thanks.

 


Comment by: Jan Karel Pieterse (12/20/2007 11:00:04 AM)

Hi Pete,

You did not include the url?

 


Comment by: Mike Carroll (1/2/2008 3:27:22 PM)

Hi Jan,

Your comment on 12/11/2007 agrees with what I have found so far: "the only way is by setting up a web query for each cell." This is strange if true. It means that each time you have a different parameter (or set of parameters), you need a different query. In other programming contexts, you can use the same query with different parameters. It seems as though web queries with parameters are not quite finished yet, in Excel 2007.

 


Comment by: swapnil (1/18/2008 9:29:01 AM)

I am trying to setup a web query using a parameter. The web url is http://in.finance.yahoo.com/q?s=INFY&m=NS . This works fine when I import into the sheet. Now how to change the stock code INFY to a parameter. If I try to edit the query and enter ["INFY"] then the import does not work. Thanks a lot!

 


Comment by: Jan Karel Pieterse (1/18/2008 10:04:02 AM)

Hi swapnil,

These steps do it:

- Insert your query using the fixed URL.
- Select a cell within the resulting table
- Open the visual basic editor
- hit control+g
- paste this line:
activecell.QueryTable.Connection="URL;http://in.finance.yahoo.com/q?s=[""Param""]&m=NS
- put cursor in that line and hit enter.
- go back to Excel
- now the parameters button on the external data toolbar should be visible
- click it and set the parameter to get its value from a worksheet cell.

 


Comment by: swapnil (1/18/2008 11:23:43 AM)

Thank you very much!

It worked perfectly.

 


Comment by: Juan (3/5/2008 12:32:06 PM)

the history data that i down to my excel query are on CVS. How can i converter this datas for what excel read as a real number and to CSV
Thanks for you help

 


Comment by: pauloulu (4/9/2008 3:31:22 AM)

I am new to this forum and I think you all will help me to enhance my
knowledge. I have been here many times and have always found this to be
a great place for information and advice about a wide range of topics

Paul

 


Comment by: Don (4/29/2008 11:00:55 AM)

Great information!
How do you query a website with a URL address that does not change when selecting options (drop down buttons) within the page? The page content changes according to the selection but the URL remains the same? Thanks in advance.

 


Comment by: Jan Karel Pieterse (5/4/2008 11:59:59 AM)

Hi Don,

I'm afraid you cannot do that using this technique.

 


Comment by: Noam (8/5/2008 2:20:41 PM)

How would I go about getting a table from a website:
http://tango01.cit.nih.gov/sig/members.taf?
_function=list&_start=1&_UserReference=0054D973E88736A54898BE24

I need to get all subsequent tables when you hit next 100 members. The only part
that changes is start=1 to start=101 etc...

Thanks in advance!

 


Comment by: Jan Karel Pieterse (8/6/2008 10:52:27 AM)

Hi Noam,

You'd use the method I show above and create a parameter for the start argument in
the url. Then you could write a small macro that changes the value in the parameter
cell with steps of 100 and refreshes the page and copies the tables to another sheet.

 


Comment by: Noam (8/7/2008 12:14:47 AM)

Thanks Jan,
I think I got the parameter part down from the first part of your article.
Unfortunately I don't have any experience in programming or writing Macros. Is this
something that would take me a long time to learn? Thanks for your time. Much
appreciated.

 


Comment by: Jan Karel Pieterse (8/7/2008 4:06:28 AM)

Hi Noam,

Quite a bit of time I'm afraid. Let me give you a head start.

I assume your webquery is shown on Sheet1 and you use cell A1 for the parameter. I
further assume your web query does NOT refresh automatically when cell A1 is changed
(one of the settings of the parameter).

Paste this code into a normal module in the workbook with your web query:

Sub RunQueries()
    Dim oSh As Worksheet
    Dim lCount As Long
    For lCount = 1 To 201 Step 100
        Sheet1.Range("A1").Value = lCount
        Sheet1.QueryTables(1).Refresh False
        Worksheets.Add
        Set oSh = ActiveSheet
        Sheet1.UsedRange.Copy
        oSh.Range("A1").PasteSpecial xlPasteValues
        oSh.Name = "Results " & lCount
    Next
End Sub

 


Comment by: Dennis ODonovan (8/15/2008 1:26:14 PM)

Can this method be used to parameterize a database query (querying a database on
our corporate network), or does it only work with web queries? Is there a link to
instructions on how to parameterize a database query? Sorry if this is posted in
the wrong area. Thanks!

 


Comment by: Jan Karel Pieterse (8/17/2008 10:03:14 PM)

Hi Dennis,

Certainly:

<a target="_blank" href="http://www.dicks-clicks.com/excel/ExternalData6.htm">Check
out this page.</a>

 


Comment by: Dennis ODonovan (8/18/2008 11:57:20 AM)

Thanks, Jan Karel!

I am ashamed to say I am a lazy programmer and was looking for a simpler way to do
this. I found such a way here (you may recognize the site!):
http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-
data-queries/ but was having trouble getting it to work with aggregated fields
(e.g. count, sum, etc.), even found a comment asking about the same error I was
getting: "Not a valid column name" on the criteria value. Anyway, I played around
with it and got a workaround: build a simple query without the aggregated fields,
but be sure to include the fields you want to parameterize, and set up the
parameters. Then if you modify the query to include the aggregated fields the
parameters are retained since they are already established. But if the aggregated
fields are specified in the initial query the MS Query tool would not allow
parameters.

Thanks again!

 


Comment by: Jim (9/5/2008 8:50:45 AM)

Is there a way to set up a web query so that the user is prompted for the url of
the table to be imported? I'n trying to set up queries for multiple web beased
tbales, and some of the tables haven't been made yet.

 


Comment by: Jan Karel Pieterse (9/8/2008 12:58:33 AM)

Hi Jim,

It depends on the exact way the url is set up. If part of the url is an argument to
show specific data (like I show in the example above), then you can do that.

 


Comment by: Thomas Hoofensmire (10/24/2008 7:02:08 AM)

I tried to set up a web query to get information from my online bank account. How
can I set up the web query to use my logon information so that I can get to the
summary page of my online bank account? Some people from my work suggested that I
might have to use "send keys". No idea what that is, but I am willing to try it if
it works.

 


Comment by: Jan Karel Pieterse (10/24/2008 7:43:49 AM)

Hi Thomas,

It might work, but is tricky to do. The routine might look like this:

Sub GetQuery()
    SendKeys "%ddwwww.jkp-ads.com~{TAB}username{TAB}password~%i~"
End Sub

 


Comment by: S.Suresh Mumbai (12/22/2008 10:53:51 AM)

Any best tutorial for webquery- advanced level. Thanks

 


Comment by: joemerieux@tiscali.co.uk (7/29/2009 9:50:03 AM)

Hi
Is there a way of selecting a particular table on the webpage that has the data?
When I undertake the import manually, I have to select the table that contains the data I want to import. I assume that using automatic up-dating that Excel records which of the tables was selected so that it can get the correct table for the up-date. However, I can't find where it stores any table identifier.
When I write a query, I can import the whole of the webpage, but I don't want the whole page - just one of a number of tables.
Thanks
Joe

 


Comment by: Jan Karel Pieterse (7/29/2009 10:11:07 AM)

If you would have recorded a macro, you could have spotted the WebTables property of the querytable, this controls which table is imported.

 


Comment by: Will (10/27/2009 2:16:10 PM)

This info above is great. I have a more elaborate issue. I wish to perform a web query using an .iqy file. There are 3 variables that go into this stock option query. 1) Stock symbol, eg, QQQQ, (2) Month, as represented by a number, eg, 12 = DEC, (3) Year, eg, 2009. I would like to enter these 3 into seperate excel cells.
    The result would be a query into an MS page that displays a Stock Options Chain. Here is a link of the page: http://moneycentral.msn.com/investor/options/default.asp?Symbol=QQQQ&Month=12&Year=2009
    Any insight into this issue would be greatly appreciated.

 


Comment by: Jan Karel Pieterse (10/30/2009 11:51:15 AM)

Hi Will,

Why do you use the iqy file for the query? You can simply use the url directly in the Data, get external Data, new web query dialog and follow the steps I outlined above.

 


Comment by: JILL (11/8/2009 3:02:23 PM)

I am having trouble with filtering imported data from the web. I want to import information from a directory in which each department has a different URL. I am unsure of how to label each piece of information in the VBA so I can filter them across my excel sheet (each person a different row).

 


Comment by: Jan Karel Pieterse (11/8/2009 10:19:46 PM)

Hi Jill,

Can you post an example URL?

 


Comment by: JILL (11/9/2009 2:44:53 AM)

http://www.uwosh.edu/cob/faculty-and-staff/faculty

for the Academic Departments I am trying to pull each professor's: First Name, Last Name, URL, Phone Number, Department, Email, streetaddress, city, state, zip code

All across column D - N, starting at Row 6.

I have imported each department onto it's own worksheet within a template workbook by recording each individual macro.

And for any information that is not available, I am to fill in the cell with "UNKNOWN"

 


Comment by: Jan Karel Pieterse (11/11/2009 3:07:06 AM)

Hi Jill,

Wouldn't it be easier to contact the website admins and ask them an extract from the database with the information you need?

 


Comment by: Steve (11/13/2009 11:26:11 PM)

Hi Jan Karel

Is it possibleto use the web query procedure in reverse?

What I would like to achieve is to have a user type one location in a cell, say Prague and then another destination in another cell, say Brno so that when I click my button (which already opens Bing map search) it would populate the from and to boxes on Bing so that the user then only has to click calculate.

Sorry if this is off topic but it sounded right to me

Best regards

Steve

 


Comment by: Jan Karel Pieterse (11/15/2009 10:19:22 PM)

Hi Steve,

You could setup the web query to use two parameters.
Set both to their own cell and neither to update on change.
Then add a button that executes this simple macro (I assumed only one webquery on the sheet):

Sub UpdateQT()
    Activesheet.QueryTables(1).Refresh
End Sub

 


Comment by: james sloan (12/18/2009 12:46:14 PM)

I have set up a 'football' spreadsheet, and have web queried into MS Excel 2003. This year, I attempted to import data from "http://www.usatoday.com/sports/sagarin/nfl09.htm", and have 'selected' an arrow as I have in the past. But this time, all I get is the web page address. Other tries give me a selection of data in running text, and am unable to import into tables in Excel. I have been retired for 17 years now, and this is one of the few enjoyable diversions that occupy my time.

What I am after are the tables on "Jeff Sagarin NFL ratings" page.

Thank you very much for any help you may extend to me. It is greatly appreciated.

jslo

 


Comment by: Jan Karel Pieterse (12/19/2009 11:42:41 AM)

Hi james,

I can repro your problem, but unfortunately have no resolution at hand. Looks like the site does not adhere to some rules Excel wants it to follow somehow.

ALl I can think of is to select the entire page and have that returned to Excel.

 


Comment by: John (12/30/2009 7:36:32 AM)

Hi Jan, I am not sure if my last comment went through, so here we go again.

I am doing work in Excel with NBA Player stats from basketball-reference.com. If you search for a player and get his page, the URL changes to this - http://www.basketball-reference.com/players/b/bryanko01.html

Everytime you go to another players page, the only part on the page that changes, is b/bryanko01. For example, if I wanted to find chris paul, that part would change to p/paulch01.

I figure out the code that it uses, so when a person times in a name on the spreadsheet the players code comes out.

How would I create a Web Query so that everytime the player's code changes the player's "Per Game" stat table comes onto the spreadsheet? I only want that single table NOT the whole page.

Thanks for your time!

John

 


Comment by: Senthamizh Arasu (12/31/2009 1:38:30 AM)

I have been working in a Result analysis project in which I have to convert only a necessary table data's or result table alone from web or html file into Microsoft access file, if not at least as an excel file. that too using vb. can you say how to do this. please.

 


Comment by: Jan Karel Pieterse (12/31/2009 4:13:00 AM)

Hi John,

The best way is to modify the queries command text with some VBA and then set the parameters manually.

Select a cell in the table and run this:

Sub Changeit()
    ActiveCell.QueryTable.CommandText = "http://www.basketball-reference.com/players/[""Player""]"
End Sub


Then the parameter button on the external data toolbar should become available.

 


Comment by: Jan Karel Pieterse (12/31/2009 4:14:06 AM)

Hi Senthamizh,

I am not sure what you are asking. Could you elaborate?

 


Comment by: kevin (2/23/2010 1:48:01 PM)

Jan,

I'm using web query to pull in data for a dynamic "stock market" type of application. Problem is the site that I need to pull the data from sometimes changes it layout. By that I mean the data that I pull in would normally be found in cell A99 but sometimes would be found in A101. What can I do to either prevent the the data from "floating" around? Or better yet what can I use similar to a VLOOKUP type of command? Thanks!

 


Comment by: Jan Karel Pieterse (2/23/2010 11:07:05 PM)

Hi Kevin,

What you need to do to resolve the issue depends highly on the layout and how it changes. A Vlookup formula may very well be the way to go indeed, but I can't say for sure.

 


Comment by: KyAZ (4/5/2010 2:16:40 PM)

I have some devices that have a built in web server. I can set up a web query to pull this data from the device, but through excel the data is clumped into tables. How can i pull out a specific data cell from a table? Do i need to look at the html code? Is this even possible with vb?

 


Comment by: Jan Karel Pieterse (4/5/2010 10:47:08 PM)

Hi KyAZ,

An Excel webquery will always import either the entire web page or one of it's tables entirely. Have a look at this page for an alternative approach:

http://www.dailydoseofexcel.com/archives/2006/11/29/html-tables/

 


Comment by: Mark (5/12/2010 8:40:19 AM)

I wrote vba code in Excel 2000 that loads web pages and extracts data from them, and the code runs flawlessly on a Windows 98 machine. When running the same code in Excel 2003 on a Windows XP machine, .Refresh occasionally fails. I assumed that something was out-of-whack on that machine and continued to use the Windows 98 machine. I just got a Windows 7 machine with Excel 2007 and I saw .Refresh fail on it as well. Any ideas why it would fail? Also, any idea why the vba code takes 3 times as long to run on the new machine as on the old 98/2000 setup????

Here is the subroutine in question...

Sub GetAnalystsEstimatesIntoScratch(ticker)
    Dim retryCount As Integer
    retryCount = 0
    
GAEIS_Start:
    Set shResults = Workbooks("earnings2.xlsm").Worksheets("Scratch")
    shResults.Cells.ClearContents
    Dim url As String
    url = "URL;http://finance.yahoo.com/q/ae?s=" + ticker
Set qtResults = shResults.QueryTables.Add(Connection:=url, Destination:=shResults.Cells(1, 1))
With qtResults
    .WebFormatting = xlNone
    .WebSelectionType = xlAllTables
    Dim r As Boolean
    On Error GoTo ErrorHandler
    r = .Refresh(False)
End With
Exit Sub
ErrorHandler:
    If (retryCount < 3) Then
        retryCount = retryCount + 1
        Resume GAEIS_Start
    End If
    If ((MsgBox("GetAnalystsEstimatesIntoScratch() for <" + ticker + "> failed. Retry?", vbYesNo)) = vbYes) Then
        retryCount = 0
        Resume GAEIS_Start
    End If
End Sub

 


Comment by: Jan Karel Pieterse (5/14/2010 6:10:26 AM)

Hi Mark,

Your current code adds a new querytable each time. You could also set up the querytable once and on subsequent runs only change it's connection property and then refresh it.
Sometimes Excel doesn't like repeatedly adding querytables to a sheet, even if you remove them before adding a new one.

 


Comment by: Mark (5/14/2010 1:18:36 PM)

Thanks for the reply. I changed my code to reuse the querytable as you suggested. Now my code runs much faster (the Add call takes 15-20 seconds) but .Refresh still fails occasionally. Any other thoughts about why it might fail? Again, the same code works flawlessly on a Windows 98 machine with Excel 2000.

 


Comment by: Jan Karel Pieterse (5/15/2010 10:39:37 AM)

Hi Mark,

In what way does the refresh fail?

What happens if you do the refresh manually a couple of times, does that fail too?
Maybe this is a problem with Internet connection settings rather than with Excel, I don't really know?

 


Comment by: jet solomon (6/11/2010 7:31:23 AM)

Hi,

I would like to know how to run a web query in excel that allows you to retrieve for instance a stock quote from excel, by changing a cell reference in excel. For instance Cell A1 would represent the stock ticker symbol. when i change the ticker symbol in cell A1, my web query will retrieve data based on the new symbol. I tried your "Bing" example but couldnt translate it over to say "finance.yahoo.com".

I know how to run a normal web query to retrieve a stock quote but it is not dynamic when i change the cell reference. Also, I know how to run the MSNMoney Central with ticker parameter however, if I'd like an outside/ different source from the web for various stock information it would be more useful.

Please let me know if you have a macro or straight web query with parameter.

Thank you very much and look forward to hearing back!
Jet

 


Comment by: Jan Karel Pieterse (6/11/2010 10:19:28 AM)

Hi Jet,

See my comment of 5/30/2006 (click this link to see all comments on this page:

http://www.jkp-ads.com/Articles/WebQuery.asp?AllComments=True

 


Comment by: Lou (6/25/2010 1:49:12 PM)

Hi,

Can the web query be refreshed when the Excel sheet is closed?

Thanks.

Lou

 


Comment by: Jan Karel Pieterse (6/26/2010 6:50:58 AM)

Hi Lou,

Unfortunately, no.

 


Comment by: mastor (7/16/2010 5:32:32 AM)

i made a web query to update from web every 30 min and i made also some macro i went the macro should run automatically afther refreshed how do it?

 


Comment by: Jan Karel Pieterse (7/16/2010 6:33:41 AM)

Hi Mastor,

You can schedule a macro that runs every 30 minutes, which refreshes the web query and then calls the subsequent macro to update the remainder.

Sub Update()
    Sheets("Sheet1").Querytables(1).Refresh False
    CallYourOtherMacroHere
    Application.Ontime Now + TimeValue("00:30:00"), "Update"
End Sub

 


Comment by: ryan (7/16/2010 11:27:12 AM)

when using visual basic for web queries, how do you set it up to maintain HTML formating and any hyperlinks?

 


Comment by: Jan Karel Pieterse (7/17/2010 2:32:28 AM)

Hi Ryan,

Why not record a macro whilst setting this up?

 


Comment by: JC (7/19/2010 4:54:08 PM)

hello,

is sub demo(2) intended to do anything other than show a message containing the number of parameter used? as i see [or don't :)] it should call some information from 'http://www.jkp-ads.com/Articles/WebQuery.asp' and use cell A3 as the starting point from which to display it.

i created a module in excel 2010, copied the code to it, and all i see is the message box containing the parameter count, and 'WebQuery' entered into cell A1. i tried to change the link and parameter value in cell A1 using the vba editor, and i get the same result --- just a parameter count of 1 displayed in a message box --- no other information but the value assigned to a1 appears.

thinking this might have to do with a recent upgrade to excel 2010, i tried this in excel 2003. still no information from the web appears.

do you have any suggestions i could use to make this work for me? or, am i getting the intended result? the permissions in my workbook should allow for this to work.

 


Comment by: Jan Karel Pieterse (8/16/2010 2:53:22 AM)

Hi JC,

It sets up the query so it automatically refreshes when you change the value in the parameter cell. Does the query refresh when you anter a new value into the cell?

 


Comment by: Jay (9/15/2010 2:51:53 AM)

Hi, is it possible for each refresh to be located in different ranges of cells (below each other) because if you say A3 then any previous data is deleted by a refresh, I want to keep this data for historic purposes

Regards
Jay

 


Comment by: Jan Karel Pieterse (9/15/2010 5:29:54 AM)

Hi Jay,

No, I'm afraid not. You'd have to write a bit of code that loops through the cells, puts each value in the parameter box in turn, wait for the Web query to update, copy the result of the webquery elsewhere.

 


Comment by: Luca (9/30/2010 1:34:44 AM)

If I have a proxy, how can I do in VBA to set the credential? My web queries are scheduled during the night so the proxy authentication is a problem. Thanks

 


Comment by: Jan Karel Pieterse (9/30/2010 2:05:23 AM)

Hi Luca,

Could you please explain a bit more? I have little knowledge of "proxy's". How does the login process work exactly?

 


Comment by: Luca (9/30/2010 2:16:32 AM)

Hi,
every http request passes from proxy: this mean that when i use a web query (opening the *.iqy file) there is a pop-up that ask me Username and Password for the authentication of the proxy. For a request on-demand it is not a problem because I can specify Username/Password. But if I schedule the execution of the web query it doesn't work until someone specify the required information (Username/Password). Can you help me?

 


Comment by: Jan Karel Pieterse (9/30/2010 5:47:39 AM)

Hi Luca,

Do you always have to supply Username and password when going to a webiste?

 


Comment by: Luca (9/30/2010 7:04:38 AM)

Yes... the proxy requires always the credential for the authentication.
I want to know if it's possibile to set che credential of the proxy
in VBA; in this way I can schedule the execution of the procedure.

 


Comment by: Jan Karel Pieterse (9/30/2010 7:05:53 AM)

Hi Luca,

You could use Sendkeys, but where and how exactly you would implement that depends on when exactly the username/password dialog is shown.
If you run the code, exactly when does the dialog come up?

 


Comment by: David (10/7/2010 12:14:45 AM)

Hello Jan,

I would like to set up an Excel Web Query to a URL that requires ["username"] and ["password"] parameters. Furthermore, the user should be prompted upon each Import/Refresh. If I follow your example of putting the Parameters in the URL it should work fine, however, the user has the option to checkboxing 'refresh automatically' and 'use this value for future refreshes'. How can I safely prompt the user for auth info each time?

Thanks in advance!

 


Comment by: Jan Karel Pieterse (10/7/2010 2:12:08 AM)

Hi David,

One way is to set up the web query as in the example above, using both username and password as parameters tied to cells. Then once done, remove your login credentials from the cells before you save the file.
After that, you can safely hand over the file to your users and instruct them to enter the login details in the appropriate cells.

 


Comment by: Geoff (10/22/2010 11:20:47 PM)

Hello,

When I use [] to identify a parameter excel does not stop to ask me for a parameter input. I'm wondering what I'm going wrong.

This is what I'll type into the url for the webquery:

http://www.eve-metrics.com/api/item.xml?type_ids=["id"]

 


Comment by: Jan Karel Pieterse (10/23/2010 2:13:48 AM)

Hi Geoff,

I'd advise you to try to modify the code sample called Demo2 shown above -so it has your url- and run that routine.

 


Comment by: Geoff (10/23/2010 3:20:53 PM)

Ok... I did this, but now how do I get it to display the data from the XML table?

 


Comment by: Jan Karel Pieterse (10/24/2010 3:17:03 AM)

Hi Geoff,

This seems to do the trick for me:

Sub foo()
    Dim osh As Worksheet
    Set osh = ActiveSheet
    With osh.QueryTables.Add("url;http://www.eve-metrics.com/api/item.xml?type_ids=[""Id""]", Range("A3"))
        MsgBox .Parameters.Count
        With .Parameters(1)
            .SetParam xlRange, osh.Range("A1")
            .RefreshOnChange = True
        End With
        .Refresh
    End With
End Sub

After running this code once, all I have to do is update cell A1 with new Id's.

 


Comment by: Geoff (10/24/2010 9:20:20 AM)

Hello Jan,

Thanks for your help, unfortunately, I get a run-time error when trying to run this code. I copied and pasted the code into VBA. Associated the macro to a button, and when I push the button I get this run-time error:

"Run-time error '1004':

Unable to open www.eve-metrics.com(etc...). The required Internet protocol is not installed on your computer, or the Internet address you requested may not be valid."

Is there something I need to install on my computer?

 


Comment by: Jan Karel Pieterse (10/24/2010 10:13:19 AM)

Hi Geoff,

My website's comment parser has removed the
h t t p : / / in front of the url, which is why this does not work.

So change this line:

With osh.QueryTables.Add("url;www.eve-metrics.com/api/item.xml?type_ids=[""Id""]", Range("A3"))

To:

With osh.QueryTables.Add("url; h t t p ://www.eve-metrics.com/api/item.xml?type_ids=[""Id""]", Range("A3"))

(of course you have to remove the spaces!)

 


Comment by: Jan Karel Pieterse (10/24/2010 10:16:31 AM)

Hi Geoff,

I fixed the comment parser so the http bit does not get chopped off.

 


Comment by: Sally (12/6/2010 7:33:07 PM)

The link of my web query is a website that requires my login and password. It appears that when I perform a web query on a new work book or a saved excel file, the query shows that it requires log in and password. So how can I continue with the step ?

 


Comment by: Jan Karel Pieterse (12/7/2010 12:47:04 AM)

Hi Sally,

I honestly don't know. I've never tried this on a sign-in website. Would you know one which has a sign-in that is free too?

 


Comment by: Aswin (2/4/2011 3:02:17 AM)

When using Web query how do we get the selected data appear once the data is downloaded. for e.g from list of 100 stock quotes i m interested in particular 10 stocks of my choice. How do i change the query so that only this 10 stock quotes are shown when ever the data refreshes. Also how do we merge formatting macros with the query macros.

 


Comment by: Hein (2/4/2011 3:12:33 AM)

Hi,

I have a web query in Exel that usually worked fine till i reinstalled my hole pc. Now if i want to add a new query it opens the result in a text file, so i cant even choose what to import. The queries works fine on a other pc.

Any idees?
Hein

 


Comment by: Jan Karel Pieterse (2/4/2011 3:48:33 AM)

Hi Hein,

I'm sorry, I'm not clear on what exactly happens. Doesn't the Edit query dialog open?

 


Comment by: Jan Karel Pieterse (2/4/2011 3:52:04 AM)

Hi Aswin,

The easiest way is to have your query on a separate sheet. Then on another worksheet use formulas to fetch the data you really want.

 


Comment by: Hein (2/4/2011 5:49:28 AM)

The thing is it opens the query dialog but if i run the query it doesn't dispay it in the dialog, it opens it in wordpad. like i said this is only on my pc an old queries wordks fine but cant make new ones.

 


Comment by: Jan Karel Pieterse (2/4/2011 5:59:12 AM)

Hi Hein,

Really odd. Which Excel version is this? Can you list the steps you take to get this, including the site's url?

 


Comment by: Hein (2/4/2011 6:21:49 AM)

This is what i'm trying to ge "0|595.00" its also just that on the webpage. Is there maybe a setting in IE or Excel that makes it open in wordpad?

 


Comment by: Hein (2/4/2011 6:44:14 AM)

Its Excel 2007 but cant give the url as it contains login details. isnt there something else? it only happens on this pc and older queries still work, just cant add new ones.

 


Comment by: Jan Karel Pieterse (2/4/2011 7:14:06 AM)

Hi Hein,

Really odd, I can't imagine what might be causing this.
Could you perhaps email me a screenshot of the query dialog? When does it open notepad, after pressing the go button?

 


Comment by: Imran (2/5/2011 10:52:34 PM)

Hi,

I am trying to pull data from the following base url:

http://www.powerexindia.com/PXILReport/pages/MCPReport_New.aspx

However, the underlying url int he script is like this:

http://www.powerexindia.com/PXILReport/Reserved.ReportViewerWebControl.axd?Mode=true&ReportID=9bdf4bd553204060865be107f2705193
&ControlID=e2859e2be9fd&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportArea&Controller=ClientControllerctl00_ContentPlaceHolder4_ReportViewerData&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top

Here, the variable parameters are ReportID and ControlID

What I want is that I should be able to enter these two parameters in two different cells and then they should be automatically pulled in the above url as I change them.

How can this be made possible pls guide me I am new to VBA and macros with only basic understanding.

 


Comment by: Jan Karel Pieterse (2/6/2011 10:49:47 PM)

Hi Imran,

Did you try the steps provided in this article?

 


Comment by: IMRAN NAQVI (2/8/2011 10:31:29 PM)

Yes, but my query has 2 different parameters. So I am not able to achieve this.

 


Comment by: Jan Karel Pieterse (2/8/2011 11:41:11 PM)

Hi Imran,

In the article, there is a section on VBA. Find the sample code starting with "Sub Demo2".
Change the URL in that code to match yours.
Something like:

Sub Demo2()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'Make sure we already have a valid value for the parameter
    Range("A1").Value="WebQuery"
    With oSh.QueryTables.Add("URL;http://www.powerexindia.com/PXILReport/Reserved.ReportViewerWebControl.axd?Code=true&ReportID=[""ReportId""]
&ControlID=[""ControlId""]&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportArea&Controller=ClientControllerctl00_ContentPlaceHolder4_ReportViewerData&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top", oSh.Range("A3"))
        .BackgroundQuery = False
        MsgBox .Parameters.Count
        With .Parameters(1)
            .SetParam xlRange, oSh.Range("A1")
            .RefreshOnChange = True
        End With
        With .Parameters(2)
            .SetParam xlRange, oSh.Range("B1")
            .RefreshOnChange = True
        End With
    End With
End Sub

 


Comment by: IMRAN NAQVI (2/9/2011 12:20:53 AM)

Well, thank you so much Jan.

I wonder if these cell reference given by you should be A3 instead of A1, since this is where you want be to enter the web query

Range("A1").Value="WebQuery"

This is because in the line oSh.QueryTables.Add.... you have mentioned the cell reference as A3, which, I think, you are trying to initiate the web query in.

Am I right? If yes, should I change the A1 in the first code line above to A3 in the original code?

 


Comment by: IMRAN NAQVI (2/9/2011 12:25:56 AM)

This is what I essentially need to do, as I understand.

1. Enter the ReportID in cell number A1
2. Enter the ControlID in cell number B1
3. Go to cell A3, and initiate the query there manually first
4. The data is then imported in a table
5. Every time I change values in the A1 and A2, the tables should change accordingly.

Am I right?

 


Comment by: Jan Karel Pieterse (2/9/2011 2:52:58 AM)

The steps you list are almost correct.

1. Remove the codeline from Demo2 that says
Range("a1")="WebQuery"
1. Enter the ReportID in cell A1
2. Enter the ControlID in cell B1
3. Run the sub called Demo2
4. The data is then imported in a table starting from cell A3
5. Every time I change values in the A1 and A2, the tables should change accordingly.

 


Comment by: IMRAN NAQVI (2/9/2011 3:23:58 AM)

Thanks for your frequent replies Jan.

The only seem the execution of the code seems to return is a message box with 2 written in it.

What's the problem?

Alternatively, is there a way to select the entire URL from a particular cell, instead of picking up two paramters and reconstructing the URL every time. This would be easier, I suppose.

 


Comment by: Jan Karel Pieterse (2/9/2011 4:36:42 AM)

Hi Imran,

Of course. You would only need something like:

1. Set up the web query manually the first time only.
2. Designate a cell to contain the full URL (say cell A1)
3. Create a small macro:

Sub ChangeQueryURL()
    With Activesheet.Querytables(1)
        .Connection="URL;" & Range("A1").Value
        .Refresh False
    End With
End Sub

3. Put a forms button on your sheet and assign the above macro to it.

 


Comment by: IMRAN NAQVI (2/9/2011 9:46:56 PM)

Hi Jan,

It only returns a error box with 400 displayed inside it.

 


Comment by: Jan Karel Pieterse (2/10/2011 5:31:12 AM)

Hi Imran,

Please email me your current sheet. (see address at bottom of page)

 


Comment by: Rudy Gobin (3/31/2011 10:30:31 AM)

Hi ,,

I created a web query with parameter (parameter from a cell on another worksheet). The parameter uses vlookup to retrieve
the cell result from another Excel file. However, it won't update automatically -- I have to manually refresh it (at least it updates so i know the parameter is fine).

Any ideas--Thanks

Rudy

 


Comment by: Jan Karel Pieterse (3/31/2011 2:41:25 PM)

The web query does not respond to a change in a formula result unfortunately.

 


Comment by: Rudy Gobin (4/1/2011 7:30:34 AM)

I have two other similar spreadsheets that have pages which update on a vlookup formula cell parameter. Not sure why this one won't update automatically when the cell changes. As mentioned earlier, it does update if I click "Refresh Data".

Could it be the formatting of the cell or the sequencing of the updates?

In the two spreadsheets that work, I simply use your [""Param""] solution in the URL, and then point to a cell on another worksheet. This cell does use a vlookup query to obtain the parameter value, and both work fine.

That's why I not sure about earlier response. ("The web query does not respond to a change in a formula result unfortunately").

If you have another ideas/solutions,I would certainly appreciate it.

Thanks much.

Rudy


 


Comment by: Jan Karel Pieterse (4/1/2011 10:19:48 AM)

Hi Rudy,

Maybe I spoke too soon. Check the parameter settings of the webquery, maybe the auto-update has been unchecked (I've seen it happen).

 


Comment by: Rudy Gobin (4/1/2011 11:28:33 AM)

Hi Jan,

I'm assuming you are referring to the checkbox "Refresh automatically when cell value changes". I always keep this checked. The cell value changes (from a Vlookup formula), but there is no automatic update. My workaround is to set the page to Refresh every 1 minute, but that is annoying.

Thanks
Rudy

 


Comment by: Rudy Gobin (4/1/2011 12:33:14 PM)

I stopped using the Offset function and found a different way of getting the parameter cell to change--works now!

Thanks
Rudy

 


Comment by: Jan Karel Pieterse (4/3/2011 5:11:08 AM)

Hi Rudy,

WHat way did you use to fix the problem?

 


Comment by: Rudy Gobin (4/3/2011 8:22:41 PM)

Hi Jan,

It was a 3 step sequence--the most important step was removing the OFFSET function to search and simply using "another" VLOOKUP function to change the parameter cell.

My original URL was "http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/mlb/teams/pastresults/2011/team2980.html".

There are only 30 teams in MLB, so I had to find a way to change the team number in the above URL. For that, I simply used a post from yourself (addressed to Swapnil) where you showed how to use the [""Param""]function.

I then indexed each MLB team using their unique ID in Covers.com team logs (Cleveland is "2980" in the above URL).
I created this index in another sheet.

There were actually 3 lookup functions to get to the cell that "Refreshes automatically when cell value changes".
(it would lookup one cell, then another , and another to get to the refresh cell).

The problem originated when I started using the OFFSET function to pull up the originating (or the first lookup)cell from the wegpage in Excel.

I simply got rid of the OFFSET function, and put back in a regular VLOOKUP function--it worked fine!

So you were right that the parameter cell cannot use formulas (like OFFSET)--but for some reason, it works with simple formulas like VLOOKUP. EXCEL is kind of quirky--not sure why this is the case.

Thanks again for your help
Rudy

 


Comment by: Jan Karel Pieterse (4/3/2011 9:45:26 PM)

Hi Rudy,

Thanks for letting us know!

 


Comment by: chella pandian (4/20/2011 1:51:59 AM)

I want to link the data in the excel sheet to get the live quote of particular company, kindly help me to do this.
regards
pandianrc@gmail.com

 


Comment by: Jan Karel Pieterse (4/20/2011 9:49:31 PM)

Hi Chella,

I'm not sure what I can do for you? Have you got a URL to view a quote in internet explorer? If so, you can use the method in this article?

 


Comment by: Q (5/13/2011 1:09:24 PM)

I believe all of your problems are due to the fact that your WEB site queries are going to a "POST" Method. Verses practically everything on the WEB about Excel's Web Query function relies on the WEB site using the "GET" Method.

The GET method places the parameters in the HTML address. Such as the name and values separated with the "?" symbol.

The POST method requires an HTML code sent from the form or input of the WEB page not in the address of the WEB page.

Microsoft link below explains this and how to POST the variables in VBA...
http://msdn.microsoft.com/en-us/library/aa140050%28v=office.10%29.aspx

 


Comment by: Mustafa (6/27/2011 5:09:49 AM)

I would like to know if there is an alternative way of doing this.

 


Comment by: Jan Karel Pieterse (6/27/2011 11:16:33 PM)

Hi Mustafa,

Well, you can always revert to using VBA code that updates the url.

 


Comment by: Yashik (7/18/2011 12:25:46 AM)

Hello there !

Can anyone tell me how i can go about publishing an excel workbook for web query in a simplest form. On the user side, they should be able to refresh their excel data.

Regards
Yashik

 


Comment by: Jan Karel Pieterse (7/18/2011 2:23:18 AM)

Hi Yashik,

What exactly do you mean by Publishing?

If you insert the web query and save the file to a network folder others can have access to, you shold be fine.

A web query can be refreshed on Excel running on a Client machine only, not with an Excel file opened in the Excel web application (or Excel services as it ships with Sharepoint).

 


Comment by: Yashik (7/18/2011 4:13:25 PM)

HI Jan,

Thanks for your prompt reply.

When i used the word "Publishing" I meant that i want to use a excel workbook which contains master data that the client side Workbook needs to be refreshed as and when new data emerges. Hope i am making sense to you this time.

So in another words technically, My workbook will be sitting somewhere on a secured webfolder. a client who uses the exact copy of my workbook will refresh this workbook using a command button. So how can i implement this on the server side so that the client side can use web query to get the current data.

thanks

 


Comment by: Jan Karel Pieterse (7/19/2011 1:37:03 AM)

All the web server has to do is generate a html page which you can refer to directly from Excel using a web query.

As soon as the web query is told to refresh, Excel will pull that html page from your server.

So in this case, the data that needs to be updated is not in Excel, but "served" as a web page.

 


Comment by: namenotshown (8/4/2011 11:37:49 AM)

sorry if it's been asked but
what if the url don't change? example:
http://www.baak.its.ac.id/pengumuman_gugus/cari.php
so now you can't do it with excel?

 


Comment by: Mike (8/12/2011 9:56:52 AM)

Hi Jan,
Thank you very much for the informative article and for taking the time to answer so many questions. I'd like to add one more to the list...
I would like to be able to import a large number of web pages with similar URL's to Excel. Each URL has the same prefix, and the suffix is ?id=234320, for example. That is, each URL ends with a different six digit number. Ideally what I'd like to be able to do is enter the six digit numbers into a long list in Excel (a hundred or more at a time), then set up web queries to automatically go out and get the data from each of those pages and bring it into Excel. Each query results in 3 columns by 297 rows of data in Excel, so perhaps I could have the six digit numbers spaced every 300 rows in column A, then have the queries populate in columns B:D going down the sheet.

Hopefully the above is somewhat clear. My question is, is there a way that I can create all these web queries without having to type them in one by one? Can I somehow copy a query and paste it down the sheet over and over?

Thanks in advance for any help you can give me,
Mike

 


Comment by: Jan Karel Pieterse (8/22/2011 2:15:59 AM)

Hi Mike,

You could use the functionality shown above in combination with a small macro. All the macro needs to do is update the cell, wait for the querytable to finish updating and then copy the results to a different worksheet.

 


Comment by: Jan Karel Pieterse (8/22/2011 2:50:31 AM)

Hi namenotshown,

Sure, just use the url as given.

 


Comment by: Sunit Gadhia (8/30/2011 3:59:55 AM)

Hi,

My question is similar to the ones about but a little deiiferent.lets say i scan all the books in my school library example isbn# 9780470044025, now putting this number on www.amazon.co.uk website gives me all the details of the books, I would like to import this details such as title, author, format, price into their subsquent fields in excel.How can i do this? please help you would be saving tons of our time.

 


Comment by: Jan Karel Pieterse (8/30/2011 7:48:55 AM)

Hi Sunit,

If I follow the instructions on this page, I can successfully create a cell which is used as a parameter input for the search in amazon.
The URL you need to input in the webquery in Excel to get this done is:

http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords=["ISBN"]&x=0&y=0

 


Comment by: David (9/2/2011 9:27:17 AM)

Hi Jan,

Do you know where to find lists of query parameters for various different websites please?

For example, http://www.gummy-stuff.org/Yahoo-data.htm has a table showing query parameters ("special tags") for finance.yahoo. I am trying to find a similar list for money.msn and, in general, to know how to know find appropriate parameter strings for other web queries.

Thank you for your help.

David

 


Comment by: Jan Karel Pieterse (9/4/2011 10:57:53 PM)

Hi David,

The only way I know is by navigating to the location you want on the website and checking the URL in the browser.
If a website lists them you're in luck, otherwise it is up to you to find them!

 


Comment by: john (9/13/2011 2:57:51 PM)

Hello,
I am trying to utilize these examples to go through a listing of 100 rows to retrieve data from a website. I'm not clear on two things:
1) The only thing that should change would be my URL, which would be in Column B (B2:B101) with each URL taking one row.
2) I am getting about 6 rows of data for each URL, so I'd like to pivot this data returned into columns instead, so it is more manageable to work with.
I can easily get the data using the web query, but it doesn't seem like it is easy to work with when I try to use a variable URL (column B).


 


Comment by: Jan Karel Pieterse (9/13/2011 11:12:22 PM)

Hi John,

Whether you can use my example depends on the variation in your URL's. Are they all pointing to the same website with perhaps just a different parameter, or are they more or less different URL's?

 


Comment by: john (9/14/2011 7:39:17 AM)

Yes, they are:
For Example:
http://<<IpAddress>>/sys_Count.html
So the variable would be: <<IPAddress>>.
Then I need to get Table 1 (which contains 1 row and two columns and Table 2 which contains 2 columns and 5 rows. I want to pivot those rows into columns so everything takes just one line.
Does that make sense?
Thank you!

 


Comment by: Jan Karel Pieterse (9/14/2011 8:00:09 AM)

Hi John,

In that case I expect you cannot use this technique.
Manually create one (or two, since you want two tables) and then create a macro that runs through your range of cells that contain the needed URL's and use the content of those cells to update the URL of the querytable, refresh the QT and then copy the resulting content to a different worksheet.
Some example code:
Sub ExtractData()
    Dim oCell As Range
    For Each oCell In Worksheets("URLs").Range("A1:A10")
        'Change URL's of queries
        Worksheets("Sheet2").QueryTables(1).Connection = "URL;" & oCell.Value
        Worksheets("Sheet2").QueryTables(2).Connection = "URL;" & oCell.Value
        'Update queries
        Worksheets("Sheet2").QueryTables(1).Refresh False
        Worksheets("Sheet2").QueryTables(2).Refresh False
        With Worksheets("Sheet3")
            'Copy cells A1:C10 and paste-transpose them on the first empty row on sheet3
            Worksheets("Sheet2").Range("A1:C10").Copy
            .Range("A" & .Rows.Count).End(xlUp).PasteSpecial xlPasteValues, , , True
        End With
    Next
End Sub

 


Comment by: Roman (9/23/2011 12:39:53 PM)

How to write a VBA function that returns a value (one value only) from a web query?

For Example, in any cell I type
=QueryValue(Q)
and it will return the number from the query Q, where e.g.
Q="http://download.finance.yahoo.com/d/quotes.csv?s=^SPX&f=l1"

Will appreciate any help!
Please send the answer to my email.

 


Comment by: Jan Karel Pieterse (9/23/2011 11:10:21 PM)

Hi Roman,

A simple way would be to just setup the web query like shown above and then have your cell point directly to the cell in the querytable containing the result.

 


Comment by: nand (10/25/2011 7:07:23 AM)

I have been reading and trying out the sample code in the Excel Book VBA and Macros by XyZ (not sure if I can mention books here as such). The problem is that in order for me to learn anything I need to create my own project. To cut a long story short, I am trying to get only the specifc price back from a yahoo URL using a webquery however I get back the whole dataset and I am not sure how I can just get the value I want. Example of my code (WIP):


'ActiveCell.Offset(, -3) = USD and ..(-2)=GBP


myCurrency = "URL;http://uk.finance.yahoo.com/q?s=" + ActiveCell.Offset(, -3) + ActiveCell.Offset(, -2) + "%3DX&ql=0"

myRange = ActiveCell.Address

asp = "%3DX&ql=0"


    With ActiveSheet.QueryTables.Add(Connection:=myCurrency, Destination:=Range(myRange))
        .Name = "q?s=USDINR%3DX&ql=0"
        .FieldNames = False
        .RowNumbers = 0
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """table1"""
        
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

End Sub



The data returned is:

Last Trade: 1.0057
Trade Time: 14:15
Change: Up 0.0019 (0.1943%)
Bid: 1.0057
Ask: 1.006


However currently i only want the last trade price but if its easy to query the webquery then i would build a sheet with seperate bid and ask prices too.

Any help will be appreciated muchly.

 


Comment by: Jan Karel Pieterse (10/26/2011 2:57:11 AM)

Hi nand,

Two remarks.
1. Why not have the webquery return everything it returns and use a formula to extract the information you need?
2. Your code keeps adding new webqueries to your worksheet, this will cause trouble in future. Instead, manually add that webquery *once*, then use VBA to update it's Connection property:

'Remove entire with...End With part, replace with this:
Range(MyRange).QueryTable.Connection = myCurrency

 


Comment by: Rahul Kumar Singh (11/18/2011 9:57:07 PM)

Hi,

I have tree questions regarding Intracting with Web using Excel VBA.

1: How to Fetch the URL of already opened site using Excel VBA?

2: How to activate any Browser Window?

3: Is there any method to specify which browse you want to choose.( IE, Crome, FireFox etc.)


Thanks
Rahul Kumar Singh

 


Comment by: Manju (11/23/2011 9:36:38 AM)

How to change only a part of the URL that is already entered in a excel sheet with values in another sheet

 


Comment by: Jan Karel Pieterse (11/23/2011 10:13:49 PM)

Hi Manju,

Isn't that exactly what this page is about?

 


Comment by: Erica (11/30/2011 10:09:26 AM)

Hi,

I am trying to do an excel webquery which works with drop down lists- i have to choose a value in drop down list A of the first webpage which will then bring me to another webpage where i will have to choose a value in drop down list B before the table that i want is shown on another webpage.

The query works something like "www.yahoo.com.search=[value from drop down list A] & search2=[]value from drop down list B]"

Is there any way that i can do a dynamic webquery which will get the option value of the drop down lists?

 


Comment by: Jan Karel Pieterse (11/30/2011 11:05:14 PM)

Hi Erica,

Depends. As long as the sequence of actions results in a specific URL which contains the arguments you need to show that page, you're good to go.

If however the URL does not show your choices you'll have to use VBA and automate the Internet Explorer object.

 


Comment by: sam (12/7/2011 9:51:41 AM)

Is it possible to query a excel file stored on a
https:\\ web page.

 


Comment by: Jan Karel Pieterse (12/8/2011 1:11:34 AM)

Hi Sam,

I really don't know!

I would just try, maybe it is possible, but not using a web query, but rather using Data, From Other sources, MSQuery.

 


Comment by: Al (12/12/2011 8:24:28 AM)

My webpage has a drop-down list. It has a default entry. I can change the entry to what I need but I the query always retrieves the default entry. How do I specify the list entry I want the page to return and then import this data?

 


Comment by: Jan Karel Pieterse (12/12/2011 8:30:12 AM)

Hi Al,

Depends. Does the URL not show which choice you made from the dropdown? If not, I'm afraid a web query is not going to work.

 


Comment by: Al (12/12/2011 8:49:42 AM)

Yes, it does. The page is displayed in the Excel window exactly as I want but when I do the import it reverts to the default value in the drop-down list.

 


Comment by: Jan Karel Pieterse (12/12/2011 10:45:09 AM)

If the URL can be publicly accessed, then please post the url (or email it to me).

 


Comment by: Al (12/12/2011 10:52:39 AM)

Unfortunately, it cannot be. I'll find another way! Thanks for your time.

 


Comment by: Felipe (1/4/2012 1:11:48 PM)

Hi,

Does any of you know how to use web queries to complete a form and then copy/paste the returning web page.

In more detail. I enter a company's name in the field "Business or Trade Name" in this website: http://bls.dor.wa.gov/LicenseSearch/Default.aspx and I copy/paste the UBI value from the resulting page in Excel. The list is long and I want to do this automatically. I would be if I can get the whole resulting web page in Excel since I can write a macro to extract the UBI information from there.

The resulting website URL is always the same so I can't trick Excel to ask me for a parameter (which would be the company's name).

I would greatly appreciate any help.

Thank you,
Felipe

 


Comment by: Jan Karel Pieterse (1/4/2012 11:32:54 PM)

Hi Felipe,

The only way I know is by using VBA. Here is a nice starting point:

http://www.tek-tips.com/faqs.cfm?fid=6399

 


Comment by: SAMEH (1/31/2012 7:45:57 AM)

The table that i am importing from web has date parameter so i should write it every day ...eg:

www.sam.aspx?....&....&operatingdate=08jan2012

Do you have any method to write the today date in the operating date just after opening the file...?
thanks for help
SAMEH
Email: samehamor2009@yahoo.com

 


Comment by: Jan Karel Pieterse (2/1/2012 10:44:04 PM)

Hi Sameh,

The article above allows you to only enter the date into a cell and then have the data pulled in automatically. Isn't that what you need?

 


Comment by: Bernard Wielfaert (2/9/2012 7:33:40 AM)

Hi,

I have a web query to a server that prompts me each time for autentication (login/password). Can I somehow give these credentials from within VBA ?
I run the report like this :

'Run the Report
Cells(4, 2).QueryTable.Refresh BackgroundQuery:=False

Best regards

 


Comment by: Jan Karel Pieterse (2/9/2012 7:54:51 AM)

Hi Bernard,

I'm afraid not. And I don't have such a webquery to experiment with either!

 


Comment by: Robert (2/12/2012 5:10:35 PM)

Okay i am trying to get a webquery from the following site with the following:
<vb>
Sub Url_Static_Query_api()
    With ActiveSheet.QueryTables.Add(Connection:= _
    "url;https://api.eveonline.com/account/Characters.xml.aspx?keyID=[""keyid""]&vCode=[""vcode""]", _
    Destination:=Range("o1"))
        .BackgroundQuery = True
     .TablesOnlyFromHTML = True
     .Refresh BackgroundQuery:=False
     .SaveData = True
    End With
End Sub</vb>
and i get:


/eveapi
/@version /@version/#agg /cachedUntil /currentTime /result/rowset/@columns /result/rowset/@key /result/rowset/@name /result/rowset/row/@characterID /result/rowset/row/@characterID/#agg /result/rowset/row/@corporationID
2 2 2012-02-13 01:04:48 2012-02-13 00:07:48 name,characterID,corporationName,corporationID characterID characters 91613750 91613750 1390490131

But when i do not use the cell references i get:

2 2012-02-12 23:42:22 characters characterID name,characterID,corporationName,corporationID Robert VanHaren 91613750 Armatech 1390490131 2012-02-12 23:51:08

How can i get the bottom results but with the cell references?

 


Comment by: Jan Karel Pieterse (2/12/2012 11:05:14 PM)

Hi Robert,

Have you entered valid parameter values into the cells the query gets the parameters from?

 


Comment by: Michael Harris (2/17/2012 9:33:10 AM)

I am trying to import financial data into Excel using a WEB query. On the web page is a drop down which consists of dates from which you can choose your start date.

For IBM, Start date 2002, the query looks like this;
http://www.advfn.com/p.php?pid=financials&btn=start_date&mode=annual_reports&symbol=NYSE%3A["Symbol"]&start_date=9

Note the start date is item 9 for 2002 on the drop down.

My problem is that the drop down list size changes, company to company, so for GOOG, for instance, the drop down list is shorter and the 2002 start date might be now be in position 6.

I guess the start date needs to be variable depending on
the drop down but can't figure out how to do it. Have some rudementary understanding of VB. Thanks

 


Comment by: Jan Karel Pieterse (2/20/2012 7:44:37 AM)

Hi Michael,

I'm afraid you will have to figure out what the logic is on which index is needed. Perhaps you can use a bit of VBA that first refreshes the page, then checks (if applicable) a cell which shows the currently imported year and then adjusts the index accordingly. There are some examples on how to change the URL using VBA on this very page.

 


Comment by: Chris (3/4/2012 3:17:11 PM)

I'm trying to import several tables that contain hyperlinks to other tables (which I am also importing). Is there a way to modify the Web Query settings such that the hyperlink is preserved? When importing I only get text.

Thanks,

Chris

 


Comment by: Jan Karel Pieterse (3/5/2012 5:03:25 AM)

Hi Chris,

I believe you can change that by clicking the Options button in the Webquery definition dialog (the one that lets you select the URL and the table) and choosing the full html option. (See 4th screenshot on this page).

 


Comment by: sam (5/29/2012 7:17:12 PM)

hi,
i am having a web-query in excel which updates items in stock from my wholesalers website, but in order this should work i need to put in my user and password, so at the first time when i made it i went thru the steps Manuel and entered all info and then redirect the query to download the info, then i made that it should get refreshed every 5 minutes so i don't get logged out, but the problem is that if i need to restart my computer or to shut down excel and reopen after 15 minutes i get logged out from my wholesalers website so i cant pull the data automatically, i tried to use send keys it doesn't work, cause the website does not redirect to the field of user name, to direct it you need to Manuel click on username and enter it in the box, so my question is there any way to record a macro which should remember how to log-in and then automatically update my web-query?

 


Comment by: Jan Karel Pieterse (5/30/2012 7:02:29 AM)

Hi Sam,

I think the only way to do this is to pull in the data using VBA entirely. I've seen examples of doing this some time ago. Check out this google search result:

http://www.google.nl/search?hl=nl&q=vba+ie+automation+fill+in+form+fields&rlz=1I7ADFA_en

 


Comment by: Jan Karel Pieterse (5/30/2012 7:03:24 AM)

Hi Sam,

I think the only way to do this is to pull in the data using VBA entirely. I've seen examples of doing this some time ago. Check out this google search result:

http://www.google.nl/search?hl=nl&q=vba+ie+automation+fill+in+form+fields&rlz=1I7ADFA_en

 


Comment by: Al (6/8/2012 2:31:36 PM)

Hello - I have a VBA code which imports data from a website.
In the webpage when you click on "go to next page", only part of the URL changes. To be more clear, this is the URL I am working on:
http://miami.craigslist.org/boo/index100.html

When you go to the next page,"index100" is changed to "index200". Now I may need to import many pages, that is, "index100" can reach "index10000".

I am trying to create a loop in VBA that does that. Do you have any suggestions ?

Thank You,

 


Comment by: Jan Karel Pieterse (6/8/2012 2:43:15 PM)

Hi Al,

Please post the relevant portion of your code.

 


Comment by: Al (6/8/2012 3:05:53 PM)

Hello,

I think I am close to what I need. I created the loop however, with each loop, the "Enter Parameter value" box pops up, an I have to enter the index, 100,200...etc. I am trying to feed it the index automatically. This the relevant portion of the code:

Alaa = 100
For P = 1 To 3 Step 1
With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://miami.craigslist.org/boo/index[""Alaa""].html", Destination:=Range("$A$" & S))
        .Name = "boo"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Alaa = Alaa + 100
next

 


Comment by: Jan Karel Pieterse (6/8/2012 9:59:46 PM)

Hi,

Change this:

"URL;http://miami.craigslist.org/boo/index[""Alaa""].html"

To:

"URL;http://miami.craigslist.org/boo/index" & 100 * p & ".html"

 


Comment by: Al (6/9/2012 12:38:35 PM)

Thank you very much it worked perfectly.

 


Comment by: Al (6/11/2012 3:48:51 PM)

Hello - I need another help please. After importing the data from the web, I need to extract one word from each row. More specifically I only need the price listed. For example, if we have:

"1957 Glaspar Avalon 15' - $2300 (ft. laud, fla.) " I need a code in VBA which extracts "$2300" only and paste it in the adjacent cell.

I tried the method of "Text to Columns" in Excel, but it did not work as needed. For some reason, delimiters are splitting words in two sometimes.

Thank you for your cooperation.

 


Comment by: Jan Karel Pieterse (6/11/2012 6:30:47 PM)

Hi Al,

If your text is in cell A1 and the number always is preceded by " - $" and followed by a space character, then this formula extracts the price:

=MID(A1,FIND(" - $",A1)+4,FIND(" ",MID(A1,FIND(" - $",A1)+4,LEN(A1))))

 


Comment by: Al (6/11/2012 7:25:33 PM)

Hi,

What if the price can range from $1,000 to say $100,000, can this formula capture this ?

Thank You,

 


Comment by: Jan Karel Pieterse (6/12/2012 9:08:17 AM)

Hi Al,

Yes, as long as the text before the price is "- $" and there is a space after the price.

 


Comment by: V S S SARMA (7/7/2012 9:45:42 PM)

Web query:

When data to be imported is in sheet, it is possible to import the web data into an excel sheet. What happens when the data is available in many sheets and we need to take the same in one sheet ? How do we do it ?

EXAMPLE: Please see the following data pertaining to cricket, stretched over 55 pages in the web.

ODI Batting

Page1

http://stats.espncricinfo.com/ci/engine/stats/index.html?class=2;filter=advanced;orderby=start;size=200;spanmax1=30+Jun+2012;spanmin1=01+Jan+2009;spanval1=span;template=results;type=batting;view=innings;wrappertype=print

Page 2

http://stats.espncricinfo.com/ci/engine/stats/index.html?class=2;filter=advanced;orderby=start;page=2;size=200;spanmax1=30+Jun+2012;spanmin1=01+Jan+2009;spanval1=span;template=results;type=batting;view=innings;wrappertype=print

etc. etc. till page 55.

 


Comment by: Kevin (7/12/2012 8:56:15 PM)

Hello,

I was wondering if anyone had used the webquery on websites with office formatting such as x:fmla="A1*1.09" which is used to store formulas. The webquery uses the formula given in this format instead of the value in the table cell. Is there any way to override that effect?

Thanks

 


Comment by: rajesh (7/31/2012 1:40:23 PM)

i'm using web query to take XML data .i'm using this process for my operation .but when i'm refresh real time data then my excel is also fluctuate .my real time data into excel is coming but issue is excel fluctuation so i need your help

 


Comment by: Miguel (8/3/2012 12:47:27 PM)

Hi,

The following url, provide wheather information in different latitude and longitude, (in this case 34 and 56) I trying to link the data obtained in different places in a spreadsheet where I previusly have set up ecuations but it does not work, can I enter 2 parameters in my query?

Thanks

http://eosweb.larc.nasa.gov/cgi-bin/sse/retscreen.cgi?email=rets@nrcan.gc.ca&step=1&lat=34&lon=56&submit=Submit

 


Comment by: Jan Karel Pieterse (8/7/2012 11:11:38 AM)

@Rajesh: I am not sure what you mean?

@Miguel: You should be able to set up parameters for both latitude and longitude, following these steps:
- Set up a webquery to a fixed lat and long
- Click in your data
- open the VBA editor (alt+F11)
- Hit control+G
- write this into the immediate window and hit enter (all on one single line of text!):
ActiveCell.QueryTable.Connection="URL;http://eosweb.larc.nasa.gov/cgi-bin/sse/retscreen.cgi?email=rets@nrcan.gc.ca&step=1&lat=[""Lat""]&lon=[""Long""]&submit=Submit"
After that, go back to Excel, click the Data, Connections, Connections button. Select the connection and click the Properties tab. Now you should see a parameters button available. Tie the parameters to cells with approproate values.

 


Comment by: Jan Karel Pieterse (8/7/2012 11:37:00 AM)

Hi Kevin,

I've never seen that construct before on websites, can you show a sample URL?

 


Comment by: Jan Karel Pieterse (8/7/2012 11:44:03 AM)

@V S S SARMA: Yes it can be done, but it involves writing a bit of VBA code that changes the URL of the connection, refreshes the webquery and then copies the result to a different worksheet.

 


Comment by: peter (11/6/2012 1:07:15 AM)

i have create an automation of this which works fine and downloads the data i require however some time it gets stuck and the only way back is to end excel which means i loose all that has be downloaded in that session is there a way to make it time out and just stop running so ican save the data

 


Comment by: Jan Karel Pieterse (11/8/2012 2:00:04 PM)

Hi Peter,

Depends on your code I guess. Can you post the relevant piece of your macro?

 


Comment by: kumar (12/10/2012 3:52:19 PM)

HI,
I had emp id in my excel sheet and when i enter this emp id in a wesite and click submit.Website retrives daa of that employee like name ,first name,DOB ,Dept No etc.IN my excel i had minimum of 10000 emp id and for every id i need to copy respective details of that emp from website to my excel .Can you help me in doing this.As i am getting daily 10000 emp id.i need to update excel daily with this emp details.it is talking alot of time from to do this.Could you help me in this.Thanks in advance

 


Comment by: Jan Karel Pieterse (12/10/2012 4:25:31 PM)

Hi Kumar,

I can help you with this, but not for free. If you are interested to start a project, please contact me on the email address listed below.

 


Comment by: Arpan Pitroda (12/29/2012 1:45:42 PM)

I've created a web query in excel vba. Now the problem is - I've created a procedure which creates a web query and there is a "Refresh" statement after it which gets data from web. Now the problem is there are other statements after that query and refresh statements. And before the data comes from web the statements after "refresh" start executing. And as the later statements uses web data which they don't found (as the query is running in background), they generate error. So can you suggest anything which can stop the later statements untill the web data comes. (Note: It is necessary to use both web query and later statements in one procedure.) Please mail me back the sollution if possible. Thank you.

 


Comment by: Jan Karel Pieterse (12/29/2012 4:52:18 PM)

Hi Arpan,

The Refresh method has an argument which you need to set to False.

 


Comment by: roger (4/30/2013 12:07:46 AM)

When I create a new excel app in vb, then add a web query, it runs fine, but when I quit the excel app in vb, it closes but does not unload from memory (still shows in task manager). I have set the excel workbook and app to nothing and unloaded the form they were called on with no luck.

If i load a worksheet that already has a web query on it, I can run that web query within vb 6 and when I then quit excel it unloads fine.

Any suggestion as to how to get an excel app that I create in vb and then use to then unload from memory upon quit of the app?

 


Comment by: Jan Karel Pieterse (5/1/2013 1:43:45 PM)

Hi Roger,

This depends on your VB code I'm afraid, hard to tell without knowing what is in your project.

One thing to look for is whether you have set all relevant object variables to nothing before trying to close Excel.

 


Comment by: James (8/24/2013 10:30:58 AM)

Hi Jan,

I am a novice in excel, and tried to perform some of the activity but failed.

Using web query I was able to extract data in column. In B1 parallely I have formula which extracts particular work from A1,A2 append to my calculation in B1, B2.

Problem is page gets refresh every 60 seconds, and column A1 gets updated, but B1 formula calculation happens for cells only when I close the excel sheet and reopen one.

I mean formula trigger in column B cells are not happening even though column A is getting refreshed every 60 secs. B cells update only when I open the sheet.

Could you please help ?

Thanks,
James

 


Comment by: Jan Karel Pieterse (8/26/2013 9:23:23 AM)

Hi James,

What formulas are in col B?

 


Comment by: raj (8/30/2014 7:56:25 PM)

Ja,

how to enable javascript within a webquery ?

The webpage i am downloading into a worksheet (using VBA webquery) has an javascript option named "previous". In the browser on clicking an hyperlink named "previous" the javascript (background) opens up new webpage.However the url remains the same.

Raj

 


Comment by: Jan Karel Pieterse (8/31/2014 8:25:32 PM)

Hi Raj,

I'm afraid that is not possible in a webquery.

 


Comment by: David Rogers (6/14/2015 12:14:52 AM)

I am trying to do web query for the forex & Bonds table in www.finviz.com (at bottom right corner of page). I get "This web query returned no data. To change the query, click OK, click the arrow on the name box in the formula bar, click the name of the external data range for the web query, right-click the selection and then click Edit Query". When I do that - I get the same error message. I also tried adding the BypassSSLNoCacheCheck using (running) RegEdit - which did nothing.

 


Comment by: Jan Karel Pieterse (6/15/2015 4:24:05 PM)

Hi David,

I'd suggest to try if PowerQuery can do this.
Webqueries have restrictions and you have bumped into one of them I'm afraid.

 


Comment by: Kingman (1/1/2016 2:22:28 PM)

Could you kindly advise how to create a web query with the below URL address using a parameter?

https://www.hkex.com.hk/chi/stat/smstat/dayquot/d151231c.htm

 


Comment by: Jan Karel Pieterse (1/4/2016 10:39:26 AM)

Hi Kingman,

I'm afraid this cannot be done using a parameter query. However, with a little bit of VBA this is easy enough:

Sub UpdateWebQuery()
    ActiveCell.QueryTable.Connection = "URL;https://www.hkex.com.hk/chi/stat/smstat/dayquot/d" & Format(Date,"yymmdd") & "c.htm
    ActiveCell.QuerTable.Refresh
End Sub

 


Comment by: Craig Spencer (4/6/2016 10:50:41 PM)

I have a macro that utilized an iqy with one parameter, the macro creates a new tab for many possible lines on the main source data, it brings in the data perfectly and will then concatenate some of the fields back to the main tab... however I have a tab created with the name from the main sheet and it puts that value in cell A1. So I get prompt to enter the parameter each time it creates the new tab to pull the data from the web query. I just click on cell A1 and it all works fine, but it would be awesome if I could set the named parameter to cell A1 in each tab as it cycles through the create/paste web contents into each spreadsheet.

I tried to add the .SetParam xlRange, ActSht (which I had set as ActiveSheet previously).Range("A1"), I tried it with and without the "with" statement, just including it in the query definitions but with no luck.

Do I need to assign a parameter name with the .SetParam command?

 


Comment by: chinesecheung (8/21/2016 3:49:22 PM)

Jan,

I cut and pasted the followings to Excel VBA and run it and I received error message 400. Do I miss anything?

Tks
Chinese Cheung
=======================================
Comment by: Jan Karel Pieterse (1/4/2016 10:39:26 AM)
Hi Kingman,

I'm afraid this cannot be done using a parameter query. However, with a little bit of VBA this is easy enough:


Sub UpdateWebQuery()
    ActiveCell.QueryTable.Connection = "URL;https://www.hkex.com.hk/chi/stat/smstat/dayquot/d" & Format(Date,"yymmdd") & "c.htm
    ActiveCell.QuerTable.Refresh
End Sub

 


Comment by: Jan Karel Pieterse (8/21/2016 7:11:28 PM)

Hi chinesecheung,

Do you receive a VBA runtime error or an error by Excel, stating "unable to open https://....."?

Note that the cod eyou posted contains a typo (QuerTable instead of QueryTable)

 


Comment by: BIBEK (9/2/2016 11:38:03 AM)

i have all data to be used as paremeter in column a, for each data i have to run web query everytime specifying the paremaeter as a1,a2,a3... or there is some alternate convenient method.

 


Comment by: Jan Karel Pieterse (9/5/2016 8:03:27 AM)

Hi Bibek,

I would set this up as follows:

- Set up one sheet which has the parameterized webquery in place as demonstrated on this web page.
- Have a macro that copies the cells A1, A2, ... in turn to the parameter cell on the sheet with the webquery
- Wait for the webquery to refresh and then copy the results of the webquery to another worksheet.

 


Comment by: chinesecheung (9/8/2016 1:46:14 PM)

Hi Jan,

I corrected the typo and still I received Run-time error 11004'; Application-defined or object-defined error

 


Comment by: Jan Karel Pieterse (9/8/2016 3:18:06 PM)

Hi chinesecheung,

Make sure the selected cell is within the range of the querytable before running my code.

 


Comment by: Richard (12/10/2016 2:58:05 PM)

When I start a web query I always get bing.com at first, with all the adds, pictures etc that slow down my computer. How can I set the homepage in excel to Blank?
Thank you for your help

 


Comment by: Jan Karel Pieterse (12/10/2016 7:26:13 PM)

It probably uses what Internet Explorer has set as the start page.

 


Comment by: Mimi (1/20/2017 9:21:19 AM)

I have an existing Web Query. I'm using Excel 2016. I need to modify the Connection string but it's grayed out. I find it by going to DATA, Connections, Properties, Definition Tab. Please help.
Thank you

 


Comment by: Jan Karel Pieterse (1/20/2017 11:50:51 AM)

Hi Mimi,

Very odd. Which file format is the file in?

 


Comment by: Mimi (1/20/2017 4:48:33 PM)

Excel Workbook .XLSX

 


Comment by: Jan Karel Pieterse (1/20/2017 5:02:04 PM)

Hi Mimi,

Are you not able to edit the query itself?
Otherwise, if you select a cell in the QT, perhaps you can access the connection string using the VBE immidiate window?
Alt+F11, control+G, type this code:

?ActiveCell.QueryTable.Connection

and press enter. To update, enter this:

activecell.QueryTable.Connection="URL;YourURLGoesHere"

and hit enter again.

 


Comment by: saad (6/21/2017 3:30:55 PM)

Hi Jan,

I have to pass the % sign as a parameter but it ends up appending the number 25 to it in the actual url when i run the query. is there any way to avoid this?

Thanks,
Saad.

 


Comment by: Jan Karel Pieterse (6/21/2017 3:55:33 PM)

Hi Saad,

You must "escape" (urlencode) the percent sign, which effectively means you have to type %25 for each % sign you need in the URL.

 


Comment by: saad (6/22/2017 1:57:31 PM)

Hi
so everytime i put the percent sign in as parameter it appends 25.
so even when i try to escape it with %25 it just ends up puttin "%2525"
in its place.

 


Comment by: Jan Karel Pieterse (6/22/2017 1:58:27 PM)

Hi Mohammad,

Yes it does, but the browser will interpret the %25 as % and leave the second 25 as 25.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].