[cristobal03]

Dang it, I hate hitting the escape key by accident right after typing a long post.

Okay, the error you got was because you need a trailing backslash at the end of the FILE_PATH constant assignment to denote it as a directory. It should've been

Const FILE_PATH As String = "Y:\Client Services\CS\"

I've changed the code below to reflect your directory tree.

The old code is scrapped. The new (working) code is below. There are three big changes: dumped GetObject for Documents.Open, swapped Replace for a more Word-native Find method, and changed the address constants to String objects because you can't programmatically add a tab character to a constant.

Here it be:

Public Sub FixAddresses()
  Dim wdDoc As Document
  Dim strFileName As String
  Dim OLD_ADDRESS As String
  Dim NEW_ADDRESS As String

  'Change the path to point to the appropriate folder
  Const FILE_PATH As String = "Y:\Client Services\CS\"

  'Change the constants below to reflect the appropriate
  'street addresses including trailing spaces.  Chr$(9)
  'represents a tab.
  OLD_ADDRESS = "XXXXXXXXXXXXX  " & Chr$(9) & _
                                    Chr$(9) & _
                                    Chr$(9) & _
                                    Chr$(9) & _
                                    Chr$(9) & _
                                    Chr$(9) & _
                                    Chr$(9) & "         "
  NEW_ADDRESS = "YYYYYYYY " & Chr$(9) & _
                              Chr$(9) & _
                              Chr$(9) & _
                              Chr$(9) & _
                              Chr$(9) & _
                              Chr$(9) & _
                              Chr$(9) & _
                              Chr$(9) & "         "
  
  Application.ScreenUpdating = False

  strFileName = Dir(FILE_PATH)
  Do While Len(strFileName & vbNullString) <> 0
    Set wdDoc = Word.Documents.Open(FILE_PATH & strFileName)

    With ActiveDocument
      With .Range.Find
        .ClearFormatting
        .Text = OLD_ADDRESS
        With .Replacement
          .ClearFormatting
          .Text = NEW_ADDRESS
        End With
        .Execute Replace:=wdReplaceAll, _
                 Format:=True, MatchCase:=True, _
                 MatchWholeWord:=True
      End With
      .Save
      .Close
    End With

    Set wdDoc = Nothing
    strFileName = Dir
  Loop

  Application.ScreenUpdating = True
End Sub

You'll have to play with the address "constants" to get the spacing just right. If you need help with that, let me know; or, if the way I've assigned them now (on multiple lines) is confusing you, let me know that too.

HTH, test it on your copies

chris.

PS tested on 64 docs, took about 30 seconds. GL

[valis]

testing it now, will update.......thx again, man.....when this works, you got dinner/drinks next time you hit texas....

[valis]

having issues....correct me if I am wrong, but doesn't .clearformatting mean that the search and replace functoin looks for test regardless of the formatting? the prob I am running into is that it isn't finding the text to replace, and I feel that is becuase of the text being formatted as bold.....

[cristobal03]

More than likely it's the address "constant" assignments. In my test documents the font was also bold, and there were no problems. Check the number of spaces and tab characters on the line, then match that against the number of spaces and tab characters in the code.

I think it's the spacing because that has always been the biggest issue. Since the replacement constant MatchWholeWord is set to True, the spaces have to be exactly the same, in the exact same sequence as in the original.

[40 minutes later]

Wow, sorry, just got pulled into a meeting. So, yeah, I think it's the address strings.

Try it without any trailing spaces and see what that does.

chris.

[valis]

noodled it out....something in the OLD_ADDRESS/NEW_ADDRESS wasn't jibing, so I hit record, did what I needed to, and threw that code in there.....now to figure out the spacing....

thanks again, man...you've not only saved me the monotony of opening, finding, replacing, and saving thousands of files, but taught me some vba as well....will update once everything is hunky dory.

[valis]

Originally Posted by cristobal03

More than likely it's the address "constant" assignments. In my test documents the font was also bold, and there were no problems. Check the number of spaces and tab characters on the line, then match that against the number of spaces and tab characters in the code.

will try that as well.....

[cristobal03]

...Kind of curious to see the code as it is now? With the inserted macro code, I mean.

What I meant about trying it without the trailing spaces is changing the lines to

OLD_ADDRESS = "Your old address"
NEW_ADDRESS = "P.O. Box whatever"

and running that on your test files. You may not even have to worry about the spacing if the strings are a similar length.

chris.

[valis]

KNEW you'd want to see....almost embarrassed to show you what i've kludged up on your nice program......BE NICE:

Public Sub FixAddresses()
Dim wdDoc As Document
Dim strFileName As String
Dim OLD_ADDRESS As String
Dim NEW_ADDRESS As String

'Change the path to point to the appropriate folder
Const FILE_PATH As String = "Y:\Client Services\CS\"

'Change the constants below to reflect the appropriate
'street addresses including trailing spaces. Chr$(9)
'represents a tab.
OLD_ADDRESS = "XXXXXX" & Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & " "
NEW_ADDRESS = "P.O. Box XXXXX" & Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & _
Chr$(9) & " "

Application.ScreenUpdating = False

strFileName = Dir(FILE_PATH)
Do While Len(strFileName & vbNullString) <> 0
Set wdDoc = Word.Documents.Open(FILE_PATH & strFileName)

With ActiveDocument
With .Range.Find
.ClearFormatting
.Text = "XXXXXXXXXXXXXX"
With .Replacement
.ClearFormatting
.Text = "P.O. Box XXXXX "
End With
.Execute Replace:=wdReplaceAll, _
Format:=True, MatchCase:=True, _
MatchWholeWord:=True
.ClearFormatting
.Text = "77024"
With .Replacement
.ClearFormatting
.Text = "77224"
End With
.Execute Replace:=wdReplaceAll, _
Format:=True, MatchCase:=True, _
MatchWholeWord:=True
End With
.Save
.Close
End With

Set wdDoc = Nothing
strFileName = Dir
Loop

Application.ScreenUpdating = True
End Sub

[cristobal03]

Is it working for you? That's all that really matters. Otherwise, try this:

Public Sub FixAddresses()
  Dim wdDoc As Document
  Dim strFileName As String

  'Change the path to point to the appropriate folder
  Const FILE_PATH As String = "Y:\Client Services\CS\"

  'Change the constants below to reflect the appropriate
  'street addresses.
  Const OLD_ADDRESS As String = "Your old address"
  Const OLD_ZIP As String = "77024"
  Const NEW_ADDRESS As String = "P.O. Box whatever"
  Const NEW_ZIP As String = "77224"

  Application.ScreenUpdating = False

  strFileName = Dir(FILE_PATH)
  Do While Len(strFileName & vbNullString) <> 0
    Set wdDoc = Word.Documents.Open(FILE_PATH & strFileName)

    With ActiveDocument
      With .Range.Find

        ' Replace the street address first
        .ClearFormatting
        .Text = OLD_ADDRESS
        With .Replacement
          .ClearFormatting
          .Text = NEW_ADDRESS
        End With
        .Execute Replace:=wdReplaceAll, _
                 Format:=True, MatchCase:=True, _
                 MatchWholeWord:=True

        ' Now replace the zip code
        .ClearFormatting
        .Text = OLD_ZIP
        With .Replacement
          .ClearFormatting
          .Text = NEW_ZIP
        End With
        .Execute Replace:=wdReplaceAll, _
                 Format:=True, MatchCase:=True, _
                 MatchWholeWord:=True
      End With
      .Save
      .Close
    End With

    Set wdDoc = Nothing
    strFileName = Dir
  Loop

  Application.ScreenUpdating = True
End Sub

If your code works, no worries. It pretty much does the same thing as what I posted here; or, rather, this version of my code is more closely geared toward your specific requirement.

HTH, and really I couldn't say anything bad about what you wrote, because yours worked for you better than mine did (which is all that really matters).

chris.

[edit]
Sorry, you'll still need to change the value of OLD_ADDRESS and NEW_ADDRESS to match your addresses. But don't worry about the trailing spaces.
[/edit]

[valis]

it worked fine, and I GREATLY appreciate all the assistance.....now, just to figure out a way to change the date field to accurately represent the date......AND to change the amt of pages to '1'.....

oh yeah, and then to fax it for me.....

chris, thanks again for all your assistance. Couldn't have done it without you.

tim

[cristobal03]

Those are going to be a lot harder; that information is almost certainly not static. They're in a table, but Word handles tables much differently than Excel. There is a Table object in Word, but I don't have a clue how it could be used to find a particular cell. I mean, intuitively, it would be something like

Tables(Index).Rows(Index).Cells(Index)

but I'm almost positive Word can't do that. If it could, you could just set that as the selection and set the selection as the range, then replace all the text in the range with the proper date/number of pages.

Boy. Let me look at some stuff about tables.

chris.

[valis]

Chris, don't sweat it.....I told word to find/replace with " " the date range.....as for the pages, to heck with it....any that are wrong I can hand write in......would that they were in excel, as that I could figure out.....next incarnation, I feel, will be in excel, if I have anything to say about it.

[cristobal03]

Wow, shows how much I know about Word. That about which I was

almost positive

is exactly the opposite of how Word handles tables. That is, my intuition is correct. Will post a fix in a minute.

chris

[cristobal03]

[bump]

Got it. I'm posting two code blocks. The first is the entire subroutine, including fixes for the "Date" and "Number of pages" rows. The only thing you have to change are the OLD_ADDRESS and NEW_ADDRESS constants (make sure the zip codes are correct).

Here is that code:

Public Sub FixAddresses()

' Written for TSG member valis; swaps an old address
' on a fax cover sheet for a new one.

  Dim wdDoc As Document
  Dim strFileName As String

  'Change the path to point to the appropriate folder
  Const FILE_PATH As String = "C:\Cover Sheets\"

  'Change the constants below to reflect the appropriate
  'street addresses.
  Const OLD_ADDRESS As String = "Your old address"
  Const OLD_ZIP As String = "77024"
  Const NEW_ADDRESS As String = "P.O. Box whatever"
  Const NEW_ZIP As String = "77224"

  Application.ScreenUpdating = False

  strFileName = Dir(FILE_PATH)
  Do While Len(strFileName & vbNullString) <> 0
    Set wdDoc = Word.Documents.Open(FILE_PATH & strFileName)

    With ActiveDocument
      With .Range.Find

        ' Replace the street address first
        .ClearFormatting
        .Text = OLD_ADDRESS
        With .Replacement
          .ClearFormatting
          .Text = NEW_ADDRESS
        End With
        .Execute Replace:=wdReplaceAll, _
                 Format:=True, MatchCase:=True, _
                 MatchWholeWord:=True

        ' Now replace the zip code
        .ClearFormatting
        .Text = OLD_ZIP
        With .Replacement
          .ClearFormatting
          .Text = NEW_ZIP
        End With
        .Execute Replace:=wdReplaceAll, _
                 Format:=True, MatchCase:=True, _
                 MatchWholeWord:=True
      End With

      ' Fix date
      .Tables(1).Rows(1).Cells(2).Select
      Selection.Text = Format(Date, "mmmm d, yyyy")

      ' Fix number of pages
      .Tables(1).Rows(6).Cells(2).Select
      Selection.Text = "1"

      .Save
      .Close
    End With

    Set wdDoc = Nothing
    strFileName = Dir
  Loop

  Application.ScreenUpdating = True
End Sub

The second code block will fix the "Date" and "Number of pages," if you don't want to bother with running the whole thing again. Here is that code:

Public Sub FixTables()
  Dim strFileName As String

  'Change the path to point to the appropriate folder
  Const FILE_PATH As String = "Y:\Client Services\CS\"

  Application.ScreenUpdating = False

  strFileName = Dir(FILE_PATH)
  Do While Len(strFileName & vbNullString) <> 0
    Set wdDoc = Word.Documents.Open(FILE_PATH & strFileName)

    With ActiveDocument

      ' Fix date
      .Tables(1).Rows(1).Cells(2).Select
      Selection.Text = Format(Date, "mmmm d, yyyy")

      ' Fix number of pages
      .Tables(1).Rows(6).Cells(2).Select
      Selection.Text = "1"

      .Save
      .Close
    End With

    Set wdDoc = Nothing
    strFileName = Dir
  Loop

  Application.ScreenUpdating = True
End Sub

As for the faxing, I can't help you there.

GL, I think I'm about spent on this thread. If you need any explanations of what I've done, I'll post back when I get off work (man, I'm such a horrible employee).

chris.

[valis]

chris: This is WAY more than I expected.....I've already started the fax process....read up a few....

I will give this a shot, and let you know regardless.....thanks again, man, for all the hard work and follow up. Horrible employee, indeed. Gimme a break. They need to be paying you double, regardless. Just out of curiousity, what industry you work in?