Why cannot Excel find this url?

[Gasman]

Hi all,
I have an Excel sheet that used to download a few stock quotes. Yahoo have now stopped access.
So I am tring another method, this time using Google finance.

From a few YouTube videos I have pieced together the code below.

If I take the connection string and paste manually (which is how I recorded the macro) it works. it also works if the ticker is hardcoded in the connection string.

However soon as I try and parameterise the string it says it cannot find the file.?

debug of string is
TEXT;http://finance.google.co.uk/finance/historical?q=LON:SSE&output=csv

Sub GetStockPrices()
Dim rngTicker As Range, rngRow As Range
Dim strTicker As String, strPrice As String

Call RemoveConnections

Sheets("Output").Select
Set rngTicker = Range("A2:A3")

For Each rngRow In rngTicker
    strTicker = Range("A" & rngRow.row).Value
    Sheets("Data").Select
    Cells.Delete

    Call Macro3(strTicker)
    'Do
    DoEvents
    strPrice = Sheets("Data").Range("B2").Value
    Range("B" & rngRow.row).Value = strPrice
Next rngRow



Set rngTicker = Nothing
Set rngRow = Nothing

End Sub

Sub Macro3(pstrTicker As String)
'
' Macro3 Macro
Dim strConnection

'strConnection = "TEXT;http://finance.google.co.uk/finance/historical?q=LON:" & pstrTicker & "&startdate=Nov+7,+2017&enddate=Nov+7,+2017&num=30&ei=vAcDWsCoMYeEU6fDgdgJ&output=csv"
strConnection = "TEXT;http://finance.google.co.uk/finance/historical?q=LON:" & pstrTicker & "&output=csv"

Debug.Print strConnection

    Sheets("Data").Select
    Cells.Clear
    With ActiveSheet.QueryTables.Add(Connection:=strConnection, _
        Destination:=Sheets("Data").Range("$A$1"))
        .Name = ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
Sub RemoveConnections()
Dim xConnect As Object
For Each xConnect In ActiveWorkbook.Connections
    xConnect.Delete
Next xConnect
Set xConnect = Nothing
End Sub

 

[Ranman256]

it may be because it not a site, but rather a download link.
The link moves data, rather than display html.

 

[Gasman]

Hi Ranman,

What I cannot get my head around is that the Youtuber appears to do the same thing (albeit on a a later version of Excel) and it works fine.

Been trying this for an hour now. :banghead:

https://www.youtube.com/watch?v=naYMz6nUqTQ&t=1s

 

[Gasman]

Just recreated the code at home and had it working with a hard coded string, created via Record macro as before.
Then tried with a parameter for the ticker, still working.
Then I needed a parameter for the market and that broke it.

Back with a hardcoded string and now not working?

Recreated again from macro, replaced with both parameters and working???:banghead::banghead::banghead:

 

[Gasman]

Now posted in the MrExcel forum for any additional help.
I believe I have proved that the concatenation of the parameters in the string is not the problem, as the string has the correct syntax and works for one or two attempts.?

 

[Gasman]

Well not solved, but I am able to run for several tickers in succession before it fails.
Now believe to many calls within a period are blocked by Google.