VBA 1004 Excel Error

Topic

New Reply

I have not been able to figure out why I’m getting a 1004: “Select method of Range class failed” error in an Access application which is attempting to loop through a record set and build a series of xls workbooks from a template.

I successfully open the template and set a reference to the sheet which will be operated on with the following:

Set wkb = xlApp.Workbooks.Open(FileName:=stXLSTmplt)

Set sht = xlApp.ActiveWorkbook.Sheets(“Expenses”)

The sht reference is passed to several functions as an object parameter and I’m able to successfully load the record set into the spread sheet with

psht.Range(“Data_Start”).CopyFromRecordset rst, , 11 where psht is the passed object.

However, when the code comes to the line

psht.Range(“Yr0d”).Select

the 1004 error is raised.

In debug mode if I enter in the immediate window

?psht.Range(“Yr0d”).name

the correct cell reference is returned (=Expenses!$I$9).

Appreciate any insights,
Marty

Reply | Quote

Replies

I suspect that the Expenses sheet is not the active sheet when you try to execute this line

psht.Range(“Yr0d”).Select

You cannot use this to select a cell on the sheet psht unless it is active

To overcome this you can use

xlApp.Goto Reference:=”Yr0d”

Or Probably better in this instance

pSht.Select
psht.Range(“Yr0d”).Select

Assuming pSht points to the Expenses Sheet

Reply | Quote

Andrew,

Somehow I just didn’t see that.
Thanks much.

Reply | Quote

I received the same error number when one of my macros tried to paste into a locked cell on a protected sheet.

Reply | Quote