Solved

Microsoft Access VBA The interface is unknown

Posted on 2014-04-29
26
2,177 Views
Last Modified: 2014-05-03
Reference:
Microsoft Excel 14.0 Object Library

Using this reference, the code below produces the error "The interface is unknown" on the second line of code using Windows 7, Office 14 and Internet Explorer 9. The code runs fine on a second computer with Windows 7, Office 14 and Internet Explorer 8. How can I fix this problem?

Public Function TestExcel()
    Dim xl As New Excel.Application
    xl.Visible = True
End Function
0
Comment
Question by:Chuck Wood
  • 9
  • 6
  • 4
  • +3
26 Comments
 
LVL 57
ID: 40030648
Is Excel installed?

Jim.
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40030674
Yes, Excel  is installed and it shows up and is selected in the References.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40030696
try this

Public Function TestExcel()
    Dim xl as object
    set xl=createobject("excel.application")
    xl.workbooks.add
    xl.Visible = True
End Function
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Bembi
ID: 40030700
You define an object variable as a Excel object.
But the xl.Visible = True would assume, that excel is running,
This is what I' missing in you code.
As you stated that the machines are equal (IE doesn't play a role), is it possible that you ested the code with excel running while on the other machine it is not started?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40030703
no points please.

I use the same technique Rey used above.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40030707
try closing the access app and reopen, then run the codes.
0
 
LVL 57
ID: 40030728
<<Yes, Excel  is installed and it shows up and is selected in the References. >>

 I should have been clearer; certainly it was installed at one time as the reference is there, but is it still installed and working?   Can you open Excel manually and work with it?

More than likely, the registry is fouled up and you'll need to run a repair on Office.  I'd also check your AV and make sure none of the Office files have been quarantined.

Jim.
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 40030735
On the ribbon, select Database Tools then select Visual Basic

The Visual Basic editor will open.

From the menu, select Tools then select References.

Scroll down and ensure Microsoft Excel 14.0 Object Library is selected.
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40030903
Thank you all for your suggestions.

Ray Obrero:
1. I tried set xl=createobject("excel.application") and it did still has the same error.
2. I have restarted the Access application several times and the code still errors.

Bembi: Dim xl As New Excel.Application starts Excel. I test the code on both machines with Excel not running. The declaration of xl as a new Excel Application starts Excel.

Jim Dettman: Yes, I use Excel regularly and it is working. I will try to repair Office and see if that fixes the registry.

WalkaboutTigger: I have checked the References in the code window and the Microsoft Excel 14.0 Object Library is selected.
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 40030956
As silly as this sounds, deselect it and then reselect it.
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40030967
WalkaboutTigger:
Good suggestion. I did that when I tried xl=createobject("excel.application") and then went back to Dim xl As New Excel.Application. It still doesn't work. :-)
0
 
LVL 57
ID: 40030983
<<As silly as this sounds, deselect it and then reselect it. >>

That's not silly; it forces VBA to refresh its reference collection.  Often fixes many problems.  

But it sounds like were further along than that.  In some way, the automation interface to Excel is broken.

Jim.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40030984
try uninstalling Office, reboot and reinstall/
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40032193
Jim Dettman:
I ran the Office repair and rebooted but the problem still exists.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40032539
will it be too much to uninstall/remove Office, reboot then install ?
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40032575
Rey Obrero:
I saw your earlier comment and I will probably do that but I will have to wait a week and a half to have our IT folks do that as I cannot have my computer down for that long during that time. I don't think I will get better advice so I am accepting your solution.
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 40032697
I realize you already selected a solution, and am not concerned about that, but one thing you can also check is see if you are getting any DCOM errors in the System or Application event logs.  They may point you to an underlying issue that is actually causing this problem.
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40032787
WalkaboutTigger:
Thank you for the suggestion. I triggered the problem several times (ran the code) and, unfortunately, there were no DCOM errors in the System or Application event logs.
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 40033384
I would like you to try something.

Exit all other applications and make certain ACCESS.EXE and EXCEL.EXE are not running in Task Manager.

From an elevated command prompt, go to the directory where the Excel.exe file is and type

.\Excel.exe /regserver

This will re-register Excel and its components.
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40033443
WalkaboutTigger:
Thanks for continuing to try on this issue. I did run the following as an Administrator in a command prompt but it did not fix the problem.
"C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe" /regserver
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40036928
Please try the following:

Option Compare Database
Option Explicit

Public Function TestExcel()
  Dim xl As Object

  On Error GoTo TestExcel_Error

  Set xl = CreateObject("Excel.Application")
  xl.Visible = True

  MsgBox "Excel started - Ready to Exit"
 
TestExcel_Exit:
  On Error Resume Next

  xl.Quit
  Set xl = Nothing

  Exit Function

TestExcel_Error:
  MsgBox Err.Number & " - " & Error.Description
  Resume TestExcel_Exit

End Function

 Do you get any errors?

 Also, are there any add-ins defined in Excel?  You might want to try disabling them.

Jim.
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40037297
Jim Dettman:

I copied the code into a new Access module and received Run-time error '424': Object required. I  then tried disabling COM add-ins and found disabling "EXCEL and EQD COM Addin" fixed the problem. How can I award point to you?
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 40038197
There should be an "ask Moderator" or similar button on the "Post a comment" form for you, cword, though it may be too late considering the ticket is closed.
0
 
LVL 16

Author Comment

by:Chuck Wood
ID: 40038217
Jim Dettman:
I have created a new question, which I will use to award you the points.
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 40039434
And just so you know, cwood, this is how this site is supposed to work.

I won't give up on a question until it is either solved or abandoned.  Many here feel the same way.  It isn't just for you, but for everyone for whom this solution helps.

Respectfully,

WT
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question