So if you broke this line into two the second is where the error would
occur?:
objExcel.Workbooks.Open FileName:=strLookIn & "\MyList.xls"
Set objWkbk = objExcel.ActiveWorkbook
If so, can you see that the workbook is open when the second line is run? (I
know it's opening but it might be delayed for some reason).
Tried sticking a DoEvents between the two?
--
Jim
"Ed from AZ" wrote in message
...
| I've been beating on this for two days. I posted earlier in
| excel.programming (
http://groups.google.com/group/
| microsoft.public.excel.programming/browse_thread/thread/
| fb2e4df1284c42a2?hl=en#), but I've gotten more info since then and
| wanted to spread the joy between both groups!! 8\
|
| The macro code is in a Word document origianlly written in Word 2000,
| then updated in Word 2003. Part of the code opens an Excel file and
| writes to it. The Word file has a ".doc" extension, and the Excel
| file has ".xls". The code is launched from a Document_Open macro, so
| the user just puts this doc on his desktop and opens it to run
| everything.
|
| Everything worked great until Monday, which was when we came in to
| work and found Office 2007 on our computers. Now the person who uses
| this particular routine gets an error every time the code access the
| Excel file. The code will actually open the file, but will not set an
| object to the open file. It works great on my machine, but not on
| his.
|
| 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."
|
| Since Monday, I've tried saving the .doc file as .docm, saving the
| Excel file as .xlsm, changing from early binding to late binding,
| changing how the code finds the file name to make sure I detect with
| or without the current extension, and throwing in code to set and
| reset the Excel msoAutomationSecurity so the macros in the Excel file
| don't hang this up.
|
| For any instance, the code will execute the Workbooks.Open command,
| and the file will open in Excel. But it throws the error in trying to
| set an object to the workbook that just got opened! And only on his
| machine!
|
| Where can I look for the answer to this? A library reference? A
| missing dll somewhere? Moon in the wrong phase? We're going a bit
| nuts here! Any help is reatly appreciated.
|
| Ed