Programmatically Update Module
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
Re: Programmatically Update Module
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
vb 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
Re: Programmatically Update Module
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?
Re: Programmatically Update Module
wont let me leave you rep, says i need to spread it about a bit before giving it to you again ha!
Re: Programmatically Update Module
Excel keeps encountering an error when i set it off on its own
if i step through the code it works fine
any ideas?
vb Code:
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
Re: Programmatically Update Module
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?
Re: Programmatically Update Module
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
Re: Programmatically Update Module
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
vb Code:
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
Re: Programmatically Update Module
still not working :(
modified code is below
vb Code:
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
Re: Programmatically Update Module
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?
Re: Programmatically Update Module
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
Code:
Set w = ActiveWorkbook
so you are really still trying to close the ActiveWorkbook.
Try the following.
vb Code:
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
Code:
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
Re: Programmatically Update Module
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
vb Code:
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
Re: Programmatically Update Module
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.
Re: Programmatically Update Module
also you can move the enable evnts to outside the loop, so it is only run once, instead of every workbook