Solved Compile Error When Clicking on Button

[JMan_1018]

Hello All,

I have had this Access database for many years and now when I click on a button which opens Excel, renames the Excel file and saves it in a specific folder. Then if the button is clicked on again, it reopens the file again. Now (I believe after an update) I get this "Compile error: User-defined type not defined". Again, this button has worked for many years. How should I define this Excel.Application (see attached file)? Why all of a sudden, it's not working?

Thanks,
Jody

 

[MajP]

Check your refereces in vba. During an update it could have broke a reference or lost the reference to Excel. If you have issues still, take a screen shot of your references.

 

[MajP]

Even if you have the Excel reference if there is a broken reference it will never get to that library.

 

[JMan_1018]

Even if you have the Excel reference if there is a broken reference it will never get to that library.

View attachment 115601

My Microsoft Excel 16.0 Object Library was unchecked. I checked it and it is working. Any ideas how it would have gotten unchecked? Can an update do that?

 

[MajP]

Can an update do that?

I believe so. I know of no other way you could lose a reference

 

[Isaac]

Did you recently move to a new version of Excel? Or was it reinstalled

 

[Eugene-LS]

Any ideas how it would have gotten unchecked?

No any, but sometimes it's more reliable to use "late binding"
Reference to "Microsoft Excel 16.0 Object Library" not needed

Try:

Dim objExcelApp As Object
Dim objWrkBk As Object, objWrkSht As Object

    Set objExcelApp = CreateObject("Excel.Application")
    Set objWrkBk = objExcelApp.Workbooks.Add
    Set objWrkSht = objWrkBk.ActiveSheet

    objExcelApp.Visible = True

    Set objWrkSht = Nothing
    Set objWrkBk = Nothing
    Set objExcelApp = Nothing

 

[The_Doc_Man]

Can an update do that?

Yes, sometimes the reference gets lost because there is an internal pointer that maybe looks like a GUID - but if the library changes due to being updated, the old pointer suddenly becomes useless because the update method replaces the file in some way.

Note also that it is possible that your problem COULD have been another broken reference that appeared in the list before the Excel reference, but because symbol resolution is in order of the list, that broken reference stops (and fails) the search. This has happened to me a few times because I had a bunch of references in an app that used Word, Outlook, and Excel app objects at one time or another, plus scripting and a couple of other oddities.

 

[Gasman]

No any, but sometimes it's more reliable to use "late binding"
Reference to "Microsoft Excel 16.0 Object Library" not needed

Try:

Dim objExcelApp As Object
Dim objWrkBk As Object, objWrkSht As Object

    Set objExcelApp = CreateObject("Excel.Application")
    Set objWrkBk = objExcelApp.Workbooks.Add
    Set objWrkSht = objWrkBk.ActiveSheet

    objExcelApp.Visible = True

    Set objWrkSht = Nothing
    Set objWrkBk = Nothing
    Set objExcelApp = Nothing

Bit over the top for the sake of a missing reference, surely.
Late binding, you do not get intellisense or the application enums?

 

[Eugene-LS]

Late binding, you do not get intellisense or the application enums?

Yes.
As an option: Write code at "early binding" and use it at "late binding".

 

[Isaac]

I second the motion. Design in early binding until you've almost memorized your heavy hitters (a quick google of xlDown will tell you if it's -4142, etc, also) - but then deploy in late binding.

I would only recommend changing
Set objWrkSht = objWrkBk.ActiveSheet
to this
Set objWrkSht = objWrkBk.worksheets(1) 'or anything you want that's a sure thing