OnError... do it again up to 5 times then quit.

[Oliver Dewar]

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:

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

 

[VBA Geek]

Sub importWebQuery()
Dim ErrorCount As Integer


On Error GoTo HandleError


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


MainExit:
Exit Sub




HandleError:
ErrorCount = ErrorCount + 1
If ErrorCount > 5 Then
    MsgBox "I give up... the internet has obviously blown up!", vbExclamation
    Resume MainExit
Else
    Resume
End If


End Sub

 

[Oliver Dewar]

Thanks so much for this.

I'm testing now.

So this will make it count the errors and try the connection again 5 times before giving up? If so, that's perfect.

 

[Oliver Dewar]

Just tested this.

It seems to go to the error handler and count to 5 then quit.

What I need is for it to repeat the data query 5 times (only if errors occur in the process) before it quits. Can you help me to make this loop the data query and count?

 

[VBA Geek]

an error does occur for your webquery because that website does not appear to exist, so it tries to import it 5 times and since it always fails it then just quits, test it with a link that works

 

[Oliver Dewar]

Thanks VBA Geek.

The website I the example is just a dummy site - so yes it doesn't exist... I wasn't expecting anyone to test with the dummy site.

I still need a solution that loops on error and then proceeds as normal if the web query works... or gives up after 5 tries.