Broken VBA office references

[CedarTree]

So I got moved to Windows 10 with VBA references now pointing to Office 16. My end-users are generally still on Windows 7, needing Office 15 references (my coding requires Excel 15 or 16, Word 15 or 16, Outlook, etc.) So when I pass them the ACCDB (not using ACCDE by the way), they get reference errors.

Anyone have some coding I can steal to check which version of office is in play and change the references when first starting up?

P.S. I'd use ACCDE but I've discovered for some reason that cross-tab queries that you run in VBA tend to break in ACCDE. It's a weird bug but I haven't spent long trying to de-bug.

Thanks!

 

[theDBguy]

Hi.You could try using Late Binding in your code. For example, rather than use something similar to this:

Dim xlApp As New Excel.Application

You would use something like this:

Dim xlApp As Object
 Set xlApp = CreateObject("Excel.Application")

Hope it helps...


PS. Here's a reference for you: VBA Early and Late Binding

 

[CedarTree]

Ah. Interesting. I've seen that mentioned but confess some ignorance here. So, when you look at references screen under Tools, how does that behave with this coding?

I want to say I AM using your suggested coding but I'd have to confirm I didn't miss a spot.

 

[theDBguy]

Ah. Interesting. I've seen that mentioned but confess some ignorance here. So, when you look at references screen under Tools, how does that behave with this coding?

I want to say I AM using your suggested coding but I'd have to confirm I didn't miss a spot.

If you have a reference to Excel checked in the Tools > References window, then you're using early binding. If you don't have any reference to Excel checked, then you're using late binding. Hope it makes sense...

 

[The_Doc_Man]

If your references are "broken" then your code generally will not run very well. But you programmatically explore what you have with the Application.References collection, for which you can get a .Count and for which you can use application.references to look at the references in your app. I have not tried to use this collection-property to actually fix references, but as long as what you do doesn't attempt to use a reference you are trying to fix, it might be possible. I said "MIGHT" because Access can be touchy about bad references.

https://docs.microsoft.com/en-us/office/vba/api/access.application.references

 

[CedarTree]

OKay so just to test things, I looked for all Excel references, changed my coding to this...

Set gobjExcel = CreateObject("Excel.Application")

Then I turned off Excel reference, but I get errors on very specific Excel coding, e.g.,

gobjSheet.Range(sRange).PasteSpecial Paste:=[B][COLOR="Red"]xlPasteValues[/COLOR][/B]

 

[theDBguy]

OKay so just to test things, I looked for all Excel references, changed my coding to this...

Set gobjExcel = CreateObject("Excel.Application")

Then I turned off Excel reference, but I get errors on very specific Excel coding, e.g.,

gobjSheet.Range(sRange).PasteSpecial Paste:=[B][COLOR=Red]xlPasteValues[/COLOR][/B]

Right, you'll have to declare all Excel constants you're using with their equivalent long integer values. For example, you could use either:

Const xlPasteValues As Long = -4163

or

gobjSheet.Range(sRange).PasteSpecial Paste:=-4163

 

[MrHans]

You can find a list of all/most Office constants here on this site:
https://www.codekabinett.com/page.php?Lang=2&Theme=10

 

[CedarTree]

Perfect... that was going to be my next question... where to get those constants.

 

[Deleted member 147267]

What about AddFromGUI method of References Object? Seems to me I used that years ago. I had a table of references with a path to the library file. I didn't use it to test against Office versions, but rather for ensuring I didn't forget to add a reference for a project. Since all Office installs were governed by IT, the locations of the files for a given version were constant from one pc to another. Seems to me that you could have a table field for Office version and load the references at run time, based on the version.

 

[CedarTree]

So far this is working great!

Is there anyway to remove reference to "Microsoft Office 15.0 Access database engine Object library"? If I unclick it, I get compile errors on basic things like "recordset" or "querydef". But I want to make sure 15.0 references don't bomb out if I upgrade to 16.0 but my users are using 15.0. The Access constants module I copied from your link doesn't handle this aspect.

 

[theDBguy]

So far this is working great!

Is there anyway to remove reference to "Microsoft Office 15.0 Access database engine Object library"? If I unclick it, I get compile errors on basic things like "recordset" or "querydef". But I want to make sure 15.0 references don't bomb out if I upgrade to 16.0 but my users are using 15.0. The Access constants module I copied from your link doesn't handle this aspect.

Hmm, I've never heard anyone having issues with this reference. Just leave it alone, and you should be fine. It's the database engine (ACE) Access is using to manage the data and run SQL statements.

 

[CedarTree]

OKay - I was suspecting that maybe this reference "auto-updates" for each version of Access so I may be fine. I'll give it a shot.

Thanks again!

 

[theDBguy]

OKay - I was suspecting that maybe this reference "auto-updates" for each version of Access so I may be fine. I'll give it a shot.

Thanks again!

You're very welcome. We're all happy to assist. Good luck with your project.