Oliver Dewar
Board Regular
- Joined
- Apr 17, 2011
- Messages
- 201
Hi Guys.
I've got some vba code that imports a website table onto a sheet. 99% of the time it works perfectly. 1% of the time the connection times out. I've found that if I manually trigger the exact same connection immediately it works. I've not used error handling before and have read up on forums about it... but still can't find what I need.
I'd like some code that will create a loop OnError 5 times before finally giving up... something like this:
OK... so that's sudo code but I hope it serves to illustrate my thinking.
I guess all I really need is a loop that works only in the event that there's an error.
As always, any help will be greatly appreciated
I've got some vba code that imports a website table onto a sheet. 99% of the time it works perfectly. 1% of the time the connection times out. I've found that if I manually trigger the exact same connection immediately it works. I've not used error handling before and have read up on forums about it... but still can't find what I need.
I'd like some code that will create a loop OnError 5 times before finally giving up... something like this:
Code:
dim ErrorCount as long
ErrorCount = 0
OnError start here again and ErrorCount = ErrorCount + 1
With Sheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://websitewithtable.com" _
, Destination:=Sheets("Sheet1").Range("$A$1"))
.name = _
"websitewithtable.com"
.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
if ErrorCount > 5 then
msgbox "I give up... the internet has obviously blown up!"
end if
OK... so that's sudo code but I hope it serves to illustrate my thinking.
I guess all I really need is a loop that works only in the event that there's an error.
As always, any help will be greatly appreciated
Last edited: