Using Parameters With Web Queries

Comments

All comments about this page:

Comment by: Jan Karel Pieterse (8-3-2006 11:52:02) deeplink to this comment

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 (10-3-2006 22:58:55) deeplink to this comment

Thanks for the info

Comment by: Willem (14-4-2006 06:34:43) deeplink to this comment

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 (15-4-2006 09:51:50) deeplink to this comment

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 (25-4-2006 19:57:24) deeplink to this comment

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 (25-4-2006 22:35:04) deeplink to this comment

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 (29-5-2006 16:10:43) deeplink to this comment

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 (29-5-2006 21:18:54) deeplink to this comment

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 (30-5-2006 07:58:22) deeplink to this comment

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 (30-5-2006 08:26:25) deeplink to this comment

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 (30-5-2006 11:31:57) deeplink to this comment

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 (12-8-2006 22:12:21) deeplink to this comment

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 (14-8-2006 01:24:30) deeplink to this comment

Hi Jason,

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

Comment by: Sudhee (19-11-2006 22:27:07) deeplink to this comment

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 (19-11-2006 22:42:06) deeplink to this comment

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 (16-12-2006 10:40:54) deeplink to this comment

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 (17-12-2006 03:19:57) deeplink to this comment

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 (19-12-2006 06:48:25) deeplink to this comment

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 (19-12-2006 08:22:07) deeplink to this comment

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 (26-1-2007 09:35:58) deeplink to this comment

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 (5-4-2007 03:20:04) deeplink to this comment

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 (5-4-2007 04:49:10) deeplink to this comment

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 (18-5-2007 10:11:51) deeplink to this comment

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 (18-5-2007 11:05:25) deeplink to this comment

Hi Ralph,

Maybe my Flexfind can do that for you?

Comment by: Lialydayhah (22-5-2007 17:34:56) deeplink to this comment

Hi

Greetings!!!Cool page!Cool work!
Bye

Comment by: Ralph W Lundvall (23-5-2007 10:30:07) deeplink to this comment

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

Comment by: Jordan OBrien (1-6-2007 12:17:14) deeplink to this comment

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 (28-6-2007 08:45:56) deeplink to this comment

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

Comment by: fish_hfd (30-6-2007 13:07:53) deeplink to this comment

It's nice

Comment by: Antique (12-7-2007 13:47:56) deeplink to this comment

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 (17-8-2007 20:06:29) deeplink to this comment

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 (20-8-2007 03:58:11) deeplink to this comment

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 (20-8-2007 07:00:34) deeplink to this comment

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 (20-8-2007 07:04:22) deeplink to this comment

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 (20-8-2007 09:43:50) deeplink to this comment

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

Comment by: Jan Karel Pieterse (20-8-2007 21:53:45) deeplink to this comment

Hi manthano,

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

Comment by: manthano (21-8-2007 18:07:43) deeplink to this comment

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 (22-8-2007 11:20:31) deeplink to this comment

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 (11-9-2007 09:58:04) deeplink to this comment

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 (11-9-2007 10:08:24) deeplink to this comment

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 (25-9-2007 19:28:55) deeplink to this comment

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 (3-12-2007 09:34:40) deeplink to this comment

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

Comment by: Pete (11-12-2007 13:44:02) deeplink to this comment

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 (11-12-2007 22:22:45) deeplink to this comment

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 (19-12-2007 13:02:57) deeplink to this comment

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 (20-12-2007 11:00:04) deeplink to this comment

Hi Pete,

You did not include the url?

Comment by: Mike Carroll (2-1-2008 15:27:22) deeplink to this comment

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 (18-1-2008 09:29:01) deeplink to this comment

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 (18-1-2008 10:04:02) deeplink to this comment

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 (18-1-2008 11:23:43) deeplink to this comment

Thank you very much!

It worked perfectly.

Comment by: Juan (5-3-2008 12:32:06) deeplink to this comment

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 (9-4-2008 03:31:22) deeplink to this comment

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 (29-4-2008 11:00:55) deeplink to this comment

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 (4-5-2008 11:59:59) deeplink to this comment

Hi Don,

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

Comment by: Noam (5-8-2008 14:20:41) deeplink to this comment

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 (6-8-2008 10:52:27) deeplink to this comment

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 (7-8-2008 00:14:47) deeplink to this comment

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 (7-8-2008 04:06:28) deeplink to this comment

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 (15-8-2008 13:26:14) deeplink to this comment

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 (17-8-2008 22:03:14) deeplink to this comment

Hi Dennis,

Certainly:

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

Comment by: Dennis ODonovan (18-8-2008 11:57:20) deeplink to this comment

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 (5-9-2008 08:50:45) deeplink to this comment

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 (8-9-2008 00:58:33) deeplink to this comment

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 (24-10-2008 07:02:08) deeplink to this comment

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 (24-10-2008 07:43:49) deeplink to this comment

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 (22-12-2008 10:53:51) deeplink to this comment

Any best tutorial for webquery- advanced level. Thanks

Comment by: joemerieux@tiscali.co.uk (29-7-2009 09:50:03) deeplink to this comment

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 (29-7-2009 10:11:07) deeplink to this comment

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 (27-10-2009 14:16:10) deeplink to this comment

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 (30-10-2009 11:51:15) deeplink to this comment

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 (8-11-2009 15:02:23) deeplink to this comment

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 (8-11-2009 22:19:46) deeplink to this comment

Hi Jill,

Can you post an example URL?

Comment by: JILL (9-11-2009 02:44:53) deeplink to this comment

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 03:07:06) deeplink to this comment

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 (13-11-2009 23:26:11) deeplink to this comment

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 (15-11-2009 22:19:22) deeplink to this comment

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):

Comment by: james sloan (18-12-2009 12:46:14) deeplink to this comment

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 (19-12-2009 11:42:41) deeplink to this comment

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 (30-12-2009 07:36:32) deeplink to this comment

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 (31-12-2009 01:38:30) deeplink to this comment

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 (31-12-2009 04:13:00) deeplink to this comment

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:

Comment by: Jan Karel Pieterse (31-12-2009 04:14:06) deeplink to this comment

Hi Senthamizh,

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

Comment by: kevin (23-2-2010 13:48:01) deeplink to this comment

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 (23-2-2010 23:07:05) deeplink to this comment

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 (5-4-2010 14:16:40) deeplink to this comment

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 (5-4-2010 22:47:08) deeplink to this comment

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 (12-5-2010 08:40:19) deeplink to this comment

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...

Comment by: Jan Karel Pieterse (14-5-2010 06:10:26) deeplink to this comment

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 (14-5-2010 13:18:36) deeplink to this comment

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 (15-5-2010 10:39:37) deeplink to this comment

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 (11-6-2010 07:31:23) deeplink to this comment

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 (11-6-2010 10:19:28) deeplink to this comment

Hi Jet,

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

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

Comment by: Lou (25-6-2010 13:49:12) deeplink to this comment

Hi,

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

Thanks.

Lou

Comment by: Jan Karel Pieterse (26-6-2010 06:50:58) deeplink to this comment

Hi Lou,

Unfortunately, no.

Comment by: mastor (16-7-2010 05:32:32) deeplink to this comment

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 (16-7-2010 06:33:41) deeplink to this comment

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.

Comment by: ryan (16-7-2010 11:27:12) deeplink to this comment

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 (17-7-2010 02:32:28) deeplink to this comment

Hi Ryan,

Why not record a macro whilst setting this up?

Comment by: JC (19-7-2010 16:54:08) deeplink to this comment

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 'https://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 (16-8-2010 02:53:22) deeplink to this comment

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 (15-9-2010 02:51:53) deeplink to this comment

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 (15-9-2010 05:29:54) deeplink to this comment

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 (30-9-2010 01:34:44) deeplink to this comment

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 (30-9-2010 02:05:23) deeplink to this comment

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 (30-9-2010 02:16:32) deeplink to this comment

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 (30-9-2010 05:47:39) deeplink to this comment

Hi Luca,

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

Comment by: Luca (30-9-2010 07:04:38) deeplink to this comment

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 (30-9-2010 07:05:53) deeplink to this comment

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 (7-10-2010 00:14:45) deeplink to this comment

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 (7-10-2010 02:12:08) deeplink to this comment

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 (22-10-2010 23:20:47) deeplink to this comment

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 (23-10-2010 02:13:48) deeplink to this comment

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 (23-10-2010 15:20:53) deeplink to this comment

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

Comment by: Jan Karel Pieterse (24-10-2010 03:17:03) deeplink to this comment

Hi Geoff,

This seems to do the trick for me:

Comment by: Geoff (24-10-2010 09:20:20) deeplink to this comment

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 (24-10-2010 10:13:19) deeplink to this comment

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:

Comment by: Jan Karel Pieterse (24-10-2010 10:16:31) deeplink to this comment

Hi Geoff,

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

Comment by: Sally (6-12-2010 19:33:07) deeplink to this comment

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 (7-12-2010 00:47:04) deeplink to this comment

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 (4-2-2011 03:02:17) deeplink to this comment

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 (4-2-2011 03:12:33) deeplink to this comment

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 (4-2-2011 03:48:33) deeplink to this comment

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 (4-2-2011 03:52:04) deeplink to this comment

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 (4-2-2011 05:49:28) deeplink to this comment

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 (4-2-2011 05:59:12) deeplink to this comment

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 (4-2-2011 06:21:49) deeplink to this comment

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 (4-2-2011 06:44:14) deeplink to this comment

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 (4-2-2011 07:14:06) deeplink to this comment

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 (5-2-2011 22:52:34) deeplink to this comment

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 (6-2-2011 22:49:47) deeplink to this comment

Hi Imran,

Did you try the steps provided in this article?

Comment by: IMRAN NAQVI (8-2-2011 22:31:29) deeplink to this comment

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

Comment by: Jan Karel Pieterse (8-2-2011 23:41:11) deeplink to this comment

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:

Comment by: IMRAN NAQVI (9-2-2011 00:20:53) deeplink to this comment

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 (9-2-2011 00:25:56) deeplink to this comment

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 (9-2-2011 02:52:58) deeplink to this comment

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 (9-2-2011 03:23:58) deeplink to this comment

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 (9-2-2011 04:36:42) deeplink to this comment

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 (9-2-2011 21:46:56) deeplink to this comment

Hi Jan,

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

Comment by: Jan Karel Pieterse (10-2-2011 05:31:12) deeplink to this comment

Hi Imran,

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

Comment by: Rudy Gobin (31-3-2011 10:30:31) deeplink to this comment

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 (31-3-2011 14:41:25) deeplink to this comment

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

Comment by: Rudy Gobin (1-4-2011 07:30:34) deeplink to this comment

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 (1-4-2011 10:19:48) deeplink to this comment

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 (1-4-2011 11:28:33) deeplink to this comment

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 (1-4-2011 12:33:14) deeplink to this comment

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 (3-4-2011 05:11:08) deeplink to this comment

Hi Rudy,

WHat way did you use to fix the problem?

Comment by: Rudy Gobin (3-4-2011 20:22:41) deeplink to this comment

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 (3-4-2011 21:45:26) deeplink to this comment

Hi Rudy,

Thanks for letting us know!

Comment by: chella pandian (20-4-2011 01:51:59) deeplink to this comment

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 (20-4-2011 21:49:31) deeplink to this comment

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 (13-5-2011 13:09:24) deeplink to this comment

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 (27-6-2011 05:09:49) deeplink to this comment

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

Comment by: Jan Karel Pieterse (27-6-2011 23:16:33) deeplink to this comment

Hi Mustafa,

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

Comment by: Yashik (18-7-2011 00:25:46) deeplink to this comment

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 (18-7-2011 02:23:18) deeplink to this comment

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 (18-7-2011 16:13:25) deeplink to this comment

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 (19-7-2011 01:37:03) deeplink to this comment

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 (4-8-2011 11:37:49) deeplink to this comment

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 (12-8-2011 09:56:52) deeplink to this comment

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 (22-8-2011 02:15:59) deeplink to this comment

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 (22-8-2011 02:50:31) deeplink to this comment

Hi namenotshown,

Sure, just use the url as given.

Comment by: Sunit Gadhia (30-8-2011 03:59:55) deeplink to this comment

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 (30-8-2011 07:48:55) deeplink to this comment

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 (2-9-2011 09:27:17) deeplink to this comment

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 (4-9-2011 22:57:53) deeplink to this comment

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 (13-9-2011 14:57:51) deeplink to this comment

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 (13-9-2011 23:12:22) deeplink to this comment

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 (14-9-2011 07:39:17) deeplink to this comment

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 (14-9-2011 08:00:09) deeplink to this comment

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:

Comment by: Roman (23-9-2011 12:39:53) deeplink to this comment

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 (23-9-2011 23:10:21) deeplink to this comment

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 (25-10-2011 07:07:23) deeplink to this comment

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):

Comment by: Jan Karel Pieterse (26-10-2011 02:57:11) deeplink to this comment

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 (18-11-2011 21:57:07) deeplink to this comment

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 (23-11-2011 09:36:38) deeplink to this comment

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 (23-11-2011 22:13:49) deeplink to this comment

Hi Manju,

Isn't that exactly what this page is about?

Comment by: Erica (30-11-2011 10:09:26) deeplink to this comment

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 (30-11-2011 23:05:14) deeplink to this comment

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 (7-12-2011 09:51:41) deeplink to this comment

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

Comment by: Jan Karel Pieterse (8-12-2011 01:11:34) deeplink to this comment

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 08:24:28) deeplink to this comment

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 08:30:12) deeplink to this comment

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 08:49:42) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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

Comment by: Felipe (4-1-2012 13:11:48) deeplink to this comment

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 (4-1-2012 23:32:54) deeplink to this comment

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 (31-1-2012 07:45:57) deeplink to this comment

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 (1-2-2012 22:44:04) deeplink to this comment

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 (9-2-2012 07:33:40) deeplink to this comment

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 (9-2-2012 07:54:51) deeplink to this comment

Hi Bernard,

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

Comment by: Robert (12-2-2012 17:10:35) deeplink to this comment

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 (12-2-2012 23:05:14) deeplink to this comment

Hi Robert,

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

Comment by: Michael Harris (17-2-2012 09:33:10) deeplink to this comment

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 (20-2-2012 07:44:37) deeplink to this comment

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 (4-3-2012 15:17:11) deeplink to this comment

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 (5-3-2012 05:03:25) deeplink to this comment

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 (29-5-2012 19:17:12) deeplink to this comment

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 (30-5-2012 07:02:29) deeplink to this comment

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 (30-5-2012 07:03:24) deeplink to this comment

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 (8-6-2012 14:31:36) deeplink to this comment

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 (8-6-2012 14:43:15) deeplink to this comment

Hi Al,

Please post the relevant portion of your code.

Comment by: Al (8-6-2012 15:05:53) deeplink to this comment

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 (8-6-2012 21:59:46) deeplink to this comment

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 (9-6-2012 12:38:35) deeplink to this comment

Thank you very much it worked perfectly.

Comment by: Al (11-6-2012 15:48:51) deeplink to this comment

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 (11-6-2012 18:30:47) deeplink to this comment

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 (11-6-2012 19:25:33) deeplink to this comment

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 (12-6-2012 09:08:17) deeplink to this comment

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 21:45:42) deeplink to this comment

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 (12-7-2012 20:56:15) deeplink to this comment

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 (31-7-2012 13:40:23) deeplink to this comment

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 (3-8-2012 12:47:27) deeplink to this comment

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 (7-8-2012 11:11:38) deeplink to this comment

@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 (7-8-2012 11:37:00) deeplink to this comment

Hi Kevin,

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

Comment by: Jan Karel Pieterse (7-8-2012 11:44:03) deeplink to this comment

@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 (6-11-2012 01:07:15) deeplink to this comment

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 (8-11-2012 14:00:04) deeplink to this comment

Hi Peter,

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

Comment by: kumar (10-12-2012 15:52:19) deeplink to this comment

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 (10-12-2012 16:25:31) deeplink to this comment

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 (29-12-2012 13:45:42) deeplink to this comment

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 (29-12-2012 16:52:18) deeplink to this comment

Hi Arpan,

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

Comment by: roger (30-4-2013 00:07:46) deeplink to this comment

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 (1-5-2013 13:43:45) deeplink to this comment

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 (24-8-2013 10:30:58) deeplink to this comment

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 (26-8-2013 09:23:23) deeplink to this comment

Hi James,

What formulas are in col B?

Comment by: raj (30-8-2014 19:56:25) deeplink to this comment

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 (31-8-2014 20:25:32) deeplink to this comment

Hi Raj,

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

Comment by: David Rogers (14-6-2015 00:14:52) deeplink to this comment

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 (15-6-2015 16:24:05) deeplink to this comment

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 14:22:28) deeplink to this comment

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 (4-1-2016 10:39:26) deeplink to this comment

Hi Kingman,

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

Comment by: Craig Spencer (6-4-2016 22:50:41) deeplink to this comment

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 (21-8-2016 15:49:22) deeplink to this comment

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 (21-8-2016 19:11:28) deeplink to this comment

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 (2-9-2016 11:38:03) deeplink to this comment

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 (5-9-2016 08:03:27) deeplink to this comment

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 (8-9-2016 13:46:14) deeplink to this comment

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 (8-9-2016 15:18:06) deeplink to this comment

Hi chinesecheung,

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

Comment by: Richard (10-12-2016 14:58:05) deeplink to this comment

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 (10-12-2016 19:26:13) deeplink to this comment

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

Comment by: Mimi (20-1-2017 09:21:19) deeplink to this comment

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 (20-1-2017 11:50:51) deeplink to this comment

Hi Mimi,

Very odd. Which file format is the file in?

Comment by: Mimi (20-1-2017 16:48:33) deeplink to this comment

Excel Workbook .XLSX

Comment by: Jan Karel Pieterse (20-1-2017 17:02:04) deeplink to this comment

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 (21-6-2017 15:30:55) deeplink to this comment

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 (21-6-2017 15:55:33) deeplink to this comment

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 (22-6-2017 13:57:31) deeplink to this comment

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 (22-6-2017 13:58:27) deeplink to this comment

Hi Mohammad,

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

Comment by: jim (17-3-2020 07:27:00) deeplink to this comment

hi,
how can I execute a web query with a vbscript ?

thanks in advance

Comment by: Jan Karel Pieterse (17-3-2020 14:20:00) deeplink to this comment

Hi Jim,

Depends on the type of Web Query, an old-fashioned one:
If the activecell is part of the querytable:

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.