Error handle for invalid entries when running background web query

mjsimmons · Feb 8th 2011

Hi
Downloading stock prices from Yahoo Finance, against a list of stock codes that are valid works fine e.g.

AA, AXP, BA, C, CAT, D etc.

The Query Quote coding used being as follows:

Code

QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
 
Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Range("C1:I1").Select
Selection.ColumnWidth = 8

Display More

The problem I encounter is if the list of stock codes, include an invalid code (in this example I have used 'VVV', or a stock code that is valid, but is not in the Yahoo database for the specific date that I am downloading, the following a error message is displayed:

Run Time Error 1004 Unable To open
http://chart.yahoo.com/table.c…&a=1&b=3&c=2011&d=1&e=The Internet site reports that the item you requested could not be found (HTTP/1.0 404)

The system stops, and I cannot move forward.

On debugging - .Refresh BackgroundQuery:=False is highlighted.

I would like to be able to add code that would skip any invalid entries such as the above, and which would then access the next valid stock code and download the stock price against this.
Any soultion that anyone could provide would be most appreciated.

Regards

Mike Simmons

junho lee · Feb 8th 2011

Re: EXCEL VBA - Problem with Refresh BackgroundQuery:=False

Hi mjsimmons,
Nice to meet you.
I write startline of code then works.

Code

On Error GoTo err_description
Set DataSheet = ActiveSheet
quote = "vvv"
qurl = "[URL]http://chart.yahoo.com/table.csv?s[/URL]=" & quote & "&a=1&b=3&c=2011&d=1&e=The"
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Range("C1:I1").Select
Selection.ColumnWidth = 8
err_description: Exit Sub

Display More

Regards, junho

mjsimmons · Feb 12th 2011

Re: EXCEL VBA - Problem with Refresh BackgroundQuery:=False

Hi Junho Lee

I very much appreciate your response.

By amending your code and leaving out lines 3 and 4 it worked, thus:

Code

On Error GoTo err_description
Set DataSheet = ActiveSheet
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Range("C1:I1").Select
Selection.ColumnWidth = 8

Display More

Thank you for your help.

Regards

Mike Simmons

mjsimmons · Feb 12th 2011

Re: EXCEL VBA - Problem with Refresh BackgroundQuery:=False

Hi Junho Lee

I left off the last line:

Code

err_description: Exit Sub

Regards

Mike Simmons

AAE · Feb 12th 2011

Re: Error handle for invalid entries when running background web query

Hello Mike,

Welcome to Ozgrid. We're glad to have you on board, however, please note the following regarding thread titles and code tags:

Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your [COLOR="navy"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, a search using YOUR as the search terms would return relevant results.

The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (colons, semi-colons, slashes, etc.)
The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

[COLOR="darkred"]Please note the change to your thread title as an example.[/COLOR]

Also:

Per the forum rules all VBA code must be wrapped in code tags, which you've omitted. I've added the tags for you this time only. Please be sure to use them in future posts.

[COLOR="navy"]How to use code tags[/COLOR]

[noparse]

Code

[/noparse]
[COLOR="red"]your code goes between these tags[/COLOR]
[noparse]

[/noparse]

Error handle for invalid entries when running background web query

Participate now!

Share