I distributed an XL2007 macro-enabled workbook (most worksheets protected). It worked fine for me when I gave it out - I had no idea so many people could find different ways to break this thing!!
So I need to make so changes to the code in all these workbooks. Unfortunately, our corporate environment has permanently disabled "Trust access to the VBA project object model". So I can't simply write an update script that will modify the code, correct? (Please tell me I'm wrong!!)
So the only other way I can see to make this update is to ask everyone to send me their files so I can add in new code. Or maybe distribute a new workbook with corrected code that will copy over the worksheets from the old file, rename the new file the same as the old one, and SaveAs over the top of it? Might that work?
Ed
Update VBA in file with "Trust VBA Access" prevented?
-
-
-
Re: Update VBA in file with "Trust VBA Access" prevented?
You are correct, I'm afraid.
If you have the same code in all these workbooks, you might want to think about putting the code into an add-in if possible, so that if and when you do have to update it, you only have to do so in one place.
Of course, if you have access to the folders that their documents are stored in, you could just run the update code from your own machine. -
Re: Update VBA in file with "Trust VBA Access" prevented?
I'm very hesitant about using an add-in. Trying to guide it into the correct folder can be a nightmare! It would indeed be great, though. No, I don't have access to all their folders - I'm not an admin type, not really even a developer - just the guy who knew tricky stuff with Excel when we needed a new log file built!
So I think I'll look at what it's going to take to copy over each one of their worksheets into a new file. Probably go something like:
-- Distribute a file with all the corrected code and one sheet with a button on it: "Click me to update"
-- Code directs them to navigate to file to be updated
-- Copy each sheet from old file into "update" file (hopefully there's a "sheet copy" method that doesn't lose formatting or formulas)
-- Do any necessary formula updates in the new book
-- Rename original file a "_old" and save
-- SaveAs update file with the original file's fullname
Hmm - is there a property of the SaveAs that creates a new file but leaves the original? If not, I'll have it clone itself first - somehow. Some of these poeple are going to have to run this near a dozen times!
Thanks for chiming in.
Ed -
Re: Update VBA in file with "Trust VBA Access" prevented?
Admitedly I dont know your company structure and I.T. system. But on the face of it I would say thats a potential for a much bigger headache than an addin.
-
Re: Update VBA in file with "Trust VBA Access" prevented?
The Workbook object has a savecopyas method that will probably be handy for your second option.
Re the addin, bear in mind that an addin can be stored in, and run from, a network location so you can literally have one copy to update.
-
-
Re: Update VBA in file with "Trust VBA Access" prevented?
Reafidy:
Quote[INDENT]Admitedly I dont know your company structure and I.T. system. But on the face of it I would say thats a potential for a much bigger headache than an addin. [/INDENT]
What are you looking at for the headache? The IT environment or the way I'm proposing to handle things?
rory:
I did not know an add-in can be run from a network location!! At the moment, I have the "Master" file as Read-Only in a network folder; the users download to their computer and then SaveAs their own file. Can I safely assume that if I had the add-in linked to the master file, it would stay connected throughout the download/drag-and-copy/right-click & SaveAs process and then the user's modifications and saving?Then again, some of the code in this is in Worksheet_Change procedures. Can I duplicate those in an add-in?? The users will be adding some new sheets, and then also making copies of a specific sheet to enter more information on.
Ed
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!