Access 2007 Conversion Issue

[Jeff Hunt]

Our company is getting ready to upgrade to Office 2007 so we have been going
through all our files with VBA or Macros to make sure they will work. So far
we have found only one that is failing, but it is beyond me why it is. One
of my coworkers has a database that uses a VBA event to open an Excel file, I
believe to make a few changes to the columns and then import into the
database. The command she is using to open the file is one we have used
successfully before:

Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")

It is failing on the "Set xl" command with the following error:

Run-time error '429':
ActiveX component can't create object.

Sounded to me like a problem with the references, but I've played with
several to no avail. The references on this database are as follows and in
this order:

- Visual Basic for Applications
- Microsoft Access 12.0 Object Library
- OLE Automation
- Microsoft DAO 3.6 Object Library
- Microsoft Excel 11.0 Object Library
- Microsoft ActiveX Data Objects 2.8 Library (used to be 2.1)
- Microsoft Office 12.0 Object Library

Anyone have experience with this error after converting from Access 2003 to
Access 2007 that can shed some light on the subject? Thanks!

 

[Dale Fye]

Your references show Excel 11.0 (Office 2003), not Excel 12.0 (Office 2007)

You should probably change the reference, but the way I work around this is
that after I have my automation code running, I change all of my declaration
statements to declare the variable (xl, wks, wbk, sheet, ....) as an object
rather than a specific type of object. Then the Set statement will assign it
the appropriate object type.

Dim xl as Object 'Excel.Application

With this technique, Access will use the appropriate version of Excel,
rather than looking for a specific version.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Jeff Hunt]

I updated the Excel reference to 12, and changed the Dim line like you
suggested. Unfortunately it still produced the same error. I've tried:

Set xl = CreateObject("Excel.Application")
and
Set xl = New Excel.Application

but they both give the same error. The original file was an Access 2003
version, but I've tried making a new 2007 file with nothing but this code in
it and it still fails. Any other ideas?

 

[Dale Fye]

What error message are you getting?

Without knowing what error you are getting I don't have a clue. What you
are using looks similiar to what I use

I generally use something like the following. This first checks to see
whether Excel is already open, if so it uses that instance of Excel and sets
bExcelOpen to true. Otherwise, it opens a new instance of Excel, and sets
bExcelOpen to false. I then use the bExcelOpen to determine whether to close
Excel at the end of my code.

dim bExcelOpen as boolean
On Error Resume Next
Set xl = GetObject("Excel.Application")
If Err.Number = 0 then
bExcelOpen = true
else
Set xl = CreateObject("Excel.Application")
bExcelOpen = false
endif
On Error Goto XXXXX

You really don't even need the reference to Excel, once your code is
working. What I generally do is add the reference to Excel, then use
explicit typing in my declaration statements (this provides me with
intellisense as I'm developing). Once my code is working, then I change all
of the declarations to Excel objects to Object and remove the reference to
Excel, which gives you the advantage that the code will now run on whichever
version of Office you are using.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Jeff Hunt]

Still getting the same error as I mentioned in my first post:

Run-time error '429':
ActiveX component can't create object.

I'll take a look at your code and see if it helps any either. Interesting
way that you used the different error handlers to check for the open status.
I don't think I've seen it used that way before.

 

[Dale Fye]

Jeff,

You might want to consider posting a message to the Office automation group
(microsoft.public.office.developer.automation). They tend to be the guys and
gals that are doing cross application development, and one of them may have
an idea.

I'd be more specific in your subject line though. Something like:

CreateObject("Excel.Application") generates ActiveX error

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.