2007 producing a 1004 error

[Guest]

I have a database that has been programmed (using VBA) to be able to export
data to Excel and import data from Excel. Now that I have Access 2007, the
exporting still works as normal (when told to save the file in .xls format),
but the importing now produces an error:

Method 'Select' of object'_Worksheet' failed

I believe this error is happening when it gets to the code:

Dim rowid As Integer
Dim Excel As Object
Dim xlWorkbook As Object
Dim xlWindow As Object
Dim dateExcel As Date
Dim dateAccess As Date
Set Excel = GetObject(Me.txtXLFIle.Value)
Dim xlSheet As Object
Dim ExcelVersion As String
Excel.Sheets("Overview").Select
Set xlSheet = Excel.ActiveSheet

Again, the only thing different is that the database is being run on a PC
with Access 2007 instead of Access 2003... any help would be greatly
appreciated!

 

[Stefan Hoffmann]

hi,

Method 'Select' of object'_Worksheet' failed
Set Excel = GetObject(Me.txtXLFIle.Value)

I would use

Dim objExcel As Object
Set objExcel = GetObject(, "Excel.Application")
objExcel.Workbooks.Open Me.txtXLFIle.Value


mfG
--> stefan <--

 

[RoyVidar]

I have a database that has been programmed (using VBA) to be able to
export data to Excel and import data from Excel. Now that I have
Access 2007, the exporting still works as normal (when told to save
the file in .xls format), but the importing now produces an error:

Method 'Select' of object'_Worksheet' failed

I believe this error is happening when it gets to the code:

Dim rowid As Integer
Dim Excel As Object
Dim xlWorkbook As Object
Dim xlWindow As Object
Dim dateExcel As Date
Dim dateAccess As Date
Set Excel = GetObject(Me.txtXLFIle.Value)
Dim xlSheet As Object
Dim ExcelVersion As String
Excel.Sheets("Overview").Select
Set xlSheet = Excel.ActiveSheet

Again, the only thing different is that the database is being run on
a PC with Access 2007 instead of Access 2003... any help would be
greatly appreciated!

Even when doing late binding, I think I would avoid using the actual
library name as name of any of my variables (Excel).

When using GetObject method, with the first arguement (pathname), I
think what is returned, is a workbook object, which means
Excel.Sheets("Overview").Select should theoretically work.

Hovewer, this method, is implicit, and have a tendency of creating
extra instances of Excel in memory (check with Task Manager), RT 1004
on seemingly OK lines of code...

I would try being a bit more explicit

dim xl as object
dim wr as object
dim sh as object

set wr = GetObject(Me.txtXLFIle.Value)
' if necessary
' set xl = wr.parent
set sh = wr.sheets("Overview")