I have a few thousand excel files that all contain a macro. I now have a new macro that I would like to replace the old macro with. Is there a way that I can distribute the new macro to all the excel files without having to copy the script by hand?
I have a few thousand excel files that all contain a macro. I now have a new macro that I would like to replace the old macro with. Is there a way that I can distribute the new macro to all the excel files without having to copy the script by hand?
Read through this link: http://www.excelforum.com/showthread.php?t=605942
I understand the add-in. My problem is switching the thousand+ files that are already there to the add-in. I would have to go through each one and select to use the .xla script wouldn't I?
Yes that is one way you can accomplish the task or alternatively you write VBA code to change the VBA code in the spreadsheets programmatically. I have never done this but many people do this to solve problems exactly like you are describing.
For starters, read up on the VBComponents collection. Make sure you reference the library "Microsoft Visual Basic For Applications Extensibility" Once done you can iterate through the code in another worksheet.
For what it is worth, accessing modules and other VBA code using code is one method that less than trust worthy virus writers get their work done ( Be carefull)
Check out the two books referenced in the link above in the previous post. Both have chapters on doing this.
Please Login or Register to view this content.
It is not a simple procedure and will fail if the VBa Project is protected in the existing workbooks. What is the code that you want to change?
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Thank you but I resolved my issue by writing a Python script that iterates through the old .xls files, picks out the VBCode in the file, and then replaces it with my new code. It was difficult because there is no official Documentation that I could find on Excel/Python attributes - but a whole lot of guess work and translating different languages into Python finally got it done.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks