Work project - need macro to insert specific files

[sparklynic]

I have been saddled with a project in work which I have run into a brick wall
on. I am exporting from one computer program into word a list of fund names.
Each of these fund names requires a comment, which are also saved in word as
separate files each with its own unique filename. I am trying to use the
exported list of filenames to insert the correct corresponding comment using
a Macro. There are anywhere between 125-250 "current" comments at any one
time and these are updated 2-3 times a year. I am expecting anything from 10
up to 50 comments to be exported per client so really would like a macro to
expand all filenames in one go rather than individually with any filenames
that cannot be matched left in the original document with the word error or
something in front of it so that they can be manually inserted after the
macro has run.

My own attempt at a macro tried to block and cut (or copy) each of the
exported filenames in the list into an "insert file" command within a loop
until the last comment had been inserted. Trouble was it didn't like the
block and cut/copy command. It just used the same filename everytime and
didn't block and cut/copy the 2nd one onwards.

If anyone has got any bright ideas or different ways of approaching this
project having seen my thinking above, I would be very grateful.

Sparklynic

 

[Jay Freedman]

I have been saddled with a project in work which I have run into a brick wall
on. I am exporting from one computer program into word a list of fund names.
Each of these fund names requires a comment, which are also saved in word as
separate files each with its own unique filename. I am trying to use the
exported list of filenames to insert the correct corresponding comment using
a Macro. There are anywhere between 125-250 "current" comments at any one
time and these are updated 2-3 times a year. I am expecting anything from 10
up to 50 comments to be exported per client so really would like a macro to
expand all filenames in one go rather than individually with any filenames
that cannot be matched left in the original document with the word error or
something in front of it so that they can be manually inserted after the
macro has run.

My own attempt at a macro tried to block and cut (or copy) each of the
exported filenames in the list into an "insert file" command within a loop
until the last comment had been inserted. Trouble was it didn't like the
block and cut/copy command. It just used the same filename everytime and
didn't block and cut/copy the 2nd one onwards.

If anyone has got any bright ideas or different ways of approaching this
project having seen my thinking above, I would be very grateful.

Sparklynic

I can't tell from your description where you're running into a problem --
whether your macro gets the wrong filename from the list, or whether it gets the
right filename but then does the wrong thing with it.

Assuming you can get the correct filename for each case, I'd recommend having
the macro insert an INCLUDETEXT field containing that filename. If the file
exists, the field will display the file's contents. If there is no matching
comment, insert a "filename" that doesn't name any existing file, and the field
will automatically display your error message.

As an example of how your macro can insert such a field:

Sub demo()
Dim FName As String
Dim oFld As Field

FName = "C:\temp\lorem3.doc" ' example only!

Set oFld = ActiveDocument.Fields.Add(Range:=Selection.Range, _
Type:=wdFieldIncludeText, _
Text:=Replace(FName, "\", "\\"), _
PreserveFormatting:=False)

oFld.Unlink
End Sub

The Replace function accounts for the fact that all backslashes in a path in an
INCLUDETEXT field must be doubled. The Unlink command then changes the field
into plain text so it's no longer linked to the comment file.

 

[sparklynic via OfficeKB.com]

Jay

I'm sorry I should have said - the macro runs ok, but I do NOT get the
correct filename for each case. It uses the first filename it copies for all
coment insertions no matter how many there are or what they should be. In
effect I get the same comment again and again.

Niki

__________________________

 

[Jay Freedman]

Hi Niki,

In that case, I need you to copy the part of your code that gets the filename
and paste it into a reply to this message. There are far too many possible
problems to be able to guess which one you have.