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

 

[V_Malkoti]

If I understand correctly you were providing the value of ticker every time you ran the code from Immediate window, so that's not exactly dynamic. A statement that you write in Immediate window is a one-time command - it doesn't permanently bind to the file.

Now as mole999 suggested you can place this statement in Worksheet_Change event of Data sheet and any change in the sheet will cause the event code to fire.
In case you didn't know how to do that, here are the steps:
1. Open VBA window.
2. Open Project Explorer if not open (View -> Project Explorer; then you should see a workbook-worksheets tree structure in a pane)
3. Double click on icon on Data sheet to open its module.
4. In the two drop-down visible above code window, choose Worksheet and Change.
5. In the subroutine container that is autogenerated, place the line of code.

Now if the ActiveCell that you need to refresh every time is in a different sheet, replace it with complete qualifier.

Sheets("SheetName").Range("B2").QueryTable.Connection = "URL;http://http://finance.yahoo.com/q/bs?s=" & [Data!A1] & "+Balance+Sheet&annual"