Alter one macro from within another

[dianahowe]

Hi - I have written and distributed an Excel file containing several macros, and one is slightly wrong. Is it possible to access one macro from within another, so that 100+ files do not have to be individually corrected? :dunno
Currently feeling a bit like this :banghead: so if anyone can tell me how or confirm it can't be done that would be great!

[johnske]

Is the incorrect procedure in the same code module in each workbook, and are all the other procedures in that module identical? If so, follow the instructions below (if not, we'd need a custom procedure to do a search through all code modules to find the incorrect line and replace it with the correct one)

Put all the workbooks with the incorrect procedures into a folder with a book containing the correct procedure.

Put this code into a standard module in the book that contains the correct procedure and run it (make sure all the other workbooks are closed as I didn't add error-handling code to check if they're open) - this is basically a 'copy and paste' for a VBE module, change "Module1" to another module name if you wish...


Option Explicit

Sub ReplaceModule1Procedures()

Dim FileFound As Object
Dim NewCode As String

'ThisWorkbook is the file containing the correct Module1 procedures
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
NewCode = .Lines(1, .countoflines) '< "copy" the code
End With

'open all files you want the corrected procs in...
With Application
.ScreenUpdating = False
.DisplayAlerts = False
For Each FileFound In CreateObject("Scripting.FileSystemObject") _
.GetFolder(ThisWorkbook.Path).Files
If Right(FileFound.Name, 4) = ".xls" _
And Not FileFound.Name = ThisWorkbook.Name Then
Workbooks.Open(FileFound).Activate
'--------Delete old procedures and replace with new--------
With ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
.DeleteLines 1, .countoflines
.InsertLines 1, NewCode '< "paste" the code
End With
'-----------------------------------------------------------------
With ActiveWorkbook
.Save
.Close
End With
End If
Next
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

[Bob Phillips]

I would drop a corrected version of the module on the server, and run a much simpler code to remove that module from the workbooks, and then import the amended version.

[dianahowe]

I think this sounds more like what I need as what's wrong is some extra lines of code need to be inserted. I may have stymied myself before I start though as the VB Project is password protected...

[Bob Phillips]

Now you have a problem. You need Sendkeys to pass the password, and that is flakey.

[dianahowe]

Using sendkeys, can you tell me how I pass it the password?
What code do I use to tell it to unlock..?

Thanks,

[GTO]

Greetings,

Am I seeing the post dates correctly? Gosh!:eek:

Well anyways, you might want to see if this helps:

http://www.vbaexpress.com/forum/showthread.php?t=30687

Mark