Exporting the same macro to 60 workbooks

haas

New Member
Joined
Jan 20, 2005
Messages
3
Hi all!

I was wondering if anyone can help me with an Excel VBA problem. I am trying to use the same macro (via a Module) for 60 different pre-existing Excel workbooks. What I did was make a change to the code in an existing template, and need to propogate it to all Excel books that were created previously from this Template.

Please help with any suggestions!


Thanks!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696
You can save the macro in the Personal Macro Workbook and run it from anywhere, but then running it 60 times manualy won't be such a good idea. May be you can loop your macro 60 times for different workbooks.
 

haas

New Member
Joined
Jan 20, 2005
Messages
3
Thanks for that but maybe I didn't make myelf too clear in my request.

I have a .xlt Template in which I have an export macro (this copies data from a sheet in the workbook and appends to a database). The template becomes a regular .xls file once I rename it. Now, I've added things to the macro on the Template; I have 60 .xls books created from this Template which house the same previous code. I need to make changes in the code in all of these .xls books to reflect the revised one I have in the Template.

Sorry if this is confusing, but if you can understand what I'm trying to do, please help!

Thanks...
 

haas

New Member
Joined
Jan 20, 2005
Messages
3

ADVERTISEMENT

Thanks for your help but that doesn't work.

All I'm trying to do is:

1. A template exists with a macro, hence code.

2. 60 Excel workbooks have been created from this template and have the same macro in them, thus they have the same code.

3. I made a revision to the macro in the Template, thereby modifying the code.

Now, can I run a macro to update the code in the 60 Excel workbooks that don't have this modification in the code? I think this process is called "patching" - not sure.

I appreciate everyone's help - I am terrible at explaining things and hopefully the above explanation helps.

Thank you all!
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi,

Are the VBA projects unlocked in these 60 workbooks? If not, then you are going to struggle (unless you use a messy SendKeys approach, but I wouldn't recommend it).

In terms of future scenarios like this you may be better creating an add-in that your users can call upon to perform the required actions. That way, you only need to amend the add-in.

As regards your request you may be interest in the routine that I posted here :
http://www.mrexcel.com/board2/viewtopic.php?t=99704&highlight=codemodule
(and in general terms have a look at the info in the link to Chip Pearson's page)

HTH
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Well, I'm able to add a line of code to existing code in workbooks. It is kind of complicated, especially when you have 60 workbooks you want to add the code to. I don't think it's possible to add code to a closed workbook, so the first order of business is figuring out how to open all of the workbooks, one at a time, and have the code update. If all your files are in a single folder, you're lucky. The following code will check the directory for .xls files, and paste the names in column A. The directory is in the code, in this case, it's a folder in my C: called New Folder:

Code:
Sub MyXLFiles()

Dim myDir As String
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Worksheets("Sheet1").Select

myDir = "C:\New Folder\"
myFile = Dir(myDir & Application.PathSeparator & "*.xls", vbDirectory)

Do While myFile <> ""
    i = i + 1
    Cells(i, 1) = myFile
    myFile = Dir
Loop

Worksheets("Sheet1").Select

End Sub

Once you have a list of all your files that need modifying, you need to know first what module the code that needs to be modified is in, then at which line within that code you want to start inserting code. In the following case, I am opening all the workbooks and inserting a single line: "This is a test" to a procedure called Sub NameGoesHere() in Module1. You would of course have to change the module to whichever one you are using, and change the name of your sub. Here is the code:

Code:
Sub AddSpecificLine()

Dim awcl As Integer
Dim i As Variant
Dim myBk As String
Dim myDir As String
Dim myRng As String
Dim VBCodeMod As CodeModule
Dim StartLine As Long

myRng = Worksheets("Sheet1").Range("B1").Value
myDir = Worksheets("Sheet1").Range("C1").Value

For Each i In Worksheets("Sheet1").Range(myRng)

myBk = i.Value

Application.ScreenUpdating = False
Application.EnableEvents = False

Workbooks.Open myDir & myBk ', , , , "password", "password"

Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule

On Error GoTo HandleErr

With VBCodeMod
    StartLine = .ProcStartLine("NameGoesHere", vbext_pk_Proc)
    .InsertLines StartLine + 5, "This is a test"
End With

HandleErr:
    
Resume Next

Application.DisplayAlerts = False
Workbooks(myBk).Save
Workbooks(myBk).Close

Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

This only adds one line of code though, I can't seem to figure out how to get it to add more than one line of code to a specific Sub in a module. In my opinion...it would be easier to delete the code you want modified, and then just create it from scratch. If you want to try that way, the following code will delete code from a workbook:

Code:
Sub removeMyCode()

Dim awcl As Integer
Dim i As Variant
Dim myBk As String
Dim myDir As String
Dim myRng As String
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

myRng = Worksheets("Sheet1").Range("B1").Value
myDir = Worksheets("Sheet1").Range("C1").Value

For Each i In Worksheets("Sheet1").Range(myRng)

myBk = i.Value

Application.ScreenUpdating = False
Application.EnableEvents = False

Workbooks.Open myDir & myBk ', , , , "password", "password"

Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule

    With VBCodeMod
        StartLine = .ProcStartLine("NameGoesHere", vbext_pk_Proc)
        HowManyLines = .ProcCountLines("NameGoesHere", vbext_pk_Proc)
        .DeleteLines StartLine, HowManyLines
    End With

Application.DisplayAlerts = False
Workbooks(myBk).Save
Workbooks(myBk).Close

Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

And the following code will write new code to the module. In this case Module1:

Code:
Sub AddaSub()

Dim awcl As Integer
Dim i As Variant
Dim myBk As String
Dim myDir As String
Dim myRng As String
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim LineNum As Long


myRng = Worksheets("Sheet1").Range("B1").Value
myDir = Worksheets("Sheet1").Range("C1").Value

For Each i In Worksheets("Sheet1").Range(myRng)

myBk = i.Value

Application.ScreenUpdating = False
Application.EnableEvents = False

Workbooks.Open myDir & myBk ', , , , "password", "password"

Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule

On Error GoTo HandleErr

With VBCodeMod
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
"Sub lockSht()" & vbCrLf & _
"   Dim mySht As Variant" & vbCrLf & vbCrLf & _
"    For Each mySht In ActiveWorkbook.Sheets" & vbCrLf & _
"        mySht.Protect Password:=""password"", DrawingObjects:=True, _" & vbCrLf & _
"            Contents:=True, Scenarios:=True" & vbCrLf & _
"        mySht.EnableSelection = xlNoSelection" & vbCrLf & _
"    Next" & vbCrLf & vbCrLf & _
"End Sub"

End With

HandleErr:
    
Resume Next

Application.DisplayAlerts = False
Workbooks(myBk).Save
Workbooks(myBk).Close

Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

I know this can be confusing...hopefully this helps or at least gets you going in the right direction.

Have a good day,

Dave (y)
 

Forum statistics

Threads
1,148,218
Messages
5,745,444
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top