2nd TRY: Up from 2003 to 2007; code now throws error creatingobject??

[Ed from AZ]

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

 

[Jim Rech]

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
| 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

 

[Ed from AZ]

I sent the user this code to put in a Word module, in case something
else in the whole macro was causing problems, but no joy!

Sub TryOpenXL()

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strSecSet
Dim strXL As String

Set xlApp = CreateObject("Excel.Application")
strSecSet = xlApp.AutomationSecurity
xlApp.AutomationSecurity = msoAutomationSecurityLow

' >> CHANGE THE FILE PATH BELOW TO POINT TO YOUR FILE <<
strXL = "C:\Documents and Settings\UserName\Desktop\MyList
\MyList.xls"

xlApp.Workbooks.Open strXL, , False
xlApp.AutomationSecurity = strSecSet
xlApp.Visible = True

' >> ERROR OCCURS HERE!!! <<
Set xlBook = xlApp.ActiveWorkbook

MsgBox xlBook.Name
End Sub

(One of the IT guys hinted the Excel12 library might have corrupted
during the push!)

Ed

 

[Jim Rech]

You didn't answer my question re is the workbook visible in Excel before the
Set.

If the user can run Excel there is not likely any corruption.

--
Jim
I sent the user this code to put in a Word module, in case something
else in the whole macro was causing problems, but no joy!

Sub TryOpenXL()

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strSecSet
Dim strXL As String

Set xlApp = CreateObject("Excel.Application")
strSecSet = xlApp.AutomationSecurity
xlApp.AutomationSecurity = msoAutomationSecurityLow

' >> CHANGE THE FILE PATH BELOW TO POINT TO YOUR FILE <<
strXL = "C:\Documents and Settings\UserName\Desktop\MyList
\MyList.xls"

xlApp.Workbooks.Open strXL, , False
xlApp.AutomationSecurity = strSecSet
xlApp.Visible = True

' >> ERROR OCCURS HERE!!! <<
Set xlBook = xlApp.ActiveWorkbook

MsgBox xlBook.Name
End Sub

(One of the IT guys hinted the Excel12 library might have corrupted
during the push!)

Ed

 

[Ed from AZ]

Sorry, Jim. Yes, Excel is visible and the file is open.

| 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.

| 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!

Ed