Solved

Microsoft Access VBA The interface is unknown

Posted on 2014-04-29
26
2,301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 4
  • +3
26 Comments
 
LVL 58
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 48

Expert Comment

by:Dale Fye
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 58
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 58
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 58

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

636 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