Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
My code checks whether Excel app is open by using this line of code:
Set moExcel = GetObject(, "Excel.Application") If there is an error then my code opens Excel app, waits until Excel window appears and executes the same line of code and there is again the same error despite the fact that Excel was already opened. That problem is NOT a timing problem (I inserted 30 secs delay to the same effect). Interestingly, setting a breakpoint at this line and stepping through it does not create any error. On the other hand, when Excel is running prior to running this code there is NOT any error. Any help appreciated, Jack Public Sub CheckExcelApp() Set moExcel = Nothing Set moExcelWS = Nothing On Error GoTo Open_Excel_App Set moExcel = GetObject(, "Excel.Application") Debug.Print "Excel app found!" Exit Sub Open_Excel_App: Debug.Print "Excel app NOT found!" On Error GoTo 0 rtn = ShellExecute(Me.hwnd, "Open", "excel.exe", vbNullString, App.Path, vbNormalFocus) If rtn 32 Then 'no error from ShellExecute Start = Timer Do Until FindWindow(vbNullString, "Microsoft Excel") 0 If Timer - Start 10 Then ExcelApp = 1: Exit Sub Loop Debug.Print "Excel app opened" Else ExcelApp = 1: Exit Sub End If '''' DelayTimer (30) Resume Try_Open_Again Try_Open_Again: On Error GoTo Error_again Set moExcel = GetObject(, "Excel.Application") Debug.Print "Excel app found!" Exit Sub Error_again: Debug.Print "Excel app again NOT found!" End Sub .. |
#2
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
"Jack" <replyto@it wrote in message ... My code checks whether Excel app is open by using this line of code: Open_Excel_App: Debug.Print "Excel app NOT found!" On Error GoTo 0 rtn = ShellExecute(Me.hwnd, "Open", "excel.exe", vbNullString, App.Path, vbNormalFocus) Don't start Excel that way. Use Automation to start it. Here's a MUCH simpler way (using late-binding) to do what your wanting: Option Explicit Private moExcelApp As Object On Error Resume Next 'TEMPORARILY ignore errors Set moExcelApp = GetObject(, "Excel.Application") On Error GoTo EH 'Resume normal error handling If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") moExcelApp.Visible = True 'if you want Excel to be visible at this point End If On Error GoTo EH 'Resume normal error handling Exit Sub 'or Function as the case may be This gives you a reference to Excel without having to shell to it and using FindWindow and all the rest of the "garbage" you're doing to accomplish this. -- Mike Microsoft MVP Visual Basic |
#3
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
Mike,
Thank you very much for your reply. One more question: Using the automation, how can I start Excel application with the specified spreadsheet title? Jack "MikeD" wrote in message ... "Jack" <replyto@it wrote in message ... My code checks whether Excel app is open by using this line of code: Open_Excel_App: Debug.Print "Excel app NOT found!" On Error GoTo 0 rtn = ShellExecute(Me.hwnd, "Open", "excel.exe", vbNullString, App.Path, vbNormalFocus) Don't start Excel that way. Use Automation to start it. Here's a MUCH simpler way (using late-binding) to do what your wanting: Option Explicit Private moExcelApp As Object On Error Resume Next 'TEMPORARILY ignore errors Set moExcelApp = GetObject(, "Excel.Application") On Error GoTo EH 'Resume normal error handling If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") moExcelApp.Visible = True 'if you want Excel to be visible at this point End If On Error GoTo EH 'Resume normal error handling Exit Sub 'or Function as the case may be This gives you a reference to Excel without having to shell to it and using FindWindow and all the rest of the "garbage" you're doing to accomplish this. -- Mike Microsoft MVP Visual Basic |
#4
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
Using the automation, how can I start Excel application with the specified
spreadsheet title? Once you've got a reference to Excel, you can open up any file you want. Use code like the following: Dim WB As Excel.Workbook Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") where XLApp is a reference to the Excel Application object. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Jack" <replyto@it wrote in message ... Mike, Thank you very much for your reply. One more question: Using the automation, how can I start Excel application with the specified spreadsheet title? Jack "MikeD" wrote in message ... "Jack" <replyto@it wrote in message ... My code checks whether Excel app is open by using this line of code: Open_Excel_App: Debug.Print "Excel app NOT found!" On Error GoTo 0 rtn = ShellExecute(Me.hwnd, "Open", "excel.exe", vbNullString, App.Path, vbNormalFocus) Don't start Excel that way. Use Automation to start it. Here's a MUCH simpler way (using late-binding) to do what your wanting: Option Explicit Private moExcelApp As Object On Error Resume Next 'TEMPORARILY ignore errors Set moExcelApp = GetObject(, "Excel.Application") On Error GoTo EH 'Resume normal error handling If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") moExcelApp.Visible = True 'if you want Excel to be visible at this point End If On Error GoTo EH 'Resume normal error handling Exit Sub 'or Function as the case may be This gives you a reference to Excel without having to shell to it and using FindWindow and all the rest of the "garbage" you're doing to accomplish this. -- Mike Microsoft MVP Visual Basic |
#5
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
Doing your way:
Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") WB is NOT set I have found the following way which works, but I am not sure whether it is the right one: If FileExists(XLSheetFullTitle) = True Then Set moExcelWS = CreateObject(moExcelApp.Workbooks.Open(XLSheetFull PathTitle)) 'moExcelWS is not set but it loads the file ' then I do whats below and moExcelWS is set. If moExcelWS Is Nothing Then Set moExcelWS = moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle, Len(ExSheetTitle) - 4)) End If Jack "Chip Pearson" wrote in message ... Using the automation, how can I start Excel application with the specified spreadsheet title? Once you've got a reference to Excel, you can open up any file you want. Use code like the following: Dim WB As Excel.Workbook Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") where XLApp is a reference to the Excel Application object. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Jack" <replyto@it wrote in message ... Mike, Thank you very much for your reply. One more question: Using the automation, how can I start Excel application with the specified spreadsheet title? Jack "MikeD" wrote in message ... "Jack" <replyto@it wrote in message ... My code checks whether Excel app is open by using this line of code: Open_Excel_App: Debug.Print "Excel app NOT found!" On Error GoTo 0 rtn = ShellExecute(Me.hwnd, "Open", "excel.exe", vbNullString, App.Path, vbNormalFocus) Don't start Excel that way. Use Automation to start it. Here's a MUCH simpler way (using late-binding) to do what your wanting: Option Explicit Private moExcelApp As Object On Error Resume Next 'TEMPORARILY ignore errors Set moExcelApp = GetObject(, "Excel.Application") On Error GoTo EH 'Resume normal error handling If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") moExcelApp.Visible = True 'if you want Excel to be visible at this point End If On Error GoTo EH 'Resume normal error handling Exit Sub 'or Function as the case may be This gives you a reference to Excel without having to shell to it and using FindWindow and all the rest of the "garbage" you're doing to accomplish this. -- Mike Microsoft MVP Visual Basic |
#6
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
"Jack" <replyto@it wrote in message
Doing your way: Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") WB is NOT set It is in every version of VB and Excel that I've ever worked with. That's the appropriate way to opena workbook and get a reference to it. Once you've done that you can get the worksheets as in: Set moExcelWS = WB.Worksheets(1) I have found the following way which works, but I am not sure whether it is the right one: If FileExists(XLSheetFullTitle) = True Then Set moExcelWS = CreateObject(moExcelApp.Workbooks.Open(XLSheetFull PathTitle)) 'moExcelWS is not set but it loads the file ' then I do whats below and moExcelWS is set. That would open the file and return a Workbook object which is the passed to the CreateObject call which will raise an error because even if it pulls a default property from the object it's not going to be a valid object identifier. As written that code makes no sense. If moExcelWS Is Nothing Then Set moExcelWS = moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle, Len(ExSheetTitle) - 4)) End If That may or may not pick up the worksheet depending on your naming conventions for the workbook and worksheets. -- Reply to the group so all can participate VB.Net: "Fool me once..." |
#7
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls")
WB is NOT set The only reason that WB would not be set is that you have an On Error Resume Next and the file whose name is specified in the FileName parameter doesn't exist. In this case, the On Error Resume Next will breeze past the "file not found" error and WB will be Nothing. If the Open operation is successful, WB will be set to the Workbook object. If WB is not set, then an error occurred with the Open method (file not found, file in use, etc). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Jack" <replyto@it wrote in message ... Doing your way: Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") WB is NOT set I have found the following way which works, but I am not sure whether it is the right one: If FileExists(XLSheetFullTitle) = True Then Set moExcelWS = CreateObject(moExcelApp.Workbooks.Open(XLSheetFull PathTitle)) 'moExcelWS is not set but it loads the file ' then I do whats below and moExcelWS is set. If moExcelWS Is Nothing Then Set moExcelWS = moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle, Len(ExSheetTitle) - 4)) End If Jack "Chip Pearson" wrote in message ... Using the automation, how can I start Excel application with the specified spreadsheet title? Once you've got a reference to Excel, you can open up any file you want. Use code like the following: Dim WB As Excel.Workbook Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") where XLApp is a reference to the Excel Application object. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Jack" <replyto@it wrote in message ... Mike, Thank you very much for your reply. One more question: Using the automation, how can I start Excel application with the specified spreadsheet title? Jack "MikeD" wrote in message ... "Jack" <replyto@it wrote in message ... My code checks whether Excel app is open by using this line of code: Open_Excel_App: Debug.Print "Excel app NOT found!" On Error GoTo 0 rtn = ShellExecute(Me.hwnd, "Open", "excel.exe", vbNullString, App.Path, vbNormalFocus) Don't start Excel that way. Use Automation to start it. Here's a MUCH simpler way (using late-binding) to do what your wanting: Option Explicit Private moExcelApp As Object On Error Resume Next 'TEMPORARILY ignore errors Set moExcelApp = GetObject(, "Excel.Application") On Error GoTo EH 'Resume normal error handling If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") moExcelApp.Visible = True 'if you want Excel to be visible at this point End If On Error GoTo EH 'Resume normal error handling Exit Sub 'or Function as the case may be This gives you a reference to Excel without having to shell to it and using FindWindow and all the rest of the "garbage" you're doing to accomplish this. -- Mike Microsoft MVP Visual Basic |
#8
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
There is a slight problem though.
If the spreadsheet is already opened and the code tries to open it again Excel gets involved asking the question to reload the sheet or not. How to avoid that? How to check if the sheet is already opened? Jack "Bob Butler" wrote in message ... "Jack" <replyto@it wrote in message Doing your way: Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") WB is NOT set It is in every version of VB and Excel that I've ever worked with. That's the appropriate way to opena workbook and get a reference to it. Once you've done that you can get the worksheets as in: Set moExcelWS = WB.Worksheets(1) I have found the following way which works, but I am not sure whether it is the right one: If FileExists(XLSheetFullTitle) = True Then Set moExcelWS = CreateObject(moExcelApp.Workbooks.Open(XLSheetFull PathTitle)) 'moExcelWS is not set but it loads the file ' then I do whats below and moExcelWS is set. That would open the file and return a Workbook object which is the passed to the CreateObject call which will raise an error because even if it pulls a default property from the object it's not going to be a valid object identifier. As written that code makes no sense. If moExcelWS Is Nothing Then Set moExcelWS = moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle, Len(ExSheetTitle) - 4)) End If That may or may not pick up the worksheet depending on your naming conventions for the workbook and worksheets. -- Reply to the group so all can participate VB.Net: "Fool me once..." |
#9
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Run time '429' : ActveX component can't create object
You can use the following function to determine whether a workbook is open.
Function IsWorkbookOpen(WBName As String, Optional WBRef As Variant) As Boolean Dim WB As Workbook For Each WB In Application.Workbooks If (StrComp(WB.Name, WBName, vbTextCompare) = 0) Or _ (StrComp(WB.FullName, WBName, vbTextCompare) = 0) Then IsWorkbookOpen = True If IsMissing(WBRef) = False Then Set WBRef = WB End If Exit Function End If Next WB End Function You can pass to this function either the simply workbook name (e.g. "Book1.xls") or the complete file name (e.g., "C:\Test\Book1.xls"). It will return True if the workbook is open or False if the workbook is not open. If WBRef is not omitted and the workbook is open, WBRef will be set to reference the found workbook. For example: Dim WorkbookName As String Dim WB As Workbook WorkbookName = "C:\Book2.xls" If IsWorkbookOpen(WBName:=WorkbookName, WBRef:=WB) = True Then MsgBox "Workbook Is Open: " & WB.FullName Else MsgBox "Workbook: '" & WorkbookName & "' is not open." End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Jack" <replyto@it wrote in message ... There is a slight problem though. If the spreadsheet is already opened and the code tries to open it again Excel gets involved asking the question to reload the sheet or not. How to avoid that? How to check if the sheet is already opened? Jack "Bob Butler" wrote in message ... "Jack" <replyto@it wrote in message Doing your way: Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") WB is NOT set It is in every version of VB and Excel that I've ever worked with. That's the appropriate way to opena workbook and get a reference to it. Once you've done that you can get the worksheets as in: Set moExcelWS = WB.Worksheets(1) I have found the following way which works, but I am not sure whether it is the right one: If FileExists(XLSheetFullTitle) = True Then Set moExcelWS = CreateObject(moExcelApp.Workbooks.Open(XLSheetFull PathTitle)) 'moExcelWS is not set but it loads the file ' then I do whats below and moExcelWS is set. That would open the file and return a Workbook object which is the passed to the CreateObject call which will raise an error because even if it pulls a default property from the object it's not going to be a valid object identifier. As written that code makes no sense. If moExcelWS Is Nothing Then Set moExcelWS = moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle, Len(ExSheetTitle) - 4)) End If That may or may not pick up the worksheet depending on your naming conventions for the workbook and worksheets. -- Reply to the group so all can participate VB.Net: "Fool me once..." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activex component can't create object | New Users to Excel | |||
Error 429 ActiveX Component can't create object | Excel Programming | |||
error 429: ActiveX component can't create object | Excel Programming | |||
Runtime error '429': ActiveX component can't create object. HELP!! | Excel Programming | |||
activex component can't create object with SAP 6.20 | Excel Programming |