Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming
|
|||
|
|||
2nd TRY: Up from 2003 to 2007; code now throws error creatingobject??
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 |
#2
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming
|
|||
|
|||
2nd TRY: Up from 2003 to 2007; code now throws error creating object??
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 |
#3
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming
|
|||
|
|||
2nd TRY: Up from 2003 to 2007; code now throws error creatingobject??
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 On May 21, 8:27*am, "Jim Rech" wrote: 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 ... | 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 |
#4
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming
|
|||
|
|||
2nd TRY: Up from 2003 to 2007; code now throws error creating object??
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 "Ed from AZ" wrote in message ... 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 On May 21, 8:27 am, "Jim Rech" wrote: 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 ... | 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 |
#5
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming
|
|||
|
|||
2nd TRY: Up from 2003 to 2007; code now throws error creatingobject??
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 On May 21, 10:08*am, "Jim Rech" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code executes fine, but compiler throws error. Huh? | Excel Programming | |||
VBS Script and ClearContents -- Code Throws An Error | Excel Programming | |||
Find Throws Error 91 | Excel Programming | |||
Excel 2000 - Excel 2003 now throws 'runtime error' | Excel Programming | |||
[Q] Save As throws type mismatch error in control's code? | Excel Discussion (Misc queries) |