Programmatically replace code in VBA module in multiple MS Word documents.
Microsoft Word. VB/VBA
I would like to write a piece of code that finds all MS Word documents in a specific folder, and changes the code in the VBA attached to the documents. I.e. I would like to change the code in a series of documents rather than having to open each document individually, open the VBA, select all, paste new code, save document (there are hundreds of documents!)
Each document will have a module called "Save_Form" in it's VBA. It is the code in this modulte that needs changing. The changed code can come from a flat file, or even the current file (if written in VBA).
Is it even possible using the MSApplication or Word object to alter the VBA code?
Any help appreciated. Thanks.
I would like to write a piece of code that finds all MS Word documents in a specific folder, and changes the code in the VBA attached to the documents. I.e. I would like to change the code in a series of documents rather than having to open each document individually, open the VBA, select all, paste new code, save document (there are hundreds of documents!)
Each document will have a module called "Save_Form" in it's VBA. It is the code in this modulte that needs changing. The changed code can come from a flat file, or even the current file (if written in VBA).
Is it even possible using the MSApplication or Word object to alter the VBA code?
Any help appreciated. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Take your time, I'll be here to help if you need it :)
ASKER
Thanks I've got it working. I used the FileSystem object to iterate through the folder structure to find all applicatble files, which might be over complicated (could I have used Dir()?).
I also created a dialogue form to tell me what was happening (frmMacroUpdate) and run the procedure from that.
Here is the final code:
<--
Function UpdateVBACode(ByRef WDDocPath As String) As Boolean
Dim FileWithCode As String, WDDoc As Document
On Error GoTo Update_Error
'FileWithCode = "\\nhc-fs2\departments\EIA
FileWithCode = Me.fCodeFile
With Documents.Open(WDDocPath)
With .VBProject.VBComponents("S
.DeleteLines 1, .CountOfLines
.AddFromFile FileWithCode
End With
.Close True
End With
UpdateVBACode = True
Exit Function
Update_Error:
frmMacroUpdate.fMesg = "ERROR: Problem updating code in file: " & WDDocPath & _
"Error: " & Err.Number & Err.Description & vbCrLf & frmMacroUpdate.fMesg
UpdateVBACode = False
End Function
Function GetFiles(strPath As String, _
dctDict As Scripting.Dictionary, _
Optional blnRecursive As Boolean) As Boolean
' This procedure returns all the files in a directory into
' a Dictionary object. If called recursively, it also returns
' all files in subfolders.
Dim fsoSysObj As Scripting.FileSystemObject
Dim fdrFolder As Scripting.Folder
Dim fdrSubFolder As Scripting.Folder
Dim filFile As Scripting.File
' Return new FileSystemObject.
Set fsoSysObj = New Scripting.FileSystemObject
On Error Resume Next
' Get folder.
Set fdrFolder = fsoSysObj.GetFolder(strPat
If Err <> 0 Then
' Incorrect path.
GetFiles = False
GoTo GetFiles_End
End If
On Error GoTo 0
' Loop through Files collection, adding to dictionary.
For Each filFile In fdrFolder.Files
'Debug.Print Left(filFile.Name, 1)
'Debug.Print Right(filFile.Name, 4)
If LCase(Left(filFile.Name, 1)) = "s" And LCase(Right(filFile.Name, 4)) = ".doc" Then
dctDict.Add filFile.Path, filFile.Path
frmMacroUpdate.fMesg = filFile.Path & vbCrLf & frmMacroUpdate.fMesg
If UpdateVBACode(filFile.Path
frmMacroUpdate.fMesg = "ERROR: Problem updating code in file: " & filFile.Path & _
"Error: " & Err.Number & Err.Description & vbCrLf & frmMacroUpdate.fMesg
End If
DoEvents
' Do the macro update here
End If
Next filFile
' If Recursive flag is true, call recursively.
If blnRecursive Then
For Each fdrSubFolder In fdrFolder.SubFolders
If fdrSubFolder.Name <> "FULL EIA" Then
GetFiles fdrSubFolder.Path, dctDict, True
End If
Next fdrSubFolder
End If
' Return True if no error occurred.
GetFiles = True
Exit Function
GetFiles_End:
Exit Function
End Function
Sub TestGetFiles()
' Call to test GetFiles function.
Dim dctDict As Scripting.Dictionary
Dim varItem As Variant
Dim strDirPath As String
strDirPath = "\\nhc-fs2\departments\EIA
strDirPath = Me.fPath
frmMacroUpdate.fMesg = "Starting code update on all SCREENING documents below: " & strDirPath & vbCrLf
' Create new dictionary.
Set dctDict = New Scripting.Dictionary
' Call recursively, return files into Dictionary object.
If GetFiles(strDirPath, dctDict, True) Then
' Print items in dictionary.
For Each varItem In dctDict
Debug.Print varItem
Next
End If
frmMacroUpdate.fMesg = "Code update complete! " & vbCrLf & frmMacroUpdate.fMesg
End Sub
-->
Thanks for your help!
Iain.
I also created a dialogue form to tell me what was happening (frmMacroUpdate) and run the procedure from that.
Here is the final code:
<--
Function UpdateVBACode(ByRef WDDocPath As String) As Boolean
Dim FileWithCode As String, WDDoc As Document
On Error GoTo Update_Error
'FileWithCode = "\\nhc-fs2\departments\EIA
FileWithCode = Me.fCodeFile
With Documents.Open(WDDocPath)
With .VBProject.VBComponents("S
.DeleteLines 1, .CountOfLines
.AddFromFile FileWithCode
End With
.Close True
End With
UpdateVBACode = True
Exit Function
Update_Error:
frmMacroUpdate.fMesg = "ERROR: Problem updating code in file: " & WDDocPath & _
"Error: " & Err.Number & Err.Description & vbCrLf & frmMacroUpdate.fMesg
UpdateVBACode = False
End Function
Function GetFiles(strPath As String, _
dctDict As Scripting.Dictionary, _
Optional blnRecursive As Boolean) As Boolean
' This procedure returns all the files in a directory into
' a Dictionary object. If called recursively, it also returns
' all files in subfolders.
Dim fsoSysObj As Scripting.FileSystemObject
Dim fdrFolder As Scripting.Folder
Dim fdrSubFolder As Scripting.Folder
Dim filFile As Scripting.File
' Return new FileSystemObject.
Set fsoSysObj = New Scripting.FileSystemObject
On Error Resume Next
' Get folder.
Set fdrFolder = fsoSysObj.GetFolder(strPat
If Err <> 0 Then
' Incorrect path.
GetFiles = False
GoTo GetFiles_End
End If
On Error GoTo 0
' Loop through Files collection, adding to dictionary.
For Each filFile In fdrFolder.Files
'Debug.Print Left(filFile.Name, 1)
'Debug.Print Right(filFile.Name, 4)
If LCase(Left(filFile.Name, 1)) = "s" And LCase(Right(filFile.Name, 4)) = ".doc" Then
dctDict.Add filFile.Path, filFile.Path
frmMacroUpdate.fMesg = filFile.Path & vbCrLf & frmMacroUpdate.fMesg
If UpdateVBACode(filFile.Path
frmMacroUpdate.fMesg = "ERROR: Problem updating code in file: " & filFile.Path & _
"Error: " & Err.Number & Err.Description & vbCrLf & frmMacroUpdate.fMesg
End If
DoEvents
' Do the macro update here
End If
Next filFile
' If Recursive flag is true, call recursively.
If blnRecursive Then
For Each fdrSubFolder In fdrFolder.SubFolders
If fdrSubFolder.Name <> "FULL EIA" Then
GetFiles fdrSubFolder.Path, dctDict, True
End If
Next fdrSubFolder
End If
' Return True if no error occurred.
GetFiles = True
Exit Function
GetFiles_End:
Exit Function
End Function
Sub TestGetFiles()
' Call to test GetFiles function.
Dim dctDict As Scripting.Dictionary
Dim varItem As Variant
Dim strDirPath As String
strDirPath = "\\nhc-fs2\departments\EIA
strDirPath = Me.fPath
frmMacroUpdate.fMesg = "Starting code update on all SCREENING documents below: " & strDirPath & vbCrLf
' Create new dictionary.
Set dctDict = New Scripting.Dictionary
' Call recursively, return files into Dictionary object.
If GetFiles(strDirPath, dctDict, True) Then
' Print items in dictionary.
For Each varItem In dctDict
Debug.Print varItem
Next
End If
frmMacroUpdate.fMesg = "Code update complete! " & vbCrLf & frmMacroUpdate.fMesg
End Sub
-->
Thanks for your help!
Iain.
Hi Iain,
FSO will work, I just prefer to not create any objects (FSO, Dictionary) when there is a built-in way (Dir, arrays) to do the same thing. Since you are the end-user of this, I always recommend going with what you know (as you'll be the one maintaining this should it need it). If you're more comfortable with FSO, stick with it. Any runtime/memory differences won't really be noticeable.
Also, after reviewing my code, you could delete ", WDDoc As Document", since I just use a With block instead of setting the opened document to WDDoc (which I had originally).
Let me know if you need anything else
Matt
FSO will work, I just prefer to not create any objects (FSO, Dictionary) when there is a built-in way (Dir, arrays) to do the same thing. Since you are the end-user of this, I always recommend going with what you know (as you'll be the one maintaining this should it need it). If you're more comfortable with FSO, stick with it. Any runtime/memory differences won't really be noticeable.
Also, after reviewing my code, you could delete ", WDDoc As Document", since I just use a With block instead of setting the opened document to WDDoc (which I had originally).
Let me know if you need anything else
Matt
ASKER
I think this is a separate question, but now I want to change the document itself!
I think I need to put the reference document in administration document that holds my VBA.
I also need to copy all the form values from the target document, and insert them back into the "new" document once the update has taken place.
I think I can do most of this myself, but how do I change the document text rather than the vba code?
Thanks again.
I think I need to put the reference document in administration document that holds my VBA.
I also need to copy all the form values from the target document, and insert them back into the "new" document once the update has taken place.
I think I can do most of this myself, but how do I change the document text rather than the vba code?
Thanks again.
I can try to help, though I'm not all that familiar with the Word object.
Lets say you have 4 docs in your target folder s1.doc, s2.doc, s3.doc, s4.doc
and you're running your code from admin.doc
What exactly do you want to do to each s#.doc file?
Lets say you have 4 docs in your target folder s1.doc, s2.doc, s3.doc, s4.doc
and you're running your code from admin.doc
What exactly do you want to do to each s#.doc file?
ASKER
I will ask this in a new question (only fair!).
But to give you a head start: what I would like to do is take the document from admin.doc and use this to overite the document in s1.doc, s2.doc etc. (the equivalent of opening admin.doc and pressing <CTRL-A><CTRL-C>, then opening s1.doc and pressing <CTRL-A><CTRL-V>).
Because these documents are forms, I will copy all the form data in s1.doc etc before I change the document, then paste the values back into the named form fields.
Thanks again. Look out for the new question!
But to give you a head start: what I would like to do is take the document from admin.doc and use this to overite the document in s1.doc, s2.doc etc. (the equivalent of opening admin.doc and pressing <CTRL-A><CTRL-C>, then opening s1.doc and pressing <CTRL-A><CTRL-V>).
Because these documents are forms, I will copy all the form data in s1.doc etc before I change the document, then paste the values back into the named form fields.
Thanks again. Look out for the new question!
Before you decide to open up a new one (I won't reply to it as I don't know enough about how it will work with forms) give this code a try with one of your documents:
Selection.WholeStory
Selection.Copy
Documents.Open "C:\file.doc"
Selection.WholeStory
Selection.Paste
I know selection.wholestory will select all, so it only seems logical this would work
Selection.WholeStory
Selection.Copy
Documents.Open "C:\file.doc"
Selection.WholeStory
Selection.Paste
I know selection.wholestory will select all, so it only seems logical this would work
Excellent solution...if you are looking to replace code/manipulate code in a module. Does anyone know how this can be done against event triggers, such as Document_Open?
ASKER
I will try this tomorrow and get back to you.
Iain.