PDA

View Full Version : Error Handling in VBA



ShaunC
01-11-2012, 04:35 AM
Hi everyone

I am getting an error when running some VBA code:

The Internet address 'h t t p ://ichart.finance.yahoo.com/table.csv?s=abm.ax&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=.csv' is not valid

(I had to space the http: due to low post count)

The VBA code generates a series of worksheets and then imports the historical data from yahoo finance. The share list was downloaded directly from the Australian Stock Exchange and adapted to my needs. The code works well until there is a share query generated which is not available on Yahoo finance, in this case abm.ax.

What would be the best way to handle this error?

I have tried simply to add:

On Error Resume Next

which works but only after I click the OK button in the error message box.This is clearly not the way to handle the error as I would either:
1. Avoid the error being generated; or
2. provide a mechanism for the error message to be dealt with and the code continues on running until the list is complete.

The code (at this stage) is broken into two parts:

The first, when executed:
adds the worksheet
renames the worksheet to the yahoo finance stock code
"calls" the second part of the code which generates the query to obtain historical data
when data download is complete, moves on to generating the next worksheet on the list.The code which generates the query (the second)(again I had to space the "http:" reference)

Sub GetHistoricalData()
'
' This code will get historical closing trade data from yahoo
' finance using the worksheet name to complete the query URL
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;h t t p ://ichart.finance.yahoo.com/table.csv?s=" & ActiveSheet.Name & "&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=.csv" _
, Destination:=Range("$A$1"))
.Name = "table.csv?s=" & ActiveSheet.Name & "&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

mdmackillop
01-11-2012, 06:35 AM
Sub GetHistoricalData()
'
' This code will get historical closing trade data from yahoo
' finance using the worksheet name to complete the query URL
'
On Error Resume Next

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;http://ichart.finance.yahoo.com/table.csv?s=" & ActiveSheet.Name & "&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=.csv" _
, Destination:=Range("$A$1"))
.Name = "table.csv?s=" & ActiveSheet.Name & "&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
If Err <> 0 Then
MsgBox "Not available: Error " & Err & " - " & Err.Description
End If
On Error GoTo 0
End Sub

ShaunC
01-11-2012, 06:25 PM
Hi mdmackillop

Thank you for your help, I really appreciate it.

I have entered the additonal code:
On Error Resume Next
and
If Err <> 0 Then
MsgBox "Not available: Error " & Err & " - " & Err.Description
End If
On Error Goto 0
then ran the code, but I am now getting the error "code execution has been interrupted" and four options - continue, end, debug and help. When I click debug the "End With" line above the if statment you suggested is highlighted yellow.

Do you have any ideas?

Cheers

Shaun

Bob Phillips
01-12-2012, 02:43 AM
Try rebooting (seriously!).

ShaunC
01-12-2012, 03:54 AM
Hi xld

I did get some limited success on rebooting.

I now get:

The Internet address 'h t t p ://ichart.finance.yahoo.com/table.csv?s=abm.ax&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=.csv' is not valid

(I had to space the http: due to low post count)

and then:

Not available error 1004 - Microsoft excel cannot access this file.

I have searching the net trying to find a solution. I have come across url validation but this is way beyond my skills. Thank you for the tip though.

Cheers

Shaun

Bob Phillips
01-12-2012, 04:16 AM
I just plugged that URL into a browser, and got page not found. It is just a bad URL.

mdmackillop
01-12-2012, 04:50 AM
Not available error 1004 - Microsoft excel cannot access this file.

That is the message I created with this line as the error handler

MsgBox "Not available: Error " & Err & " - " & Err.Description

ShaunC
01-15-2012, 08:14 PM
Hi mdmackillop and xld


I just plugged that URL into a browser, and got page not found. It is just a bad URL.

You are right it is a bad URL. The problem is that the list that this is working off it +2300 URL's. To sort through them one at a time would take for ever in the larger process. So what I would like to acheive is where excel comes across a bad URL in the list it just moves on to the next item in the list.


That is the message I created with this line as the error handler

MsgBox "Not available: Error " & Err & " - " & Err.Description



I see, how could it be changed so that it "moves on" to the next item in the list automatically (no user input)?

Cheers

Shaun

mdmackillop
01-16-2012, 02:30 AM
Delete the Message Box, assumining you have looping set correctly.

Bob Phillips
01-16-2012, 03:17 AM
Or better still, create another list of the bad URLs for you to address offline, instead of messaging them inline.