Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2444
  • Last Modified:

Microsoft Access VBA The interface is unknown

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
Chuck Wood
Asked:
Chuck Wood
  • 9
  • 6
  • 4
  • +3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Is Excel installed?

Jim.
0
 
Chuck WoodAuthor Commented:
Yes, Excel  is installed and it shows up and is selected in the References.
0
 
Rey Obrero (Capricorn1)Commented:
try this

Public Function TestExcel()
    Dim xl as object
    set xl=createobject("excel.application")
    xl.workbooks.add
    xl.Visible = True
End Function
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
BembiCEOCommented:
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
 
Dale FyeCommented:
no points please.

I use the same technique Rey used above.
0
 
Rey Obrero (Capricorn1)Commented:
try closing the access app and reopen, then run the codes.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
WalkaboutTiggerCommented:
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
 
Chuck WoodAuthor Commented:
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
 
WalkaboutTiggerCommented:
As silly as this sounds, deselect it and then reselect it.
0
 
Chuck WoodAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Rey Obrero (Capricorn1)Commented:
try uninstalling Office, reboot and reinstall/
0
 
Chuck WoodAuthor Commented:
Jim Dettman:
I ran the Office repair and rebooted but the problem still exists.
0
 
Rey Obrero (Capricorn1)Commented:
will it be too much to uninstall/remove Office, reboot then install ?
0
 
Chuck WoodAuthor Commented:
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
 
WalkaboutTiggerCommented:
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
 
Chuck WoodAuthor Commented:
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
 
WalkaboutTiggerCommented:
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
 
Chuck WoodAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Chuck WoodAuthor Commented:
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
 
WalkaboutTiggerCommented:
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
 
Chuck WoodAuthor Commented:
Jim Dettman:
I have created a new question, which I will use to award you the points.
0
 
WalkaboutTiggerCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now