VBA References

[emmahope206]

Hi All,

I'm in a sticky situation, I work for a massive global company and have an IT department that is impenetrable. The situation is as follows:

We have a 2013 Excel workbook connected to a 2013 Access database* (using MS Query), most of us are on Windows 7 (handful on Windows 10 but upgraded from Win7 or Office 365 but upgraded from Office 2013). Excel has a VBA reference to "Microsoft Office 15.0 Object Library" & it works fine for all of these people.

*It is actually about 60 different Access databases, the Excel workbook picks the right one depending on a combination of drop down options the user selects in Excel plus a password they add.

Out new starters are being added with Windows 10 and Office 2013 and for some reason (unknown & unfathomable**), IT are setting this up with a Office16 folder with a totally different C drive path (I believe the Office15 folder does exist but does not contain the appropriate DLLs). (**possibly getting ready for Office365 upgrade, possibly just because they seem to be there to drive us crazy!)

We are not allowed to amend either the Office15 or Office16 folder ourselves and at one point, IT were copying the DLLs over from the one folder to the other but are now refusing due to the number of new starters we have (100s) & now we cannot get them to engage with us at all.

I have tried to recreate the issue but cannot as I have the correct DLLs in the correct folders. I have also tried to programmatically add the references to the correct folders but have had little success for two reasons. 1. the end users are not capable of updating the security settings to allow the 'trust access to the VBA project object model' (they tend to be junior, very new & often there are language barriers) and 2. as I cannot see or recreate the issue I can't even be sure if I am getting the path right. (as I can't recreate it, I can't even work out the GUID etc!).

I am using MSQuery and this methodology as the Access databases MUST be passworded and encrypted as they contain personal data and this can only be visible to certain people with certain levels of access.

I have tried copying the DLLs from my c drive into a shared location and re-pointing the VBA to these, thinking this would help but the VBA project does not understand this.

I'm sorry I don't really understand what is meant by early & late binding (I have seen this on some similar posts).

Can someone give me any suggestions on how to get around this issue?

Thanks
Emma

 

[RoryA]

What exactly is the issue? The Office reference should update as necessary between versions of Office.

 

[emmahope206]

What exactly is the issue? The Office reference should update as necessary between versions of Office.

Hi Rory,

Apologies. The issue is that the version of Office is the same (Offfice 2013 Pro) but IT has set the new starters up (somehow) with an Office16 folder rather than an Office15 folder on the C drive. The VBA reference looks in the Office15 folder for the DLL, does not find it and therefore fails to connect to the database.

I cannot recreate it or add an Office 16 reference as I do not have this, I have an Office15 folder/References.

Thanks
Emma

 

[RoryA]

The Office reference has nothing to do with database connections, and should work regardless of where it was installed. It also doesn't change based on the folder name. Are you getting an error message? If so, what is it, and what is the code?

 

[emmahope206]

The Office reference has nothing to do with database connections, and should work regardless of where it was installed. It also doesn't change based on the folder name. Are you getting an error message? If so, what is it, and what is the code?

Hi Rory,

The issue is that Excel returns nothing when attempting to refresh the query. I'm not allowed to remote in to the users machines to see the actual issue but when the 1st line helpdesk do, they say there is no error message but when their specialists check they say it is due to the fact it is looking for "ACEODBC.DLL" in C:\Program Files (x86)\Common Files\microsoft shared\OFFICE16 when it is actually in C:\Program Files (x86)\Common Files\microsoft shared\OFFICE15 and it resolves itself if this DLL is copied across from Office15 to Office16.

I am told this is something that is used to connect Office programs such as Excel to Access and is part of the VBA references...is this not the case? There is no code as such that affects this other than ActiveWorkbook.RefreshAll. The connection between Excel & Access uses MSQuery (Data ->From Other Sources ->From Other Sources ->MS Access Database etc.)

Thanks for your help.
Emma

 

[RoryA]

If you're not getting an error, then either there is some code suppressing errors or the problem is not with the code. I don't see how the references could be the issue since you would get a compile error if they were a problem and that is not the dll referenced by the Office reference anyway (that would be the MSO.dll). It might be a dll used by MSQuery but that's outside of your control as far as I know.

 

[jkpieterse]

Perhaps you might show us some code too?

 

[Marc L]

Hi,​

​

when using early binding so any reference outside the application itself (aka here Excel) can obviously be an issue,​

the reason why often the VBA pro coders use late binding rather than early binding …​

​

An example : a Word 15 reference was added to an Excel project via the VBE Tools menu (that is early binding).​

The VBA procedure well works on the source computer but not on another one which does not have Word 15 but another version.​

So the solution consists to remove the reference from the Excel project in order to go to late binding​

with this kind of codeline Set WordApp = CreateObject("Word.Application") …​

 

[emmahope206]

Thanks for the posts guys, I guess it's back to square one. There is no error message, even when the user right clicks on the table and just tries to 'refresh', it just does not return anything (which confirms Rory's point that it's not a VBA reference issue). IT helpdesk insist it is 'the macro' at fault as the user usually runs it from a button (which is basically just an activeworkbook.refreshall command) and my (incorrect) assumption that this DLL must be therefore be related to a VBA reference but I can see now this is not the case. This DLL must be related to something else, presumably the MSQuery. I will have to see if I can find some other way of getting the data from the multiple passworded/encrypted Access databases into the Excel tables in simple way that works across all my users.

 

[RoryA]

If a manual refresh/refreshall doesn't work either, then it's clearly not a code problem (I'm sure you know that). In my experience, IT helpdesks know little to nothing about Office and tend to take the path of least resistance - i.e. blame the thing that is outside of their control. If copying the dll from one place to another fixes the problem, it would suggest to me that the installation was faulty, which is definitely their remit.

I'm surprised you don't get an error of any sort with either refresh method though.