Web Query (book example not working)

[Gwenda16]

I have entered the code from "VBA and Macros for Microsoft Excel" on page 341. When I step through the code I always get an error at the following line:

Set QT = WSW.QueryTables.Add(Connection:=ConnectString, _
Destination:=WSW.Range("A1"))

What might I be missing? I'm not new to Excel, but I am new to macros. Do I need some other background things setup, such as worksheet names? certain worksheet active? the range highlighted? etc.....??

Thanks for your help.
Gwenda

 

[Colo]

Hi Gwenda,

I'll tell your q to the authors.

 

[starl]

What's the error that you are getting? Have you downloaded the sample files - is that what you are using?

 

[Gwenda16]

I have a blank worksheet named "Workspace", and worksheet named "Portfolio" with the stock symbols (as per fig 14.4)

When I step through the code I get the following error:
Runtime error '5' Invalid procedure call or argument.

 

[MrExcel]

Try this for me:

1. Step through the code until it gets past the For Next loop that builds the connect string

2. Hit Ctrl+G to bring up the immediate pane

3. In the immediate pane, type Print ConnectString

4. Copy the ConnectString and paste it in a reply here


When I run through the code, I get a connect string like this:

URL;http://finance.Yahoo.com/q/cq?d=v1&s=PSO,+SJM,+KO,+MSFT,+CSCO,+INTC,+T


My code to create the connect string is like this. The physical limitations of the book split one of the critical lines of code. You can rejoin it in the VBA editor.

    FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
    For i = 2 To FinalRow
        Select Case i
            Case 2
                ConnectString = "URL;http://finance.Yahoo.com/q/cq?d=v1&s=" & WSD.Cells(i, 1).Value
            Case Else
                 ConnectString = ConnectString & ",+" & WSD.Cells(i, 1).Value
        End Select
    Next i

Bill

 

[Gwenda16]

Many thanks, Bill. It's working fine now.

Gwenda

 

[Jeff_N_Texas]

Hi,
would it be possible to send me your excel workbook with the macro to pull multiple tickers.

I have been working on one and can't get it to work.

 

[Gwenda16]

Jeff,

I would recommend getting a copy of "VBA and Macros for Microsoft Excel" and checking out pg 341, plus the surrounding pages (along with the info submitted by Bill). All the necessary code is there.

Also make sure that your excel worksheet with the tickers listed does not have any spaces after the ticker name. This will prevent it from working. Only the ticker must be in the excel cell.

Gwenda