VBA References

emmahope206

New Member
Joined
Mar 14, 2012
Messages
7
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
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
937
Office Version
  1. 2010
Platform
  1. Windows
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)
No error message may come from an On Error codeline hidding any issue …​
When opening the References option in the VBE Tools menu any reference issue is displayed as 'missing' …​
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,106
IMHO this is a clear case of a bad installation. IT hasn't done the job right. If you ask me being able to use ODBC in office is crucial.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,759
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top