"Workbooks.Open" fails with 2007??

[Ed from AZ]

I am automating Excel through Word VBA. The code is breaking at the
line to open the Excel workbook. THe code has worked great through
Word/Excel 2003 - but the 2007 update was pushed over the weekend and
now it breaks on Workbooks.Open. No other Excel instances are open
when this code runs, and the workbook can be opened with a double-
click with no errors.

Set objExcel = New Excel.Application
objExcel.Visible = True
Set objWkbk = objExcel.Workbooks.Open(FileName:=strLookIn &
"\MyList.xls")

The error is "Method 'Open' of object 'Workbooks' Failed."

Does anyone have any insight on this?

Ed

 

[JLGWhiz]

Unless you specifically save the file as a 2003 format, it will require a
four digit file extension to open it. If it has macros it will be .xlsm if
it does not it will be .xlsx. To be sure, check in the file open dialog box
with file extensions set to show.

 

[Ed from AZ]

The file was originally an Excel 2000 file, which opened fine in XL
2000 and 2003. All the code is now in a Word 2000/2003 document,
which is being opened in an Office 2007 (Win XP Pro) environment and
is trying to open the original XL file in XL 2007. The XL file does
have macros.

Ed

 

[Ed from AZ]

We did save the Excel file as a xlsm file.

What we discovered was that either as xls or xlsm, the file _does_
open. It's just that the Word code can't create the workbook object.
The error comes up that the Open Method failed, but the open works -
setting the object fails.

Ed