changing code with code

[tommy1234]

Hello
I have a code in 70 files that has vba code written in each of them.
A few days ago the company has changed the server number and due to that i have to update the code in all the files.
Is there an option to update them with a code ?
(i need help only on how to update one file and i'll manage with the rest).

thanks :banghead:

[johnske]

Yes, if the code's all in standard modules you just export or delete the old and import the new. If there's code in other types of modules such as ThisWorkbook or worksheet modules the procedure's different - more info required...

But if you only want to change the server number you can search thru the code and just replace every instance of the old line with a new line of code - post the old line of code and the line you want to use instead.

[Norie]

Check this http://www.cpearson.com/excel/vbe.htm.

[tommy1234]

the module is in ThisWorkbook that's the reason i want to do something "automatic" and not to enter each workbook and replace the server name

[lucas]

Sounds like you need to rethink your logic overall. It would be better to create an addin with the code and make it available on the server. Then all you would have to do is fix the code in the addin and your done. Not a good idea to spread your code like that expecially in the personal.xls. See John's article on addins in the articles section.

[johnske]

Put all the workbooks that need the new ThisWorkbook procedures into a folder with the book containing the new procedure.

Put this code into a standard module in the book that contains the new 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 the ThisWorkbook module.


Option Explicit

Sub ReplaceThisWorkbookProcedures()

Dim FileFound As Object
Dim NewCode As String

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

'open all files you want the new 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("ThisWorkbook").CodeModule
.DeleteLines 1, .countoflines
.InsertLines 1, NewCode '< "paste" the code
End With
'-----------------------------------------------------------------

ActiveWorkbook.Close savechanges:=True
End If
Next

.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub