Exporting the same macro to 60 workbooks

[haas]

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!

 

[a7n9]

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]

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...

 

[Davers]

Here is a solution I helped someone else with...you might want to check it out to see if it is close to what you had in mind...

http://www.mrexcel.com/board2/viewtopic.php?t=124885&highlight=

Dave

 

[haas]

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)]

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, 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:

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:

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:

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:

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