Solved

Microsoft Access VBA The interface is unknown

Posted on 2014-04-29
26
2,068 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 119

Expert Comment

by:Rey Obrero
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
 
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 119

Expert Comment

by:Rey Obrero
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now