Got a question and need support on Active X error message

[Brent E]

Good morning,

I am using an Access 2003 on an XP Pro computer and using an Access module
and an object variable to control Excel and things seem to function w/ no
problems if Excel is open, but if Excel is closed, my code should test to see
if Excel is already open, if not module should launch Excel. This is my code

Dim OBJ As Object
On Error Resume Next
Set OBJ = GetObject(, "EXCEL.Application")
If ERR.Number <> 0 Then
ERR.Clear
Set OBJ = CreateObject("EXCEL.APPLICATION")
Else: MsgBox ERR.Description
End If
But I get an error saying "Runtime error 429, Active X component can't
create object." I went to microsoft webiste and looked up the error and
explanation is that "You tried to place an Active X control on a form at
design time or add a form to a project with an Active X control, but the
associated information in the registry could not be found."...."to correct
the problem, the information in the registry may have been deleeted or
corrupted. Reinstall the Active X control or contact the control vendor."
Problem is I don't recall every changing, installing, or messing w/ any
Active X controls. I also have included in my module's references "Microsoft
Active X Data Objects 2.1 Library " and "Microsoft ActiveX Data Objects
Multidimensional 2.7 Library". I am curious, do these at all conflict w/ each
other, must I encorporate a different Active X library, do I need to
reinstall Access, or do I need a service pack update? I am not sure how to
correct this problem. Any suggestions? Thanks. Cordially,

 

[Ken Snell [MVP]]

The code you posted is exactly what you are using? Change it to this:

Dim OBJ As Object
On Error Resume Next
Set OBJ = GetObject(, "EXCEL.Application")
If ERR.Number <> 0 Then
ERR.Clear
Set OBJ = CreateObject("EXCEL.APPLICATION")
End If

You don't need the "Else: MsgBox..." step when there is no error.

 

[Brent E]

Thanks. Actually I tried remming out the line and also deleting the line, but
I still get same error. Seems to be something wrong w/ my Active X control in
registry or something. Any ideas? Thanks.

 

[Ken Snell [MVP]]

On which code line do you get the error? When the error message box shows
up, click "Debug" button and see which line is highlighted in yellow.

 

[Brent E]

I found that when I click on Debug, the error occurs in line:
Set OBJ = GetObject(, "EXCEL.Application")
but as far as I know there isn't anything wrong w/ this line, and it works
just fine if Excel is open. Only errors when Excel is closed. What do u think
about this? Thanks.

 

[Ken Snell [MVP]]

That line should error when EXCEL is not open. That is the reason for the
On Error Resume Next
line in the code -- the code should not break and should instead continue to
the next code line.

Thus, I'm thinking that you need to change a setting in Visual Basic Editor.
Open VBE, and click Tools | Options | General tab. Be sure that the option
for "Break on Unhandled Errors" is the option that is set on the right side
of the window.

--

Ken Snell
<MS ACCESS MVP>

 

[Dave Patrick]

Something like this may also work.

set list = getobject("winmgmts:").execquery(_
"select * from win32_process where name='excel.exe'")
If list.count > 0 Then
msgbox "excel is running"
Else
msgbox "excel is not running"
End If

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I found that when I click on Debug, the error occurs in line:
| Set OBJ = GetObject(, "EXCEL.Application")
| but as far as I know there isn't anything wrong w/ this line, and it works
| just fine if Excel is open. Only errors when Excel is closed. What do u
think
| about this? Thanks.

 

[Brent E]

Good morning guys. Thanks. I just got your messages. I appreciate u guys
helpin me out on this. Ken, I checked my setting as u suggested. Yes, my
settings were set to stop on all errors, I changed that to only on unhandled
errors and no I do not get an error, but if Excel is closed, Excel does not
seem to open even w/ the following additions to my code. I thought maybe
Excel was opening in background and I just couldn't see it, but when I went
into task manager and looked at applications and processes running, there are
no representations of Excel that I can see. What do u suggest? Thanks

And Dave, thanks for that terrific code suggestion. If I can't get sub I
already got going to work, I will definitely give that a try. I didn't know u
could use a win32 process like that. Very cool. What are the winmgmts and
execquery processes. I've not known I could use those before and would like
to know more about those. Thanks again.

 

[Brent E]

I forgot to insert my code into my last post. These are updates to my code:
Dim OBJ As Object
On Error Resume Next
Set OBJ = GetObject(, "EXCEL.Application")
If ERR.Number <> 0 Then
ERR.Clear
Set OBJ = CreateObject("EXCEL.APPLICATION")
End If
OBJ.Visible True
OBJ.Workbooks.Add
But I still do not see Excel open or open a workbook.

 

[Dave Patrick]

A couple of links to get you started.

http://www.microsoft.com/technet/scriptcenter/default.mspx
http://www.microsoft.com/technet/scriptcenter/resources/wmifaq.mspx

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Good morning guys. Thanks. I just got your messages. I appreciate u guys
| helpin me out on this. Ken, I checked my setting as u suggested. Yes, my
| settings were set to stop on all errors, I changed that to only on
unhandled
| errors and no I do not get an error, but if Excel is closed, Excel does
not
| seem to open even w/ the following additions to my code. I thought maybe
| Excel was opening in background and I just couldn't see it, but when I
went
| into task manager and looked at applications and processes running, there
are
| no representations of Excel that I can see. What do u suggest? Thanks
|
| And Dave, thanks for that terrific code suggestion. If I can't get sub I
| already got going to work, I will definitely give that a try. I didn't
know u
| could use a win32 process like that. Very cool. What are the winmgmts and
| execquery processes. I've not known I could use those before and would
like
| to know more about those. Thanks again.

 

[Ken Snell [MVP]]

Code looks fine. So let's see what might be happening. Insert a step to put
up a msgbox with error number and description after CreateObject...let's see
if there is a problem there for some reason.

Dim OBJ As Object
On Error Resume Next
Set OBJ = GetObject(, "EXCEL.Application")
If ERR.Number <> 0 Then
ERR.Clear
Set OBJ = CreateObject("EXCEL.APPLICATION")
MsgBox "error " & err.number & " : " & err.description
End If
OBJ.Visible True
OBJ.Workbooks.Add

--

Ken Snell
<MS ACCESS MVP>

I forgot to insert my code into my last post. These are updates to my code:
Dim OBJ As Object
On Error Resume Next
Set OBJ = GetObject(, "EXCEL.Application")
If ERR.Number <> 0 Then
ERR.Clear
Set OBJ = CreateObject("EXCEL.APPLICATION")
End If
OBJ.Visible True
OBJ.Workbooks.Add
But I still do not see Excel open or open a workbook.

Something like this may also work.

set list = getobject("winmgmts:").execquery(_
"select * from win32_process where name='excel.exe'")
If list.count > 0 Then
msgbox "excel is running"
Else
msgbox "excel is not running"
End If

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I found that when I click on Debug, the error occurs in line:
| Set OBJ = GetObject(, "EXCEL.Application")
| but as far as I know there isn't anything wrong w/ this line, and it
works
| just fine if Excel is open. Only errors when Excel is closed. What do u
think
| about this? Thanks.

 

[Dave Patrick]

Try something like this.

Dim OBJ As Object, list As Object
Set list = GetObject("winmgmts:").execquery("select " _
& "* from win32_process where name='excel.exe'")
If list.Count > 0 Then
MsgBox "excel is running"
Else
MsgBox "excel is not running"
Set OBJ = CreateObject("EXCEL.APPLICATION")
OBJ.Workbooks.Add
OBJ.Application.Visible = True
End If


--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I forgot to insert my code into my last post. These are updates to my code:
| Dim OBJ As Object
| On Error Resume Next
| Set OBJ = GetObject(, "EXCEL.Application")
| If ERR.Number <> 0 Then
| ERR.Clear
| Set OBJ = CreateObject("EXCEL.APPLICATION")
| End If
| OBJ.Visible True
| OBJ.Workbooks.Add
| But I still do not see Excel open or open a workbook.

 

[Steven M. Britton]

This is what I use and have never had a problem...

Function SendtoExcel()

Dim xlsApp As Object
Dim wkb As Object
Dim strPath As String
Dim wkbTemp As Object
Dim wks As Object
On Error Resume Next
Set xlsApp = GetObject("Excel.application")
If Err Then
Set xlsApp = CreateObject("Excel.Application")
End If
xlsApp.Activate
xlsApp.Visible = False
strPath = "C:\file\temp.xls"
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query", strPath
DoCmd.SetWarnings True
Set wkbTemp = xlsApp.Workbooks.Open(strPath)

xlsApp.Visible = True

End Function

 

[Brent E]

Terrific. Thanks guys. I am sorry I didn't respond sooner, I got called away
to another office to assist w/ a program. I will give all of your suggestions
a try and keep u posted tomorrow. Thanks again guys, have a terrific evening.

This is what I use and have never had a problem...

Function SendtoExcel()

Dim xlsApp As Object
Dim wkb As Object
Dim strPath As String
Dim wkbTemp As Object
Dim wks As Object
On Error Resume Next
Set xlsApp = GetObject("Excel.application")
If Err Then
Set xlsApp = CreateObject("Excel.Application")
End If
xlsApp.Activate
xlsApp.Visible = False
strPath = "C:\file\temp.xls"
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query", strPath
DoCmd.SetWarnings True
Set wkbTemp = xlsApp.Workbooks.Open(strPath)

xlsApp.Visible = True

End Function

Try something like this.

Dim OBJ As Object, list As Object
Set list = GetObject("winmgmts:").execquery("select " _
& "* from win32_process where name='excel.exe'")
If list.Count > 0 Then
MsgBox "excel is running"
Else
MsgBox "excel is not running"
Set OBJ = CreateObject("EXCEL.APPLICATION")
OBJ.Workbooks.Add
OBJ.Application.Visible = True
End If


--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I forgot to insert my code into my last post. These are updates to my code:
| Dim OBJ As Object
| On Error Resume Next
| Set OBJ = GetObject(, "EXCEL.Application")
| If ERR.Number <> 0 Then
| ERR.Clear
| Set OBJ = CreateObject("EXCEL.APPLICATION")
| End If
| OBJ.Visible True
| OBJ.Workbooks.Add
| But I still do not see Excel open or open a workbook.

 

[Brent E]

Good morning guys, I looked at everybody's suggestions and was able to get
the procedure to work. I assume I would use similar code to test to see if
Word is open and if not to run Word also? Thanks for all your terrific
assistance. I really appreciate your time.

This is what I use and have never had a problem...

Function SendtoExcel()

Dim xlsApp As Object
Dim wkb As Object
Dim strPath As String
Dim wkbTemp As Object
Dim wks As Object
On Error Resume Next
Set xlsApp = GetObject("Excel.application")
If Err Then
Set xlsApp = CreateObject("Excel.Application")
End If
xlsApp.Activate
xlsApp.Visible = False
strPath = "C:\file\temp.xls"
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query", strPath
DoCmd.SetWarnings True
Set wkbTemp = xlsApp.Workbooks.Open(strPath)

xlsApp.Visible = True

End Function

Try something like this.

Dim OBJ As Object, list As Object
Set list = GetObject("winmgmts:").execquery("select " _
& "* from win32_process where name='excel.exe'")
If list.Count > 0 Then
MsgBox "excel is running"
Else
MsgBox "excel is not running"
Set OBJ = CreateObject("EXCEL.APPLICATION")
OBJ.Workbooks.Add
OBJ.Application.Visible = True
End If


--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I forgot to insert my code into my last post. These are updates to my code:
| Dim OBJ As Object
| On Error Resume Next
| Set OBJ = GetObject(, "EXCEL.Application")
| If ERR.Number <> 0 Then
| ERR.Clear
| Set OBJ = CreateObject("EXCEL.APPLICATION")
| End If
| OBJ.Visible True
| OBJ.Workbooks.Add
| But I still do not see Excel open or open a workbook.

 

[Ken Snell [MVP]]

Yes.

--

Ken Snell
<MS ACCESS MVP>

Good morning guys, I looked at everybody's suggestions and was able to get
the procedure to work. I assume I would use similar code to test to see if
Word is open and if not to run Word also? Thanks for all your terrific
assistance. I really appreciate your time.

This is what I use and have never had a problem...

Function SendtoExcel()

Dim xlsApp As Object
Dim wkb As Object
Dim strPath As String
Dim wkbTemp As Object
Dim wks As Object
On Error Resume Next
Set xlsApp = GetObject("Excel.application")
If Err Then
Set xlsApp = CreateObject("Excel.Application")
End If
xlsApp.Activate
xlsApp.Visible = False
strPath = "C:\file\temp.xls"
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query",
strPath
DoCmd.SetWarnings True
Set wkbTemp = xlsApp.Workbooks.Open(strPath)

xlsApp.Visible = True

End Function

Try something like this.

Dim OBJ As Object, list As Object
Set list = GetObject("winmgmts:").execquery("select " _
& "* from win32_process where name='excel.exe'")
If list.Count > 0 Then
MsgBox "excel is running"
Else
MsgBox "excel is not running"
Set OBJ = CreateObject("EXCEL.APPLICATION")
OBJ.Workbooks.Add
OBJ.Application.Visible = True
End If


--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I forgot to insert my code into my last post. These are updates to my
code:
| Dim OBJ As Object
| On Error Resume Next
| Set OBJ = GetObject(, "EXCEL.Application")
| If ERR.Number <> 0 Then
| ERR.Clear
| Set OBJ = CreateObject("EXCEL.APPLICATION")
| End If
| OBJ.Visible True
| OBJ.Workbooks.Add
| But I still do not see Excel open or open a workbook.