Word 2007: Macro to edit macro?

[lisaharris]

I have over 1000 word documents with an embedded macro defining a merge data source. The server containing the merge data is changing.

Basically, the users export a data file from our mainframe system, then open the merge doc and click a button. The doc unprotects, browses out to the user's directory (based on their windows.application.username), opens the data source, merges it, puts the doc into preview mode, and reprotects it.

Does anyone have a way to edit a macro with a macro or vba script? The server path is the same in all documents -- right now it says \\swordfish\IL_merge and it needs to change to \\marlin\ilmerge

Any suggestions would be appreciated! (Even if the suggestion is to hire a temp to edit them manually!)

Thanks!

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[macropod]

Hi Lisa,

VBA support should be sought in the VBA forum. That said, give the following a try:

Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, wdDoc As Document
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  Call EditCode(wdDoc)
  wdDoc.Close SaveChanges:=True
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Sub EditCode(wdDoc As Document)
Dim VBC, VBComp
Dim i As Long, j As Long, bFnd As Boolean
Dim StrFnd As String, StrRep As String, StrNew As String
StrFnd = "\\swordfish\IL_merge" 'Text to Find
StrRep = "\\marlin\ilmerge" ' Replacement Text
Set VBC = wdDoc.VBProject.VBComponents
bFnd = True
For Each VBComp In VBC
  With VBComp.CodeModule
    i = 1
    j = .CountOfLines
    bFnd = .Find(Target:=StrFnd, StartLine:=i, StartColumn:=1, EndLine:=j, _
      EndColumn:=255, WholeWord:=True, MatchCase:=False, PatternSearch:=False)
    Do Until bFnd = False
      StrNew = Replace(.Lines(i, 1), StrFnd, StrRep)
      .ReplaceLine i, StrNew
      j = .CountOfLines
      bFnd = .Find(Target:=StrFnd, StartLine:=i, StartColumn:=1, EndLine:=j, _
        EndColumn:=255, WholeWord:=True, MatchCase:=False, PatternSearch:=False)
    Loop
  End With
Next VBComp
Set VBC = Nothing
End Sub

Simply add the code to a document, then run it & point its browser to the folder containing the documents to be updated.

Note: You will need to allow access to the VBA project object model to use the code (File|Options|Trust Centre|Trust Centre Settings|Macro Settings)

Cheers
Paul Edstein
[MS MVP - Word]