+ Reply to Thread
Results 1 to 6 of 6

Mass Macro Distribution

  1. #1
    Registered User
    Join Date
    07-03-2007
    Location
    Kentucky
    Posts
    6

    Mass Macro Distribution

    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?

  2. #2
    Forum Contributor Tarball's Avatar
    Join Date
    04-21-2007
    Location
    Atlanta, GA
    Posts
    166
    Read through this link: http://www.excelforum.com/showthread.php?t=605942

  3. #3
    Registered User
    Join Date
    07-03-2007
    Location
    Kentucky
    Posts
    6
    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?

  4. #4
    Forum Contributor Tarball's Avatar
    Join Date
    04-21-2007
    Location
    Atlanta, GA
    Posts
    166
    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.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    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

  6. #6
    Registered User
    Join Date
    07-03-2007
    Location
    Kentucky
    Posts
    6
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1