B
Bruce
I have the following webquery that retrieves data from the web. Before I
refresh it I would like to validate the URL exists and is valid. If it
doesn't then I would like to send a message and end the macro.
How do I go about this? Should I set a timeout factor?
Bruce
Sub getQuote()
Dim QuerySheet As Worksheet, DataSheet As Worksheet
Dim qurl As String, qStart As String, queryTags As String
Dim i As Integer
Dim nQuery As Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set DataSheet = ActiveSheet
queryLink = "http://finance.yahoo.com/d/quotes.csv?s="
queryTags = "nb3b2l1c6p2pohgva2kjd1t1"
qStart = "C7"
Range(qStart).CurrentRegion.ClearContents
i = 7
qurl = queryLink + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + queryTags
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range(qStart))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range(qStart).CurrentRegion.TextToColumns
Destination:=Range(qStart), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Columns("C:C").EntireColumn.AutoFit
Call Del_Name_Range
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("A5").Select
End Sub
Function Del_Name_Range()
For Each N In Sheet26.Names
If InStr(N.Name, "ExternalData") > 0 Then N.Delete
Next N
End Function
refresh it I would like to validate the URL exists and is valid. If it
doesn't then I would like to send a message and end the macro.
How do I go about this? Should I set a timeout factor?
Bruce
Sub getQuote()
Dim QuerySheet As Worksheet, DataSheet As Worksheet
Dim qurl As String, qStart As String, queryTags As String
Dim i As Integer
Dim nQuery As Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set DataSheet = ActiveSheet
queryLink = "http://finance.yahoo.com/d/quotes.csv?s="
queryTags = "nb3b2l1c6p2pohgva2kjd1t1"
qStart = "C7"
Range(qStart).CurrentRegion.ClearContents
i = 7
qurl = queryLink + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + queryTags
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range(qStart))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range(qStart).CurrentRegion.TextToColumns
Destination:=Range(qStart), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Columns("C:C").EntireColumn.AutoFit
Call Del_Name_Range
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("A5").Select
End Sub
Function Del_Name_Range()
For Each N In Sheet26.Names
If InStr(N.Name, "ExternalData") > 0 Then N.Delete
Next N
End Function