G
Guest
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
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