Programmatically Update Module

[Mitch_s_s]

Hi,

I am looking for a way to update the same module in over 100 files.

All i need to do is update a variable value from 24 to 8.

I know you are able to update lines of code in modules, but I don't know how.

Could someone please offer some sample code/examples?

Thanks
David

[westconn1]

you can try like this i tested works ok, this method read the whole module, then deletes all code in the module then rewrites the module with the updated code

Dim vc As Object
Set vc = Workbooks("book1").VBProject.VBComponents("module1").codemodule 
eend = vc.countoflines
if vc.Find("mystr = 8", 1, 1, eend, 1, False, False, False) then  'only do anything if that variable and value are found in the module
mystr = vc.Lines(1, eend)
repstr = Replace(mystr, "mystr = 8", "mystr = 24")  ' mystr being the variable value to change
vc.deletelines 1, eend
vc.insertlines 1, repstr
end if

it is also possible to just replace individual lines, but it is more complicated code, and i doubt it has much gain in speed, unless the code in each module is very large

[Mitch_s_s]

Spot on mate, that works a treat

bit gutted the intellisense (sp) doesn't work on the find/replace/deletelines and insertlines functions you posted

will search for them now though

just to confirm dim eend as long, mystr and repstr as string?

[Mitch_s_s]

wont let me leave you rep, says i need to spread it about a bit before giving it to you again ha!

[Mitch_s_s]

Excel keeps encountering an error when i set it off on its own

if i step through the code it works fine

any ideas?

Sub open_workbooks()
Dim cell As Range
 
For Each cell In Range("A2", Range("A2").End(xlDown))
    Application.EnableEvents = False
    Workbooks.Open (cell.Value)
    Application.EnableEvents = True
    update_Timer
    
Next cell
 
End Sub
 
 
Sub update_Timer()
Dim vc As Object
Dim eend As Long
Dim mystr As String, repstr As String
Set vc = ActiveWorkbook.VBProject.VBComponents("module1").codemodule
eend = vc.countoflines
If vc.Find("TIMERSEC = 25", 1, 1, eend, 1, False, False, False) Then  'only do anything if that variable and value are found in the module
mystr = vc.Lines(1, eend)
repstr = Replace(mystr, "TIMERSEC = 25", "TIMERSEC = 8")  ' mystr being the variable value to change
vc.deletelines 1, eend
vc.insertlines 1, repstr
End If
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

[Torc]

Where does the error occur.

Would I be correct in saying that you have the names of workbooks you want
to operate on in column A of the Workbook the macro is run from?

[Mitch_s_s]

yeah all my file names are in column A

I can't see where the error occurs, as when i step through the code it works fine

it's when i set it off running on its own, its causes excel to crash

[westconn1]

you should not save or close activeworkbook, specify the workbook by name or object, looks like you may try to close the workbook where your code is

if you want intellisense to work try the code like this, pass the workbook or workbook name to your sub /function, i would use a function with a return of true or false is successful

dim w as workbook
Set w = Workbooks("somefile.xls")
With w.VBProject.VBComponents("module1").CodeModule
    eend = .CountOfLines
    mystr = .Lines(1, eend)
    repstr = Replace(mystr, "mystr = 8", "mystr = 24")
    .DeleteLines 1, eend
    .InsertLines 1, repstr
End With
w.save
w.close

[Mitch_s_s]

still not working

modified code is below

Sub open_workbooks()
Dim cell As Range
 
For Each cell In Range("A2", Range("A2").End(xlDown))
    Application.EnableEvents = False
    Workbooks.Open (cell.Value)
    Application.EnableEvents = True
    update_Timer
    cell.Interior.Color = 13434828
Next cell
 
End Sub
 
Sub update_Timer()
Dim vc As Object
Dim eend As Long
Dim mystr As String, repstr As String
Dim w As Workbook
 
Set w = ActiveWorkbook
 
If w.VBProject.VBComponents("XTAQ").codemodule.Find("TIMERSEC = 25", 1, 1, eend, 1, False, False, False) Then  'only do anything if that variable and value are found in the module
With w.VBProject.VBComponents("XTAQ").codemodule
    eend = .CountOfLines
    mystr = .Lines(1, eend)
    repstr = Replace(mystr, "mystr = 8", "mystr = 24")
    .DeleteLines 1, eend
    .InsertLines 1, repstr
End With
End If
w.Save
w.Close
End Sub

[westconn1]

i copied your code into excel module with a few minor changes, as none of my workbooks have a module of that name, put a couple of workbook names in column A, you code ran fine, both workbook modules had update code

so what is not working?

[Torc]

I think most of the problem may be solved if you stop using ActiveWorkbook.

Although you use w as the Workbook you are still closing it, and it is
explicitly set to be the ActiveWorkbook by the following

Set w = ActiveWorkbook

so you are really still trying to close the ActiveWorkbook.

Try the following.

Sub open_workbooks()
Dim cell As Range
 
For Each cell In Range("A2", Range("A2").End(xlDown))
    Application.EnableEvents = False
    Application.EnableEvents = True
    update_Timer cell.value
    cell.Interior.Color = 13434828
Next cell
 
End Sub
 
Sub update_Timer(ByVal WbkName as String)
Dim vc As Object
Dim eend As Long
Dim mystr As String, repstr As String
Dim w As Workbook
 
Set w = Workbook.Open(WbkName)
 
If w.VBProject.VBComponents("XTAQ").codemodule.Find("TIMERSEC = 25", 1, 1, eend, 1, False, False, False) Then  'only do anything if that variable and value are found in the module
With w.VBProject.VBComponents("XTAQ").codemodule
    eend = .CountOfLines
    mystr = .Lines(1, eend)
    repstr = Replace(mystr, "mystr = 8", "mystr = 24")
    .DeleteLines 1, eend
    .InsertLines 1, repstr
End With
End If
w.Save
w.Close
End Sub

If this is still giving problems then I suggest that you liberally pepper the code with Debug.Print statements such as

Sub open_workbooks()
Dim cell As Range
Debug.Print "Starting open_workbook"
For Each cell In Range("A2", Range("A2").End(xlDown))
    Debug.Print "Cell Address " & cell.Address & " : " & Cell.Value
    update_Timer cell.value
    Debug.Print "Finished update_timer"
    cell.Interior.Color = 13434828
    Debug.Print "Cell InteriorColor set"
Next cell
Debug.Print "Finished open_workbook"
End Sub

[westconn1]

i did suggest passing the workbook to the sub, but if you do as torc suggests then you need to move the application.enableevents to where the workbook is opened
here is another option

Sub open_workbooks()
      Dim cell As Range
       
      For Each cell In Range("A2", Range("A2").End(xlDown))
          Application.EnableEvents = False
          Set w = Workbooks.Open(cell.Value)
          Application.EnableEvents = True
          update_Timer w
          cell.Interior.Color = 13434828
      Next cell
       
      End Sub
       
      Sub update_Timer(w As Workbook)
      Dim vc As Object
      Dim eend As Long
      Dim mystr As String, repstr As String
       
'      If w.VBProject.VBComponents("XTAQ").codemodule.Find("TIMERSEC = 25", 1, 1, eend, 1, False, False, False) Then  'only do anything if that variable and value are found in the module
      With w.VBProject.VBComponents("module1").codemodule
          eend = .CountOfLines
          mystr = .Lines(1, eend)
          repstr = Replace(mystr, "mystr = 8", "mystr = 24")
          .DeleteLines 1, eend
          .InsertLines 1, repstr & vbNewLine & "'test done"
      End With
      w.Save
'      End If
      w.Close
      End Sub

[Mitch_s_s]

cheers mate, made the amendments as per your post

excel is falling over at wb.save, only after find and replace has been done

My security settings are ok, as I have allowed for programmatic access to vb

the files i am trying to update, do have digital certificates but the account I am using has the certificate installed.

[westconn1]

also you can move the enable evnts to outside the loop, so it is only run once, instead of every workbook