Run time error 91

[Timtropolis]

Greetings all,

I have a procedure that worked fine until I upgraded from Access 2003 to Access 2010. Below is a snipped of the code:


Dim xlAP As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Set xlAP = CreateObject("Excel.Application")
Set xlWB = xlAP.ActiveWorkbook
Set xlWS = xlWB.ActiveSheet

This procedure is supposed to open a new spreadsheet and then insert data accordingly. I'm now receiving a "Run time error 91 - Object variable with block variable not set" on the line in question (see above)

From what I can see, the "ActiveSheet" option is viable in 2010, but just for kicks, I tried xlWB.WorkSheets("Sheet1") but still receive the same error. I also tried declaring all 3 variables as "Objects" but still the same error.

If anyone could point me in the right direction it would be greatly appreciated.

TIA,
Tim

 

[boblarson]

If I use the exact same code you have there, I get the same error in Access 2003. When you create a new application, there are no workbooks. You have to either open one or add one first.

So, changing to this should work:

Dim xlAP As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
 
Set xlAP = CreateObject("Excel.Application")
Set xlWB = [COLOR=red][B]xlAP.Workbooks.Add[/B][/COLOR]
Set xlWS = xlWB.ActiveSheet