3

I writing some VBA code using Microsoft Office 16.0 Object Library. When I pass the file to another user who runs on Microsoft Office 14.0 Object Library. Once the function is being triggered, an error cannot find project and library will appear.

How can I auto update users using lower version of Microsoft Office Object Library to same version as mine?

My Reference

enter image description here

User 2 Reference

enter image description here

aaa
  • 857
  • 4
  • 25
  • 46
  • 3
    You cannot update theirs to yours. You would need to find the version they are using and use the appropriate reference. Late binding is often my preference to avoid this version dependency. The method you are considering (in reverse, i.e. stepping down to the appropriate reference) would need another step. It would need to remove the missing reference as well. – QHarr Nov 27 '17 at 07:06
  • See here for removing missing references https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b90ed5cc-6bd1-46b4-bbea-de4a15521b26/detect-and-remove-missing-references-in-vba-code?forum=exceldev – QHarr Nov 27 '17 at 07:10
  • 1
    See here for checking version with `Application.Version`: https://excel.tips.net/T003340_Automatically_Changing_References_to_VBA_Libraries.html and see here for adding a reference programmatically: https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically – ashleedawg Nov 27 '17 at 07:11
  • Version 16.0 = Excel 2016, Version 14.0 = Excel 2010. Perhaps you should set it up with 14.0 rather than updating all of your users. – ashleedawg Nov 27 '17 at 07:13
  • @ashleedawg Hi, u mention 14.0 = Excel 2010, but when I check their excel version, it is Excel 2016. Do you know why? – aaa Nov 27 '17 at 07:14
  • @ashleedawg All users would need to be on that version though – QHarr Nov 27 '17 at 07:14
  • As far as I know you can't run "part of" Excel 2016 an Excel 2010 installation, and if you can, it's probably not a good idea, and possibly not legal. Why can't you use 14.0? – ashleedawg Nov 27 '17 at 07:40
  • 2
    If you have objects, properties that are Excel version sensitive, you need to code logically for that. Giving the users the Object Library file won't automatically make their Excel istallation work with features that weren't available in 2010. – ashleedawg Nov 27 '17 at 07:44
  • @ashleedawg Hi, it took me awhile to understand. So where should I applied the code? ` Dim oExcel As Object Set oExcel = CreateObject("Excel.Application")` on workbook_open? – aaa Nov 27 '17 at 08:09
  • Uh, I'm not sure what you're trying to do with that code. What would you like to accomplish? Have you decided that you will use the lesser Object Library version, instead of changing the users? This is very hard to troubleshoot without more information. – ashleedawg Nov 27 '17 at 08:31
  • The Office library should automatically adjust to the user's version. Are you sure it's that library that is causing the problem and not one of the other non-standard references? – Rory Nov 27 '17 at 08:42
  • @Rory Hi, I just updated my question to reflect on problem. – aaa Nov 27 '17 at 08:58
  • @ashleedawg I believe I'm the only one using ver 16 right now. If I changes the user's version, what would happen? Does it required the users to do anything? If it is too troublesome for the user, I think I would use the lesser version. – aaa Nov 27 '17 at 08:58
  • that depends on a few things. To clarify: are you using features in Excel that didn't in versions before 16.0 (2016)? – ashleedawg Nov 27 '17 at 09:24
  • I’m actually only using the file system object and connecting to SharePoint. – aaa Nov 27 '17 at 09:25
  • it's excel file that you're distributing though, right? – ashleedawg Nov 27 '17 at 09:41
  • @ashleedawg Sorry, didn’t get u, but it works fine on my desktop – aaa Nov 27 '17 at 09:42
  • Yes - so it's an Excel 2016 file that connects to Sharepoint, and you're distributing it a bunch of Excel 2010 users, correct? – ashleedawg Nov 27 '17 at 09:50
  • @ashleedawg Yes. I’m afraid some might be using other versions. But most should be using the version14 – aaa Nov 27 '17 at 09:52
  • Are you actually using that `AdobeAAMDetect` library in your code? If not, just uncheck it and save the workbook, which will fix your problem. – Rory Nov 27 '17 at 11:45
  • @Rory thanks for the suggestion, will tried tomorrow morning and update you. Can you please explain reasons make you think so? – aaa Nov 27 '17 at 11:46
  • 1
    It's the only `MISSING:` reference, and that is what causes the runtime issue you mentioned. You can see in the picture that the Office reference has updated just fine. – Rory Nov 27 '17 at 11:48
  • @Rory Yes you are absolutely correct. Once uncheck it works fine. – aaa Nov 28 '17 at 05:05

1 Answers1

0

Here's a good source of info:

Use Excel with earlier versions of Excel

Personally, I would check the users' version number in auto_open and then add/remove the reference as needed. More here and here.

Other options:

  • get a copy of the user's version of the Office Object Library and add it as a Reference on your machine, and uncheck 16.0 when it's time to save to distribute.
  • Install Excel 2010 as a 2nd version on your machine, and switch between as needed.
  • Upgrade all users to Office 2016

...or, is there I.T. department at this workplace?

  • Sounds like I.T.'s problem. Open a ticket. :-)
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Thanks for answering, but @Rory had a more easy solution. However, I would still add in what you suggested since it is going to shared with multiple users. Just to be double secured :) – aaa Nov 28 '17 at 05:06
  • I'm glad you got it figured out! – ashleedawg Nov 28 '17 at 12:05