Access Compile Error Type Mismatch

I'm using the code below and get a type mismatch compile error on :
"(C:\Program Files (x86)\Microsoft Office\Office16\Excel.exe)"





Sub RemoveReferences()

'try to Remove a reference to Excel
On Error GoTo CanNotRemoveExcel
Application.VBE.ActiveVBProject.References.Remove "(C:\Program Files (x86)\Microsoft Office\Office16\Excel.exe)"



Exit Sub
CanNotRemoveExcel:
MsgBox "Can not reference Excel"
End Sub

Open in new window

shieldscoAsked:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try removing the parentheses ...

That said - removing a reference via code can cause all sorts of issues. What exactly are you trying to do?
shieldscoAuthor Commented:
same issue
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My bad ... been a looooong time since I even looked at that code.

References.Remove expects a Reference as the argument, so you'd have to loop through and compare the FullPath to each Reference:

Dim ref As Access.Reference
For Each ref In Application.References
    If UCase(ref.FullPath) = UCase("Your full file location") Then
        Application.References.Remove ref
        Exit For
    End If
Next ref

Still, it's always a bad idea to remove references at runtime. If you're trying to manage mismatched references on your enduser machines, there are much, much better ways.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
You can remove it this way:

Application.References.Remove Application.References("Excel")

Open in new window

mbizupCommented:
Take a look at this article about Early Binding vs Late Binding:

Early vs Late Binding in VBA

Late Binding is not version specific, so if you implement it in your code, you do not need to worry about changing references to reflect different versions of products.  The code will work regardless.

The article was written with a target audience of Excel developers automating Outlook, but the same principle applies when automating Excel from Access.

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
shieldscoAuthor Commented:
compile error can't find project or library

Dim ref As Access.Reference
 For Each ref In Application.References
     If UCase(ref.FullPath) = UCase("C:\Program Files (x86)\Microsoft Office\Office14\excel.exe") Then
         Application.References.Remove ref
         Exit For
     End If
 Next ref
shieldscoAuthor Commented:
runtime error 48 loading dll

Application.References.Remove Application.References("Excel")
Gustav BrockCIOCommented:
Then your code and/or file is corrupted.
If Excel is not referenced, the error will be 9: Subscript out of range
shieldscoAuthor Commented:
Neither the file or the code is corrupt
Gustav BrockCIOCommented:
Then it is the Office install. That loading error is not related to this piece code.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
compile error can't find project or library
What version of Access? I'm using 2016, and the code runs as expected. I added an Excel reference, modified the path so it pointed to the correct location on my machine, and ran my code. I worked as expected, with no errors, and removed the reference.

I also tested Gustav's code, and it worked as expected - it removed the reference, and did not produce an error.

As Gustav has indicated, something else is going on with your application.
shieldscoAuthor Commented:
Some users are running 2010 and others 2016
shieldscoAuthor Commented:
I went with late binding which solved my different version issues.
shieldscoAuthor Commented:
Thanks to all
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.