Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA: Excel Web Query Problems

My firm uses about 60 different web sites to retrieve input data for a price foreacst. We use Excel to call the website, and return data. Before calling a website, we use On Error Resume Next. We hoped that if a web query timed out, it would step over that and move on to the next Call. It doesn't work that way. Excel passes the query command to a different ap, if that target website is down, or the data is not available, the query simply hangs there forever. Excel does not know that an error occured, and so never calls the Resume Next.

Is there a way out of this problem? Like perhaps a timer that is called prior to calling the webquery. If the time exceeds "X" seconds, it Resumes Next.

Thanks,

Mike


Post your answer or comment

comments powered by Disqus
Just registered to deal with this particular problem which I suspect will be shared by other Visual Basic developers. I saw the problem discussed on another forum, without a solution. I have only scanned the first several 'pages' of this forum so may be re-entering a problem discussed further in the past - if so, sorry.

I have developed an extensive application using excel and access data bases to manage investment (stock) data. My XP system (WAS planning to 'upgrade' to Vista) uses the VBA/excel web query function to extract data from selected web sites. In doing so the Office Apps use the installed version of Internet Explorer to conduct the web operations.

The connection URL typically will involve a complex scripted string:

http://finance.yahoo.com/q/hp?s=MSFT

this is a simple case that extracts historical prices (hp) for Microsoft (s= MSFT) from the Yahoo Finance web site. Note the embedded "?".

If one enters this URL in any browser (including IE7), it obtains the requested web page. However, when the IE7 browser operates 'under the covers' in an office application, it blocks importation of the web page into the application.

Backing up: With IE6 as the default browser one can, within an excel worksheet, query the above web page with URL. Enter which brings up the web page in the imbedded IE6 browser. downloads the page to the worksheet.

After dutifully updating to IE7 this is no longer possible. will find the web page (just like it will with the manual browser entry); but will create an error messaged to the effect that the system cannot access the FILE; giving possible reasons of Read Only (not true), not in the system (Not True); and having invalid characters in the file name - citing amongst other "?". IE7 is apparently treating a web query under the same rules as local disc access.

Conducting the same test under IE7 using an URL without the "?" such as: http://finance.yahoo.com successfully imports. In fact, in my application, other web pages with very complex scripts albeit sans "?" import fine.

The error message makes it look like IE7 has introduced some 'Filename Nazi' that blocks valid use of an URL script with embedded "?" (other other characters of which I'm not aware). I surmise that the IE7 code developers, in their insulated manually operated browser environment (read IE7 for non-commerical applications) never considered or tested for Visual Basic operated Office applications that use IE7 'under the covers'.

Anyway, I had a 'crashed' XP system 'upgraded' with IE7. I did a system restore pre-IE7 and now MY system works again. However, as a commercial developer I'm screwed. My years of work (literally) cannot be run on new Vista (sold with IE7) or XP (upgraded to IE7) systems.

I know that there are many developers out there that use Office for data mining the web. I'm dismayed by the MSFT marketing decison to abandon those developers.

My questions:

1. Is my IE7 defect analysis correct -e.g. it is blocking web queries with its list of excluded characters intended for File Names which may or may not be applicable to web query URLs?

2. Is there a VBA work around that I can envoke in my Visual Basic Code that will shut down the MSFT FileName Nazi agent. Note: I've tried some 'On Error Resume Next' and similar strategies in VBA without success. Just for reference the error occurs at the 'Refresh' command:

With rYahooQ.QueryTable
.Connection = "URL;http://finance.yahoo.com/q/hp?s=MSFT"
.Refresh BackgroundQuery:=False
End With

where the range, "rYahooQ" has been set to a range with a previously initiated valid worksheet query. (works fine under IE6, not IE7)

3. Last resort: is there some IE7 patch?*

4. Last Last resort: Is there some way to install a stable browser (non-IE) into excel/access/VBA - one that won't be inadvertently 'upgraded' to a disfunctional state by potential users of my application?*

* obviously if I have to ask a potential customer to muck with his MSFT OS/IE7 to make an Office/VBA application run, I've severly limited the class of people who might buy such an application - hence 'last resort'

Thanks for your time and consideration. Looking forward to some salvation!

Hi,

I have been working on and off for some time on a macro that opens up specific webpages from a predifined list of URLs and retrieves information. Lucky for me so far, the website that contains all the webpages has a specific structure that is standardized and so I can tell from a simple web query in Excel which cells contain the inofromation I need. As my understanding of vba has grown, has my appetite for perfection and so I have started to add more loops using nested If statements, case-specific scenarios, etc.

Two problems I have found now that I can't seem to tackle alone and seek advice and commentary:

1) The website needs a username/ password to be entered. When I use Internet Explorer (normal browsing), I give them once at the begining of the session and IE seems to remember that I am logged on and allows me to view the pages I need. Using Excel query, I can't seem to enter it automatically in the vba code to be automated, and even if I open it manually the first time then run the macro, it works fine for a while then after a few loops asks again for the username/ password. Can anyone guide me on how to add them into the vba code from the start?

2) I have no idea why, but my macro works fine for a few loops (~20) then goes off the reservation; even though I specifically added On Error Resume Next at the begining of the code so that it would finish and then I coudl examine the results of a full cycle. Excel 2010 stops working or intiating any web activity. I went back to a computer running Excel 2007, and it seems to be workign fine but slower. Can anyone direct me (examples would be great) to how to use Internet Explorer instead of the Excel web query function? I am assuming that EI is faster than the Excel web query. What about Firefox, etc.? Any experience?

Thanks.

abousetta

1. I use scripting to perform Excel Web Queries, often I get nicely formatted pages but which contain multiple hyperlinks I don't require. I found that you can issue worksheet.hyperlinks.delete and remove them, but it also removes all formatting, i.e. the ColorIndex - is there a way to remove hyperlinks and preserve the other formatting.

2. I have a persisted XML Recordset, which I open and issue RS.Filter = "a valid filter". If I then issue .CopyFromRecordset(RS) to move the data into Excel, the filter is ignored and all rows are copied. Why is this?

Thank you,

stan

Okay, I do not know if this can be done, but really need some good guidance on this one. So here goes

I have an excel web query that get some weather data. the address does not change so it always updates that data. But I need to use some data from another site. That requires you to select the fields . So the address is not always the same. Is there a way to overcome this. would i have to maybe edit the address to display the info i want. or someone suggest a dynamic web query I have no clue how to go about doing this. Any help would be greatly appreciated

Here is the web address I am trying to acces. Any guidance would be appreciated

http://www.arl.noaa.gov/ready-bin/me...te=--&cntry=UK

I am trying to setup a MS Excel web query based on the following URL:

http://finance.yahoo.com/currency/co...submit=Convert

This is the Yahoo currency converter web page

When I introduce a parameter for amount this works OK as follows:

http://finance.yahoo.com/currency/convert?amt=["amount"]&from=USD&to=JPY&submit=Convert

However when I introduce parameters for the from and the to currency this
does not work as follows:

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Can anyone tell me why? What am I doing wrong? Is there another way around
this?

The only thing I can possible think of is that the from and to currency on
the web site are drop down lists of values where the amount field is not.

Many thanks in anticipation

RobC

excel web queries: make the box for the website name much longer;
we need to be able to type in a webaddress that is long: the host name, the
cgi, the program name, password and userid, and a bunch of parameters for the
program. Excel says, when we try to edit our query, that the address is too
long to edit; please make it longer. i can save the .iqy and edit it that
way, but that's too hard for my end users. they need to be able to edit the
page name right in the box under Edit Query.
thanks.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

I am trying to work with excel web query(*.iqy) function to implement a automatic report fetching. But the requested URL will redirect to a login page, and after user/pass submitted, the data page will be shown.

I am not sure how to handle such situation in the iqy part. Is there any one can provide me any suggest?

Thx in advance.

Hello,

Can anyone tell me plz how to Bypass username/password prompt when executing an excel web query. I want other users to be able to run that from excel and I don't want to provide them my password or username. Thank you

Hello there.
I've been using the excel web query functionality at home and at the
office for a while now to retrieve stock quotes from yahoo finance.
recently however it stopped working on two of my 3 machines!
More specifically its the queries that require one to be logged in that
have stopped working.

I get an error message saying that "The Internet site reports that a
conenction was extablished but the Data is not available"

After reading the topics here and at other places on the web i tried:

1) Rolling back using system restore (to get rid of any updates that
couldve broken this)
2) Adding a .html in my query (ie finance.yahoo.com/p.html?v&k=pf_2)
3) Reinstalling MS Office

I am running Win XP SP2 and IE 6

All help is much appreciated!

Thanks!

I have a VBA script that goes to certain web pages and captures data
before I format it in Excel. All pages are the same style so the Web
Query just selects the same "selected box" on each page before it
copies the data into Excel. However, the web site has recently upgraded
the way it presents its data and our VBA script does not work...!

Basically, when the web page is generated the data section within the
web page itsellf takes a while before it appears on the screen, but
Excel only sees the data that first appears - not the data section
which I want. If you paste the following URL into your Web Query finder
you will see the Web Query icons (tick boxes) appear agiant some of the
boxes on the web page - but not the main data section.

http://www.chintai.net/tokyo/chintai...00000001000023

However, if you click on the "Hide Icons" button (in the top right
corner) and then click again a tick box will appear next to the data
section that I want! But, the Excel web query does not see this data
because it appears later than the rest of the page... If I can make the
Excel Web Query generate the icons (tick boxes) on the web page a bit
later I think the problem will be fixed because Excel will then be able
to see the data section which I want.

Can anyone help me make the icons (tick boxes) on the web page apper a
bit later, please???

I have a web query in VBA that is not working although it works in other
computers. The reason is that it does not access the Internet. The same
happens if I don't use VBA. After the usual Data/Import External Data/New
Web Query the window starts in
www.google.com and displays

"Action Canceled. Internet Explorer was unable to link to the Web page
you requested. The page might be temporarily unavailable."

If I enter

http://moneycentral.msn.com/detail/s...ote?Symbol=aig
or
http://moneycentral.msn.com/investor...asp?SYMBOL=IBM

I receive a nasty

"The page cannot be displayed. The page you are looking for is currently
unavailable. The Web site might be experiencing technical difficulties, or
you may need to adjust your browser settinngs."

However, the same query works in IE. Needless to say, I tried with other
sites, and the result is the same.

I'm using Windows XP, Excel 2003. My computer is part of a LAN, inside a SBC
Yahoo firewall. I also disabled both the Windows and the SBC Yahoo firewalls
and the results are the same: cannot connect.

Any suggestion? Do I have to change any settings? Any Office Control to
download or Office Web Components missing? How to add them?

I looked for the requirements/options/settings to make Web Query run in
Excel 2003 but couldn't find them on the Microsoft website (or anywhere
else).

Thanks guys,

CW

Hi,

I'm new here ... as writing a question ... but as founding a solution for my Excel issues, I was often a visitor - so thx for all previous help!

Now I have funny or scary issue with Web Query. I'm using VBA, but I found out that even trough Web Query dialog window I got same "error". I'm trying to get some data from Google Map - json stuff.

So I open a ... oh yes - Excel 2010 ... "get data from web" and in dialog box New Web Query under Address: paste this link ...

http://maps.googleapis.com/maps/api/...z&sensor=false

... if you click it, you'll see json structure of geo-location for some address (FireFox will open it, IE you need to select open if you didn't play with Win Registry). Same structure I see in dialog box ...

web_query_01.jpg

... but when I click Import I don't get same results ...

web_query_02.jpg

I've played with all options, but always same result. In most of cases this is working, but now I'm facing with this issue and I don't know what could be wrong.

Does anybody have any idea? Advice?

I would appreciate any kind of help ... thx, Marko

I work on an ancient NT machine at work that only has Excel 97 installed. Our administrator locks just about every function on our machine, so I am unable to create a web query using the wizard in Excel. To make matters easier, I created the correct query on another machine using Excel 2000, then saved it as an iqy file and ran it on my work machine in Excel 97. My query works in 2000, and only displays the tables on the website that I specify (i.e tables 2 and 6). This keeps the data consistent and brief, which is what I want since this encompasses a lot of data and formulas. However, when I run the query in 97, ALL of the tables on the webpage that I am getting data from appear. What a nightmare! It makes formulas useless, as some of the requested data may be in 8 rows and 15 columns while others are in 11 rows and 33 columns. And not only do I have inconsistent data entries, but I also have a page full of links to other sites, all of which are not necessary. Any of you know the VB codes to only extract data from certain tables in 97? I don't know if it is possible, as the web query tools are different in 97 and 2000 (or 2002/XP, etc).

I've having a problem with the text returned from a webquery from this web page:

http://ceti.astroempires.com/ranks.a...w=guilds_level

There's a lot of unicode extended characters and symbols, and in excel they all end up with a or or other incorrect characters inserted before them.

For example

[~~] becomes [~¥¥~]
[☆KOS☆] becomes [☆KOS☆]

This doesn't happen with a simple copy/paste from the website to excel. I've tried playing with every web query option and none of them have any effect, and there's nothing relevant to this problem that I can find with google. The source on the website uses the actual characters, not the hex codes btw.

Does anyone know how to make the web query not mangle these characters?

I am trying to do a web query on Excel 2004 on a Mac. In this verison we have to edit text files to do the query. My problem is that no matter what I do, I ge the full page, when I am trying to just get the tables.

Anyone worked with this critter?

Rich

Hi all,

Long winded title for this one!

Got another problem with my SQL Query after connecting from VBA Excel via ADO to MS Access. I'm hoping someone in here might have an incling...

.Open "SELECT
[Date], [Part No], [Batch Qty] FROM [" & TableName & "] " & _
            " WHERE [" & TableName & "].[Date] LIKE '" & sDate & "'", cn, , ,
adCmdText
sDate is declared as a date and the above statement performs as it should, but i'm getting an error 'Data Type Mismatch in Criteria Expression' when I change my statement from LIKE to =, example;

.Open "SELECT [Date], [Part
No], [Batch Qty] FROM [" & TableName & "] " & _
            " WHERE [" & TableName & "].[Date] = '" & sDate & "'", cn, , ,
adCmdText
I need to be able to perform a BETWEEN statement on the date and this does not work either producing the same error message. Anyone have any ideas why this is happening?

TIA....Any Ideas Appeciated!

Hello!

Wondering if anyone came across a similar problem:

About a week ago, my automated web query decided to stop working, giving me this message: "The internet site reports that a connection was established but the data is not available"

The query worked perfectly up until now, and I have not changed any Excel settings. (Stranger still, it seems to do this to only certain sites, including this one). I've rebuilt the query thinking that the website changed, tested, re-tested and still, same error!

To give a bit of a background, I need to pull weather forecasts from http://www.weather.com/weather/print/CAXX0504

I've scoured the net and seen similar messages - but seen no solutions...
I would be forever in gratitude for any pointers on this...

Update: I noticed something peculiar.. When I go to Internet Explorer and try to use the "Edit" icon... it is disabled for weather.com! If we look at say... theweathernetwork.com (another site i use) it is NOT. Is it possible the site itself may be disabling the automatic data pulls?

I have had a Excel web query accessing an asp web site & downloading data in Excel 2000 for a number of years with no problems. However, when replicating the same query in Excel 2003 & indicating which tables to download in the edit query wizard, I get a "strict parse error" error dialog box & no data is downloaded. Perhaps it caused by a security feature in Excel 2003. I have excel's macro security level set to low. If someone could assist in solving this problem it would be very much appreciated.

I have a problem with web query.
I create an excel sheet, retrieving web data. generally, it work well, but
sometimes, it take a longer time to complete the process.

I check the time:
1. generally, it take 5~10 sec.
2. sometimes, when internet or link is error, it will take 21~24 sec. (seem
like some timeout constant) and return an error.
3. But only some exceptional cases, the query waiting for several minute
(more than 5 min) to return back to excel with data.

In case 1 and 2, I can handle it, but in case 3, I don't know how to handle
it. Only let the program halting and waiting for the reture. It seem not
work. If I can set a timeout for web query, it may help.

Do you have some suggestion, please help.
Best regards,

Hi there,

I'm really hoping somebody can help me.....

......I've been using Excel(2003)'s "Web Query" for a while now but I've
encountered a situation where the spreadsheets I've created only work
on "some" machines and not others. All the machines in question are
running XP os and Excel 2003sp2.

Basically, the web queries are used to extract share prices.....I know,
I know.....aren't pretty much ALL web queries used to do this :-)

However, on certain machines, the "Refresh" of the web queries causes
the data to disappear and the cells become blank.

So, upon further investigation, it appears when you "Edit Web Query"
the query has "lost" the ticks next to the table(s) that were selected.
This only occurs on certain machines. Now, if you re-tick the table(s)
the query will work as desired (but not on the other machines - there's
two distinct sets of machines where the query will either work or not
work, and if you edit the query these sets are reversed).

I tried saving out an iqy file from each machine to see what the
differences may be......and the only thing I can spot is that the line
"Selection=" varies between the two sets of machines. For example, one
set generates "Selection=24" and the other set generates "Selection=21"
for exactly the same web query. I'm sure this is the root of the
problem.....

So, in short.....can anyone advise me as to how I can rectify this
situation? Is there a specific dll that Excel calls for the Web Query
functionality? Is it a machine-specific setting that could cause the
web page to be parsed differently? Or.....??

Any input or help is very gratefully received!

Many thanks,
Matt

Good afternoon,

I have a macro on my workbook_open() method like this:

    With
ActiveWorkbook.Connections("Connection")
        .Name = "Connection"
        .Description = ""
    End With
    Range("A1").Select
    
    With Selection.QueryTable
        .Connection = Get_URL
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = False
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1").Select
    ActiveWorkbook.Connections("Connection").Refresh
I am opening this workbook in a VB script like this:

dir =
CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False

Set objWorkbook = objExcel.Workbooks.Open(dir & "Variacion_Interanual.xlsm")
wscript.sleep 15000
objWorkbook.Save
objWorkbook.Close
The problem is that when the web query returns no data, it displays a messagebox which requires user interaction to click on "YES".

Is there a way to prevent this messagebox to appear?

Hi ,dear All

I have already use excel web query, and set every 5 minutes auto update web.
And here is question , I want to use vba event to trigger when cell's value changed.

Unfortunately,

	VB:
	
 Range) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code didn't trigger successfully,I think that web query make cells
value changed didn't trigger the event.
So any useful event should I use ??
Thanks a lot!

Hi everybody,

I used macro record and got a macro downloading data from:

"http://stockcharts.com/def/servlet/SC.scan?s=TSA[/url][t.t_eq_s]![as0,20,tv_gt_40000]![yg_eq_1]"

However when I run the macro, I ALWAYS get error message saying the URL is not correct. I have checked the URL many time and I am very sure the url is correct. Also when I download data from the url manually using web query, there is no problem at all.

What wrong there? How to avoid the error message when running the macro? Any help will be appreciated.

This question deals with a single workbook that consists of 28 Tabs. The first two tabs are reports. The next 26 tabs are worksheets with web queries on each tab. Is there a code that loops through the 26 sheets and refreshes the web queries on each one??


No luck finding an answer? You could always try Google.