Using VBA to change references in ANOTHER project. Help

[bobsnail]

Hello, New to the Board but really need some help.

I work for a large organisation (6000 + PCs). I work on a team where we capture info on a excel spreadhsheet that has embedded macros. These workbooks get sent to different users who add their contribution to the workbook and save the changes to the workbook before returning to me.

The problem is that different users are using differnt versions of MS office, which means that if someone opens the document in office 2003 and saves the changes, the office 2000 reference is replaced by the office 2003 library, so when i get the file back it crashes and i have to change the reference manually.

I flagged this up weeks ago as an issue in the hope that i could amend the excel workbook we send out with something like :- (which would run on workbook open)

Set ref = Application.VBE.ActiveVBProject.References("Outlook")
Application.VBE.ActiveVBProject.References.Remove ref
Application.VBE.ActiveVBProject.References.AddFromFile_ "D:\Applications\Office2000\OFFICE9\msoutl.olb"

Sadly this option was not embraced, more PCs have been upgraded to Office 2003 and this is a time consuming issue

What i want to do is have a 'Master tool' that opens each template and sucks the new data out of them, fixing teh invalid reference as it goes. The reference MUST be fixed becuase i might forward the document onto another user that doesnt have Office 2003, and they will experience the same issue

Can i do something similar to the above code, but not in the active project.

something like :-

Dim wbsource as workbook

set ref = wbsource.application.vbe.activevbproject.references ("Outlook")

Blah blah.

So im affecting the VBE of the damaged excel workbook rather than the active project?

I tried it but get an error saying it conflicts with an existing library

Am i on the right lines?

The Master tool will user Microsfot Outlook object library 9.0 and so will the excel workbooks i send out. But when i receive the excel workbook back it will contain "Microsfot Outlook object library 10.0" which wont be on my PC, hence i want the code to automaticall open, replace the invalid reference with a valid one and close it down again.

Hope ive not over empasised. Any thoughts greatfully received

 

[stanleydgromjr]

bobsnail,

I would reccommend that you re-write your original code to first check the version of MS Office, and/or Outlook, that is on the computer, and then branch to the code in your macro for that version of MS Office, and/or Outlook.

Or, do you have to open the other file to copy information from it? There are ways to copy data from closed workbooks (if everyone is using your original template).

Have a great day,
Stan

 

[bobsnail]

thx for youe comments so far but

Yeah i understand what you say, unfortuantely i have no control over the rollout of the different versions of office, also i am not allowed to add any code to the workbook that gets the dodgey reference.

So instead of opening each workbook manually and changing the reference i want a tool that opens every workbook in a folder (or in an outlook inbox) and changes the reference if its broke.

Im completely happy on how to do all of this, i can even do what i want to self (to the tool that has the macro running).

The only thing i cant do is manipulate the VBE for the spreadsheet im opening.

As mentioned in my original post the workbook MUST be fixed becuase it has to be passed on to other users who are not IT literate.

Thanks for your help so far.