Solved

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

Posted on 2007-04-03
10
1,644 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now