macro refuses to run on open
Posted by Dwight on August 06, 2001 12:30 PM
I have a (lengthy) macro which essentially runs a Web Query to get a current stock quote and pastes the value into my spreadsheet (plus a lot of formatting which makes for the length). The macro works fine when I run it after opening the spreadsheet, but when I try to have it run automatically by pasting it into the This Workbook module in VBA Project (after code: Private Sub Workbook_Open() I get a Run-time error 1004 message, and when I select debug it highlights the line: Selection.QueryTable.Refresh BackgroundQuery:=False. The spreadsheet isnt protected at any time.
If this provides enough information, can anyone tell me what I might be doing wrong?
Posted by Damon Ostrander on August 06, 2001 6:18 PM
Hi Dwight,
The problem is that the QueryTable property requires a Range qualifer. This range is a cell or cells that intersect with the QueryTable of interest. In your statement
Selection.QueryTable...
Selection is a selected range. But when the workbook first opens there is nothing selected, so no range specified.
To fix it, just specify the range rather than use Selection:
Worksheets("Sheet2").Range("C7").QueryTable...
Probably a better way to do it, since this could fail if the Query Table is moved around on the sheet and no longer intersects with C7, would be to specify the Query Table like this:
Worksheets("Sheet2").QueryTables(1)...
which only assumes that this is the first Query Table on sheet 2, and doesn't require that you know where it is on the sheet.
Happy computing.
Damon
Posted by Dwight on August 07, 2001 9:41 AM
Damon: used second option, works great. Thanks! EOM
