Open Excel from Access the interface is unknown error

When I run the VBA code in the three test functions below, I get a "the interface is unknown" error in the first two and an "object required" error in the third. Microsoft Excel 14.0 Object Library is selected in the database References and I have deselected it and selected it again. I have disabled all add-ins, reregistered Excel, and shut down the computer and rebooted it. The first function works fine on a second computer. How can I fix this problem?

=== VBA code ===
Option Compare Database
Option Explicit

Public Function TestExcel1()
    Dim xl As New Excel.Application
    xl.Visible = True
End Function

Public Function TestExcel2()
    Dim xl As Excel.Application
    Set xl = New Excel.Application
    xl.Visible = True
End Function

Public Function TestExcel3()
    Dim xl As Object
    Set xl = CreateObject("EXCEL.APPLICATION")
    xl.Visible = True
End Function</code>
LVL 16
Chuck WoodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
This is the code that I generally use to open Excel.  This gets the currently open instance of Excel if there is one, and opens a new one if one is not already open.
I also create three global objects which an be accessed from all of my Excel functions and subroutines.

Global xl As Object     'Excel.Application  '
Global wbk As Object    'Excel.Workbook    '
Global sht As Object    'Excel.Worksheet   '

Public Sub GetExcelApp()

    On Error GoTo ProcError
    
    TempVars!ExcelWasOpen = True
    Set xl = GetObject(, "Excel.Application")
    
ProcExit:

    Exit Sub
ProcError:
    TempVars!ExcelWasOpen = False
    If Err.Number = 429 Then
        Set xl = CreateObject("Excel.Application")
        Resume Next
    Else
        Debug.Print Err.Number, Err.Description
        Resume ProcExit
    End If

End Sub

Open in new window

Chuck WoodAuthor Commented:
Dale Fye-
Thank you for your response.
I received an "object required" error on line 18 (Set xl = CreateObject("Excel.Application")).
Dale FyeOwner, Developing Solutions LLCCommented:
why did you add ( ) around that line?  In my code, it reads:
Set xl = CreateObject("Excel.Application")

Open in new window

in your message above you have an extra set of ( )
(Set xl = CreateObject("Excel.Application"))

Open in new window

if you are still getting an error with that, then you might want to try my code in a new database, the one you are working with may be corrupted.  With that code, you will not need any references to Excel in your database.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also be sure to UNCHECK the Excel reference, if you use Dale's code. As he mentions, you do not need a reference to Excel if you use late binding.
Chuck WoodAuthor Commented:
Dale Fye-
The extra parentheses were just to reinforce what was on line 18. I did not put them in the code I ran.

Scott McDaniel-
I unchecked the Excel reference and ran the code again but received the same error on line 19.
Dale FyeOwner, Developing Solutions LLCCommented:
did you try putting that code (just the code I posted) in a new database?

If you do that, I would add another line:

xl.Visible = true

on line 11, to make sure the instance of Excel is visible.  Then call the subroutine from the immediate window.
Chuck WoodAuthor Commented:
I tried that (see the code below) and I received an "object required" error on the previous line 18 (now line 19). The code does not get to xl.Visible = True as it errors into ProcError on line 9 with error number 429 "object required."

Option Compare Database
Option Explicit
Global xl As Object     'Excel.Application  '
Global wbk As Object    'Excel.Workbook    '
Global sht As Object    'Excel.Worksheet   '

Public Sub GetExcelApp()

    On Error GoTo ProcError
   
    TempVars!ExcelWasOpen = True
    Set xl = GetObject(, "Excel.Application")
    xl.Visible = True
   
ProcExit:

    Exit Sub
ProcError:
    TempVars!ExcelWasOpen = False
    If Err.Number = 429 Then
        Set xl = CreateObject("Excel.Application")
        Resume Next
    Else
        Debug.Print Err.Number, Err.Description
        Resume ProcExit
    End If

End Sub
Dale FyeOwner, Developing Solutions LLCCommented:
cwood, with the error handler in place, the code should jump to ProcError when it fails to find an open Excel Application, and should then jump to the error handler.  Put a breakpoint on the line that reads:

Tempvars!ExcelWasOpen = False

and then step through the code to see whether it is being handled properly.

Dale
Chuck WoodAuthor Commented:
Dale Fye-
I put a breakpoint on the line TempVars!ExcelWasOpen = False and stepped through the code and the code had a "object required" error on line 19, just as it did before.
Dale FyeOwner, Developing Solutions LLCCommented:
so it never jumped to the error handler?

This was in a new database, with nothing else in it?
Chuck WoodAuthor Commented:
This is a new database with nothing else in it and it did jump to the error handler. That is why it threw the error on line 19, which is in the error handler.
Helen FeddemaCommented:
Here is my boilerplate code for getting the current instance of Excel, if there is one, or creating a new one:

   Set appExcel = GetObject(, "Excel.Application")

   'Your code here

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   'Excel is not running; open Excel with CreateObject
   If Err.Number = 429 Then
      Set appExcel = CreateObject("Excel.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in " & Me.ActiveControl.Name & " procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

Open in new window

appExcel is declared as Excel.Application, and there is a reference to the current version of Excel.
Chuck WoodAuthor Commented:
Helen Feddema-
With he exception of the name of the object (appExcel instead of xl), your code looks the same as the code Dale Fye provided, which created the "object required" error. The problem is that the code is producing the error on one computer but is working correctly on another computer.
Dale FyeOwner, Developing Solutions LLCCommented:
Have you tried to repair office on the affected computer?
Chuck WoodAuthor Commented:
Dale Fye-
No. I will try that.
Chuck WoodAuthor Commented:
Dale Frye-
I repaired Office but I am still getting the same error.
Helen FeddemaCommented:
That is what I was going to suggest.  The next step would be to uninstall Office, then reinstall it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Did you also make sure your Windows installation is fully up to date, with all required patches/hotfixes?
Chuck WoodAuthor Commented:
Helen Feddema-
I have asked our IT support to reinstall Office.

Scott McDaniel-
My Windows is kept up to date by our corporate IT. Another computer with the same Windows does not have this problem.
Chuck WoodAuthor Commented:
Reinstalling Office is the correct solution. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.