I am trying to run a VBA Web Query to Yahoo Finance to get monthly stock price data. The error I get is : 'Run Time Error 1004' Unable to open <site> Cannot connect to the internet server.
I would greatly appreciate any help. My complete code is below.
Sub RunQuery(StSymbol As String)
'Dim ConnectStr As String
ConnectStr = "URL; http://finance.yahoo.com/q/hp?<wbr>s=" & StSymbol & "&a=" & BMonth & "&b=2" & "&c=" & BYear & "&d=" _
& EMonth & "&e=2&f=" & EYear & "&g=m"
Debug.Print ConnectStr
With ActiveSheet.QueryTables.Add(<wbr>Connection:=ConnectStr, Destination:=Range("$A$1"))
.Name = "StockPrices_" & StSymbol
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True '(ERROR HERE)
End With
End Sub
I would greatly appreciate any help. My complete code is below.
Sub RunQuery(StSymbol As String)
'Dim ConnectStr As String
ConnectStr = "URL; http://finance.yahoo.com/q/hp?<wbr>s=" & StSymbol & "&a=" & BMonth & "&b=2" & "&c=" & BYear & "&d=" _
& EMonth & "&e=2&f=" & EYear & "&g=m"
Debug.Print ConnectStr
With ActiveSheet.QueryTables.Add(<wbr>Connection:=ConnectStr, Destination:=Range("$A$1"))
.Name = "StockPrices_" & StSymbol
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True '(ERROR HERE)
End With
End Sub