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.
LVL 15
colly92002Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
colly,

Sure it's possible.. just change the folder name in the MainSub, and the filename in the UpdateVBACode function.  If you'd prefer to have it take the code from a module in VBA, just let me know and I can easily modify it to do so.
Note, if you're using office xp or newer, you'll probably have to change the macro security settings to "Trust Access To VBA Project", or however it is worded.

Sub MainSub()
 Dim FName As String, FPath As String
 FPath = "C:\folder name\" 'make sure it ends with a \
 FPath = Dir(FPath & "*.doc")
 Application.ScreenUpdating = False
 Do Until Len(FPath) = 0
  UpdateVBACode FPath & FName
  FPath = Dir
 Loop
 Application.ScreenUpdating = True
End Sub

Function UpdateVBACode(ByRef WDDocPath As String) As Boolean
 Dim FileWithCode As String, WDDoc As Document
 FileWithCode = "C:\NewModuleCode.txt"
 With Documents.Open(WDDocPath)
  With .VBProject.VBComponents("Save_Form").CodeModule
   .DeleteLines 1, .CountOfLines
   .AddFromFile FileWithCode
  End With
  .Close True
 End With
End Function

Matt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colly92002Author Commented:
Excellent!  Thank you!

 I will try this tomorrow and get back to you.

Iain.
0
mvidasCommented:
Take your time, I'll be here to help if you need it :)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

colly92002Author Commented:
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\Administrator test files\FormUpdater\Save_Form.txt"
 FileWithCode = Me.fCodeFile
 
 With Documents.Open(WDDocPath)
  With .VBProject.VBComponents("Save_Form").CodeModule
   .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(strPath)
   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) = False Then
                    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\Administrator test files\FormUpdater\"
   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.
0
mvidasCommented:
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
0
colly92002Author Commented:
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.
0
mvidasCommented:
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?

0
colly92002Author Commented:
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!
0
mvidasCommented:
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
0
vopipCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.