Runtime Error 1004

normanbox

New Member
Joined
Jul 8, 2015
Messages
44
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,297
Office Version
2019, 2016, 2013
Platform
Windows
looks like the "" marks
 

normanbox

New Member
Joined
Jul 8, 2015
Messages
44
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

Moderator
Joined
Oct 23, 2004
Messages
10,297
Office Version
2019, 2016, 2013
Platform
Windows
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

Well-known Member
Joined
Jun 10, 2015
Messages
898
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

New Member
Joined
Jul 8, 2015
Messages
44
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.
 
Last edited:

normanbox

New Member
Joined
Jul 8, 2015
Messages
44
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".
 
Last edited:

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
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

New Member
Joined
Jul 8, 2015
Messages
44
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.
 
Last edited:

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,297
Office Version
2019, 2016, 2013
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,751
Messages
5,446,284
Members
405,394
Latest member
WStockel

This Week's Hot Topics

Top