Run time '429' : ActveX component can't create object

[Jack]

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



..

 

[MikeD]

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.

 

[Jack]

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

 

[Chip Pearson]

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)

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

 

[Jack]

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(XLSheetFullPathTitle))
'>>>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

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)

 

[Bob Butler]

Doing your way:

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(XLSheetFullPathTitle))
'>>>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.

 

[Chip Pearson]

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]

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

 

[Chip Pearson]

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)

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