Solved Compile error : User-defined type not defined (Dim xlApp As Excel.Application), due to problem with 'Microsoft Excel 16.0 Object Library' Reference (1 Viewer)

[alan2013]

In recent days, a colleague sent me an Access database to make some changes to, and I did, and sent it back to him. On opening it, my colleague encountered a compile error :

Compile error : Can't find project or library

On looking at the 'References' list (my suggestion), he saw amongst the list 'MISSING : Microsoft Excel 16.0 Object Library' (with 'Location' C:\Program Files (x86)\Microsoft Office\root\Office 16\EXCEL.EXE). I suggested that he un-tick that Reference, and he did.

That seemed to resolve the "Can't find project or library" compile error. However, now he's getting another compile error :

Compile error : User-defined type not defined

And that compile error points to the following line in the VBA code :

Dim xlApp As Excel.Application

So I take it that's due to the 'Microsoft Excel 16.0 Object Library' Reference having been un-ticked (see above).

It now transpires that there had been a miscommunication : I hadn't realized that the database I had been sent was Access 2013. I worked on it in Access 2021. That may have triggered these problems, I suppose.

Excuse my ignorance of these things, but : How can this be fixed now, please ?
TIA

 

[theDBguy]

Since you're using early binding and your colleague has an earlier version of Office, they simply need to add a reference to their version of Excel.

 

[alan2013]

Since you're using early binding and your colleague has an earlier version of Office, they simply need to add a reference to their version of Excel.

Excuse my further ignorance of early binding, Refereences, etc, DBGuy, but exactly what does he have to do, technically ? Reinstate the 'Microsoft Excel 16.0 Object Library' Reference that was previously un-ticked? (Won't that re-introduce the other problem that he initially encountered ?)

 

[theDBguy]

Excuse my further ignorance of early binding, Refereences, etc, DBGuy, but exactly what does he have to do, technically ? Reinstate the 'Microsoft Excel 16.0 Object Library' Reference that was previously un-ticked? (Won't that re-introduce the other problem that he initially encountered ?)

Your friend cannot readd the reference to Excel 16, because it doesn't exist in their system; hence, it's marked as missing. Instead, they have to add a reference to the version of Excel that is on their system. It could be Excel 12.0 or 14.0 or 15.0, etc.

 

[alan2013]

Your friend cannot readd the reference to Excel 16, because it doesn't exist in their system; hence, it's marked as missing. Instead, they have to add a reference to the version of Excel that is on their system. It could be Excel 12.0 or 14.0 or 15.0, etc.

So I need to instruct him to go to References, take a look down through the list of Available References, and tick whatever is present in the list where the Excel object library is concerned (ie. add a 'Microsoft Excel XX.X Object Library' Reference, XX.XX being whatever the version number is in his list) ?

 

[The_Doc_Man]

So I need to instruct him to go to References, take a look down through the list of Available References, and tick whatever is present in the list where the Excel object library is concerned (ie. add a 'Microsoft Excel XX.X Object Library' Reference, XX.XX being whatever the version number is in his list) ?

Yes, that is basically correct. It will likely fix the problem. HOWEVER, there CAN be version incompatibilities. They are rare. Therefore, full disclosure is that re-referencing the Excel library SHOULD fix the problem - but not 100% guaranteed. Maybe 99% and a fraction to fix the issue. IF another error pops up, just remember to copy the exact text of the message - and if it highlights a particular line or object, tell us about that, too.

 

[alan2013]

Your friend cannot readd the reference to Excel 16, because it doesn't exist in their system; hence, it's marked as missing. Instead, they have to add a reference to the version of Excel that is on their system. It could be Excel 12.0 or 14.0 or 15.0, etc.

Thanks very much for your help

 

[alan2013]

Yes, that is basically correct. It will likely fix the problem. HOWEVER, there CAN be version incompatibilities. They are rare. Therefore, full disclosure is that re-referencing the Excel library SHOULD fix the problem - but not 100% guaranteed. Maybe 99% and a fraction to fix the issue. IF another error pops up, just remember to copy the exact text of the message - and if it highlights a particular line or object, tell us about that, too.

Thanks very much for your help. I'll prompt him to try it, and see how it goes.

 

[theDBguy]

Thanks very much for your help

You're welcome. @The_Doc_Man and I were happy to help. Good luck. There is a code solution, but doing it manually is pretty straightforward and simple. You can also consider switching to late binding.