Editing a Macro in one file using VBA in another file

[kevkni]

Hi, I'm hoping that somebody can help me!

I have nearly 200 Excel files that all contain the same VBA macro. However as these files are all distributed to multiple users, the macro in question is protected to prevent users from accidentally amending the code.

I now need to amend the code in each of these files, and rather than working my way through each file one by one, I'm looking for a way to do this using a macro routine.

I already have a routine which works through the files making changes to each Excel file in turn, but currently the changes are to the worksheets themselves, and not the protected VBA code in each file.

There are two adjustments to the code that I need to make:

1. In a module called PackProtection, I need to amend the code from "AllowFormattingColumns:=False" to "AllowFormattingColumns:=True"

2. In a module called StartNewMonth, I need to add some additional (pre-written) code which resets the value in one particular cell (which has an identical cell address in each of the excel files) from "Yes" to "No"

Is there a relatively straightforward way of being able to update the VBA code in each of the Excel files by modifying my current routine? :dunno

Thanks
Kevin

[mikerickson]

One approach would be to Export a good module from your Master.xlsm and have a routine that Imports it into the targeted workbook.

[kevkni]

Thanks Mike

I don't suppose you could help with some generic code for that? I've tried searching for code on the interweb, but any that I find I'm struggling to understand what each stage is doing, and how I would tailor the code to my particular routine.

If it helps I already have a module with the correct code - ready to be inserted.

Thanks
Kevin

[snb]

Why don't you produce 200 copies of the file that contains the revised macro ?

[kevkni]

Why don't you produce 200 copies of the file that contains the revised macro ?

Although each file has the same macro, each file contains information relevant to the particular location it's from. i.e. a file from Glasgow has Glasgow related information, the Southampton file has Southampton related information etc. etc. Each file is from a different location.

Whilst re-creating the files would be the easiest solution in this particular situation it just simply isn't an option.

Replacing the existing module with a new module would seem to be the logical appraoch, I just can't find understandable code to make this happen.

Any help would be gratefully received.

Regards
Kevin

[SamT]

I am attaching a file that contains the entire VBE obect Model from Office 97. While it is dated, most of it should still be applicable. All the helps are in Cell Comments.

VBE, or Visual Basic Editor, is the object you must work with when programmatically changing Code pages.

[snb]

to remove a module see:

http://www.snb-vba.eu/VBA_Excel_VBproject_en.html#L29

to import a module see:

http://www.snb-vba.eu/VBA_Excel_VBproject_en.html#L23

[Aflatoon]

If you have password protected the code in each file, you are going to have a lot of fun...

I would suggest you take this opportunity to move the code to a separate workbook from the data so that in future you can simply distribute a new code workbook to everyone.

[SamT]

Aflatoon + 1

[Aflatoon]

This may help: http://www.vbaexpress.com/forum/showthread.php?t=43809

[Ago]

If you have password protected the code in each file, you are going to have a lot of fun...

I would suggest you take this opportunity to move the code to a separate workbook from the data so that in future you can simply distribute a new code workbook to everyone.

+1!

And I would suggest you make it a add-in that can selfupdate.
In that way you only update your own masterfile and the slaves will follow :devil2:

[Jan Karel Pieterse]

Quick tip on add-in: If you put it in a network share that everyone has access to AND mark the file read-only, updating the add-in is a simple matter of deleting old add-in, copying in new add-in and marking the file read-only again. As soon as a user restarts Excel they have your new copy.