I have a macro which gets external data via a web query.

If for some reason a connection can't be made a 400 error appears. Is it
possible to add some code to mine below which says if connection fails
display message "Connection failed" press Get New Data to try again"

If so, what do I need and where exactly would I insert it into my code below?

Thanks a lot

Keith

-------

Sheets("Data").Select
If Range("a1").Value 0 Then
Range("A1:AJ65536").Select
Selection.ClearContents
Selection.QueryTable.Delete
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.xyz.co.uk/extranet/mydata.asp",
Destination:=Range("A1"))
.Name = "mydata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False

End With
Call next macro
End Sub

right before add query add the line

on error goto GetNewData

then right above end sub add

exit sub
GetNewData:
'now add your message box to get new data



"nospaminlich" wrote:

I have a macro which gets external data via a web query.

If for some reason a connection can't be made a 400 error appears. Is it
possible to add some code to mine below which says if connection fails
display message "Connection failed" press Get New Data to try again"

If so, what do I need and where exactly would I insert it into my code below?

Thanks a lot

Keith

-------

Sheets("Data").Select
If Range("a1").Value 0 Then
Range("A1:AJ65536").Select
Selection.ClearContents
Selection.QueryTable.Delete
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.xyz.co.uk/extranet/mydata.asp",
Destination:=Range("A1"))
.Name = "mydata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False

End With
Call next macro
End Sub

Thanks Ben. Works a treat, brilliant!

"ben" wrote:

right before add query add the line

on error goto GetNewData

then right above end sub add

exit sub
GetNewData:
'now add your message box to get new data



"nospaminlich" wrote:

I have a macro which gets external data via a web query.

If for some reason a connection can't be made a 400 error appears. Is it
possible to add some code to mine below which says if connection fails
display message "Connection failed" press Get New Data to try again"

If so, what do I need and where exactly would I insert it into my code below?

Thanks a lot

Keith

-------

Sheets("Data").Select
If Range("a1").Value 0 Then
Range("A1:AJ65536").Select
Selection.ClearContents
Selection.QueryTable.Delete
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.xyz.co.uk/extranet/mydata.asp",
Destination:=Range("A1"))
.Name = "mydata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False

End With
Call next macro
End Sub