Runtime Error 1004

[normanbox]

I'm a novice at VBA and am using the immediate window. I am getting a Run-Time error '1004': Application-defined or object-defined error and I don't know why. I have the following code in my immediate window.

ActiveCell.QueryTable.Connection="URL;http://finance.yahoo.com/q/bs?s=[""BalanceSheets""]&+Balance+Sheet&annual"

I have a similar code, but for the Income Statement which works just fine. I created the code last week and can't seem to find it now, otherwise I would show you.

Any help on why I'm getting the error would be appreciated and also any ideas where code placed in the immediate window is stored would also be helpful.

Thanks

 

[mole999]

looks like the "" marks

 

[normanbox]

Thanks.

When I eliminate the two double quotes, I then get a "Compile error: Expected: end of statement" which I also can't figure out why I'm getting.

 

[mole999]

look at the one that works but


strings are deliminated by pairs of "" and joins involve & so " text " & "-" & "more text" works because of how it joins together

yours does this in excel
"URL;No Ticker Symbol - Yahoo! Finance["
"BalanceSheets"
"]
&
+Balance+Sheet&annual"

 

[V_Malkoti]

Whats the actual URL that you are trying to connect to? Is BalanceSheets really a part of the original URL or are you trying to refer to a named range there?

 

[normanbox]

I'm going crazy because I can't find the one that works, otherwise I would. The troubling thing, it's part of the same workbook! I can only find part of connection property, which says, "http://finance.yahoo.com/q/s/is?s=["Ticker"]&+Income+Statement&annual". When I entered the entire code, which I can't find, I was able to point to where the "ticker symbol" lies and the query updated.

I have been trying to follow the example I found at: https://blogs.office.com/2009/07/31/using-parameters-with-web-queries/ which is how I was able to get the Income Statement code to work. In the page it has me complete the following steps:
1.Click any cell in the new table.
2.Open the Visual Basic Editor by hitting the Alt+F11 key.
3.Hit control+g to open the immediate window.
4.Now type the following line:
activecell.QueryTable.Connection=”URL;http://moneycentral.msn.com/companyreport?symbol=[“”Ticker””]”
5.Click Enter to confirm.
6.Close the Visual Basic Editor.

Then it goes on and gives additional instructions, which worked perfectly.

I must be missing something simple to complete the code and find my other code.

 

[normanbox]

V_Malkoti,

The actual URL is: http://finance.yahoo.com/q/bs?s=AAME+Balance+Sheet&annual

However, the ticker is "AAME" and that is updated based on sheet "Data A1".

 

[V_Malkoti]

Try this:

ActiveCell.QueryTable.Connection = "URL;http://http://finance.yahoo.com/q/bs?s=" & [Data!A1] & "+Balance+Sheet&annual"

AAME ticker value will be read from cell A1 of sheet Data.

 

[normanbox]

That is great progress, but the ticker symbol doesn't dynamically update the ticker based on what's in Data!A1. For example, if I update the cell with AAU, it doesn't automatically update the query to that symbol's webpage, whereas I'm able to do that in my Income Statement query.

 

[mole999]

in this instance Data!A1 is only read when the macro is run

unless you have an on worksheet_change event that monitors Data!A1 and runs the query