Solved

Programmatically replace code in VBA module in multiple MS Word documents.

Posted on 2007-04-03
10
1,841 Views
Last Modified: 2010-01-06
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.
0
Comment
Question by:colly92002
  • 5
  • 4
10 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 250 total points
ID: 18845124
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
 
LVL 15

Author Comment

by:colly92002
ID: 18845858
Excellent!  Thank you!

 I will try this tomorrow and get back to you.

Iain.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 18845899
Take your time, I'll be here to help if you need it :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Author Comment

by:colly92002
ID: 18850307
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
 
LVL 35

Expert Comment

by:mvidas
ID: 18850537
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
 
LVL 15

Author Comment

by:colly92002
ID: 18851112
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
 
LVL 35

Expert Comment

by:mvidas
ID: 18851586
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
 
LVL 15

Author Comment

by:colly92002
ID: 18857125
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
 
LVL 35

Expert Comment

by:mvidas
ID: 18857155
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
 

Expert Comment

by:vopip
ID: 26193980
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question