Documents.Open using a variable for filename

[Stacey Bailey]

We have recently changed our standard terminology and need to replace old
terms with new in a large number of documents (>400). Rather than doing
this manually or even one file at a time, I'd much rather create a listing
of the files that must be replaced and have a macro that automatically opens
each, performs the replacement, saves, and closes documents. (I think I
might be able to reuse this logic many many...many times in the future.)
However, I'm running into one snag. I can't get my Documents.Open to accept
a FileName represented by a variable rather than an explicit string. Below
is the code I'm working with, which errors out at the moment at
Documents.Open.

Can anyone help?

'Select first row in file listing file

While Selection.Text <> ""
MyFile = Trim(Selection.Text)
Selection.TypeBackspace 'Remove entry now that it has been read

Documents.Open FileName:=MyFile, ConfirmConversions _
:=False, ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto,
XMLTransform:=""

' Execute search and replace macro
'Save and close the file

Selection.MoveEnd Unit:=wdLine, Count:=1
Wend

Thanks,
Stacey Bailey

 

[Jay Freedman]

Hi Stacey,

I suspect that the problem is not with Document.Open or with having the
filename in a variable. More likely it's that the value of the variable
MyFile isn't a valid filename. Try running the macro one statement at a time
by pressing F8 in the VBA editor. When the highlight passes from the "MyFile
=" statement to the TypeBackspace statement, hover the mouse over the MyFile
variable to see what its value is.

If it contains a square, that represents the paragraph mark at the end of
the line. Note that the Trim function removes leading and trailing spaces,
but not paragraph marks. A quick way to eliminate paragraph marks would be
to change the "MyFile =" statement to

MyFile = Trim(Replace(Selection.Text, vbCr, ""))

Once you get past that error, you'll find that the macro as written gets a
similar error after the last listed file has been processed. That's because
Word won't let you delete the final paragraph mark, so the Selection.Text is
never just an empty string, and the condition in the While statement is
always true. When the Selection is down to the final paragraph mark, MyFile
will be an empty string, and the Open statement will error again. The
solution here is to change the While statement to

While Selection.Text <> vbCr

I have a couple more comments, a bit off the topic of your post. One is that
if you don't want to lose your list of 400+ files, you must be very careful
to run this destructive macro only on a copy of the list, never on the
original. In general, it's a bad idea to delete information as you process
it -- there are better methods that are nondestructive.

A second comment is that, with all that opening and closing of documents
going on, you may find that Word becomes confused about which document the
Selection is in. You should define one Document object for the document that
contains the list of filenames and another Document object for the result of
the Open statement, and then use only Range objects inside those documents
to do your manipulations instead of using the Selection.

For example, to get the filenames, a loop like this won't destroy the list
and won't lose track of its location:

Dim MyRange As Range
Dim MyFile As String
Dim ListDoc As Document, ProcessDoc As Document
Dim MyPara As Paragraph

Set ListDoc = ActiveDocument

For Each MyPara In ListDoc.Paragraphs
Set MyRange = MyPara.Range
MyRange.MoveEnd wdCharacter, -1 ' exclude para mark
MyFile = Trim(MyRange.Text)

If MyFile <> "" Then
Set ProcessDoc = Documents.Open(FileName:=MyFile, _
ConfirmConversions:=False, ReadOnly:=False, _
AddToRecentFiles:=False, Format:=wdOpenFormatAuto)

' Execute search and replace macro
' on ProcessDoc.Range

'Save and close ProcessDoc
End If
Next MyPara

 

[Charles Kenyon]

I don't see anything wrong with your code so long as "MyFile" is a valid
filename. You may also want to look at
http://word.mvps.org/FAQs/MacrosVBA/BatchFR.htm. To use it, you would copy
all the files you want to change into a single folder, run the macro, and
then move them back to their original folders. It might be easier than what
you are attempting.
--
Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide

See also the MVP FAQ: http://www.mvps.org/word which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

 

[Helmut Weber]

Hi Stacey,

above all, what error?

What do you mean with "row", are the filenames in a table?

What is "Trim" in this context good for?

Quote:
Returns a Variant (String) containing a copy of a specified string
without leading spaces (LTrim), trailing spaces (RTrim), or both
leading and trailing spaces (Trim).

Are all files in the same directory?


Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Stacey Bailey]

Never mind. I got it. It was including the paragraph mark at the end of my
selection text. Here's my revised code that works.

Sub Replace_All_in_Set_of_Files()
'
' Replace_All_in_Set_of_Files Macro
' Macro created 08/24/2005 by Stacey Bailey
'
Dim MyFile As String

'Request input path - no slash at end
MsgBox ("Please include END as the last line in the input file with a
carriage return after it")
Path = InputBox("Path for files (no trailing slash, please):", "Path")
'Go to the beginning of the file
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1
'Read first element
Selection.MoveEnd Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeBackspace
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
'Loop through files
While Selection.Text <> "END"
MyFile = Chr(34) & Path & "\" & Trim(Selection.Text) & Chr(34)
'Remove entry now that it has been read
Selection.TypeBackspace
'Open the file
Documents.Open FileName:=MyFile, ConfirmConversions _
:=False, ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto,
XMLTransform:=""
'Execute Replacement Macro
Application.Run MacroName:="SDF_ReplaceTerms"
'Save and Close file
ActiveDocument.Save
ActiveDocument.Close
' Read next file to process
Selection.MoveEnd Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeBackspace
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Wend
MsgBox ("All done.")
End Sub