[valis]

hello, I've got an interesting problem. We are moving our mailing address to a p.o. box, and I need to notify all of our clients. The best (and easiest, I feel) way to do this is by updating our cover sheets, as ALL clients have one of those. If all clients had email, I would say that would be the easiest, but only about 85% of them have email. So I've decided that the easiest way is to update all the cover sheets, and just send a fax to everyone. My question is thus:

is there a way to change the address in each document (cover sheet) without opening each one? There are well over a thousand cover sheets, and I don't know enough of VBA to enable me to write a macro that opens one, finds XXXXX and replaces it with YYYYY, saves, prints, closes, then opens the next file.

Any ideas?

[cristobal03]

I don't suppose we're lucky enough that the address is associated with a bookmark?

Could you upload the cover sheet template?

chris.

[valis]

et voila:

[valis]

was sort of hoping you would show up, chris....I've seen some of your other replies, and you seem to have VBA by the throat.....thanks.....

v

[cristobal03]

Woo, buddy. This'll be a mess, at best. Some notes/first impressions, so I don't forget:

1) Keep in mind that your cover sheet has been arranged manually using spaces, so when you change the address you also have to replace the trailing spaces. (This is probably the biggest hang-up.)

2) Do all your cover sheets reside in one directory?

3) Since the street address appears only once on the cover sheet, you should be able to use the Replace method to swap text for text. But because of the trailing spaces, you'd need to declare some constants including the spaces. For example, the constant representing the address in the posted file would be

Const OLD_ADDRESS As String = "XXXXXXXXXXXXX  							         "

NOT

Const OLD_ADDRESS As String = "XXXXXXXXXXXXX"

If that makes any sense. The constant NEW_ADDRESS would be the same way.

Uh, so, I guess, let me know about where the cover sheet files are kept and I'll start banging out some code. It might be tomorrow before I get to it; my day's almost up here, sorry 'bout that.

chris.

[cristobal03]

[bump]

Thinking now it won't be as tricky as I first assumed.

Also, thanks for the compliment

chris.

[valis]

chris....no worries, mate, as wolfey would say....i know hwere you are coming from as I am writing this from home, and template is at work....to wit:

1. Yes, I am aware of the leading and trailing spaces issue....would it be easier to simply create a template, and then import only the client name, and fax number? Both of those are in individual cells in a table in the document....something to consider, I guess.

2. Yes, the are all in the same directory.

3. it makes sense......

as for the compliment, those are not given out, um, complimentarily. From what I've seen you post, you know what yer talking about.....I can fuddle around with vba in excel, but word, for anything other than typing, has always somewhat befuddled ME.

and no, I did NOT design the cover sheet.....

tim

[cristobal03]

Just thinking about something, are the cover sheets the only files in the directory?

[valis]

yes.

[cristobal03]

[edit]
The below code was untested and broken. Future readers needing a similar requirement, working code may be found in post #16.
[/edit]

Okay, I got some code but no way to test it. Can you copy your cover sheets directory to a temp location and run this on that folder to see if it works for you?

Mind, this might be super-buggy, or really slow. Opening/modifying/closing 1K documents in one loop might be...tricky. Also, despite your compliments, I really don't know much about VBA at all.

Anyway, here it is:

Public Sub FixAddresses()
  Dim wdApp As New Word.Application
  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 including trailing spaces.
  Const OLD_ADDRESS As String = "XXXXXXXX                   "
  Const NEW_ADDRESS As String = "YYYYYYYY                   "

  strFileName = Dir(FILE_PATH)
  Do While Len(strFileName & vbNullString) <> 0
    Set wdApp = GetObject(strFileName, "Word.Application")
    With wdApp
      .Activate 'not sure this is necessary
      With .ActiveDocument
        .Range = Replace(.Range, OLD_ADDRESS, NEW_ADDRESS)
        .Save
        .Close
      End With
    End With
    Set wdApp = Nothing
    strFileName = Dir
  Loop
End Sub

One thing that I'd like to know (if I had a way to test this) is whether or not wdApp has to be dimensioned as a New object. Also, like I commented, I don't know if wdApp.Activate is necessary or just a process hog.

See if it works for you, let me know. In the meantime I might try to duplicate the conditions on my box using the sample cover sheet you posted.

chris.

[valis]

testing it now....will post shortly....that is, unless the pc explodes or something....

chris, thanks.

tim

[cristobal03]

Gah, you're going to get an error. There's something wrong with the GetObject line.

Lemme work on it a bit, I don't know what's going on.

chris.

[valis]

okay, getting a syntax error at the below line:

strFileName = Dir(Y:\Client Services\CS)

[cristobal03]

Could you post the code as you've modified it (not the address constants, of course)?

Also, I've started testing this out, and there's something odd happening...the code is completely reformatting the whole file. I don't know what's causing that, let me look at it a little more.

chris.

[valis]

Public Sub FixAddresses()
Dim wdApp As New Word.Application
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 including trailing spaces.
Const OLD_ADDRESS As String = "XXXXXXXXXXXXXXXXXX "
Const NEW_ADDRESS As String = "P.O. Box 19351 "

strFileName = Dir(Y:\Client Services\CS)
Do While Len(strFileName & vbNullString) <> 0
Set wdApp = GetObject(strFileName, "Word.Application")
With wdApp
.Activate 'not sure this is necessary
With .ActiveDocument
.Range = Replace(.Range, OLD_ADDRESS, NEW_ADDRESS)
.Save
.Close
End With
End With
Set wdApp = Nothing
strFileName = Dir
Loop
End Sub