Combining Links

[Bryan Fegan]

Greetings,

I'm using links in a word document to dynamically pull
data from an excel file. I'd like to use DOCPROPERTY to
dynamically pull the path and file name for the source
file that should be used to populate the word document.
I've created the DOCPROPERTY to return the path and file
name but I'm having a difficult time combining them with
the imbedded links. Example below:

These are the links that return the path and the file
name:

{ DOCPROPERTY Path}{ DOCPROPERTY File}

This is an example of the link that I'm using to pull
data from the source file:

{ LINK Excel.Sheet.8 "C\\Documents and
Settings\\U\\Desktop\\Outcomes\\Outcomes.xls" "Sheet1!
R2C3" \a \f 4 \h \*MERGEFORMAT }

How do I replace the hard coded path in the link above
with the dynamic path and file name created with the
DOCPROPERTY reference?

Thanks in advance for your help.

Regards,
Bryan

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Bryan Fegan > écrivait :
In this message, < Bryan Fegan > wrote:

|| Greetings,
||
|| I'm using links in a word document to dynamically pull
|| data from an excel file. I'd like to use DOCPROPERTY to
|| dynamically pull the path and file name for the source
|| file that should be used to populate the word document.
|| I've created the DOCPROPERTY to return the path and file
|| name but I'm having a difficult time combining them with
|| the imbedded links. Example below:
||
|| These are the links that return the path and the file
|| name:
||
|| { DOCPROPERTY Path}{ DOCPROPERTY File}

What do these properties contain? I mean, can you post some examples. How do
you assign a value to these properties?

Also, I think that you need the path to have double slashes ( \\ ) as path
separator to work with LINK.
Did you plan for/test this with your {DOCPROPERTY Path}?
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Guest]

I created and set the "Path" and "File" properties on the
custom tab in the document properties window (File -->
Properties --> Custom).

The property "Path" specifies the directory where my
source file is located. Double slashes (\\) are being
used as path seperators.

The property "File" specifies the file name of the source
file.

{ DOCPROPERTY Path}{ DOCPROPERTY File} currently returns:

C:\\Documents and
Settings\\U\\Desktop\\Outcomes\\Outcomes.xls

Any idea how I can referenc the two DOCPROPERTYs above in
the following link (as opposed to having the path and the
file name hardcoded)?

{ LINK Excel.Sheet.8 "C:\\Documents and
Settings\\U\\Desktop\\Outcomes\\Outcomes.xls" "Sheet1!
R2C3" \a \af 4 \h \*MERGEFORMAT }

Sorry if this is confusing. I'm new to linking office
applications and there may be simpler ways to accomplish
the same objective.

Thank you.

Bryan Fegan

 

[Guest]

I guess what I'm looking for is how to do this:

{ LINK Excel.Sheet.8 "{ DOCPROPERTY Path}{ DOCPROPERTY
File}" "Sheet1! R2C3" \a \af 4 \h \*MERGEFORMAT }

Note the imbedded doc properties in the LINK. The
problem is, that doesn't work!

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < (e-mail address removed) > écrivait :

| I guess what I'm looking for is how to do this:
|
| { LINK Excel.Sheet.8 "{ DOCPROPERTY Path}{ DOCPROPERTY
| File}" "Sheet1! R2C3" \a \af 4 \h \*MERGEFORMAT }

It works, but only once. I mean, under Word XP, it works, but as soon as you
have updated the link once, the DOCPROPERTY fields are converted into
text.... I tried With REF and DOCVARIABLES fields, same results. So I guess
you can embed fields to specify a path and file name, but as soon as Word
has established the link, it removes the fields to put the real values.

Why do you need to do it this way?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Bryan]

Hello again - Just wanted to say thanks for your help in
advance.

If you can help me think of an easier way to accomplish
the same task then I would be very grateful.

Here is the problem I'm trying to solve:

An Access database is currently used to massage data
downloaded from an AS400. I'm not quite sure what's
going on in Access but the output is a fairly
sophisticated cross tab report. The data in the cross
tab report is used to populate multiple tables in a MS
Word document as well as to create pie and bar charts
which are also imbedded in the same word document. The
creation of the word document from the Access report is a
manual process and takes a lot of time to complete.

Since the cross tab report output is always the same
format and since the word document is always the same
format too I figure there must be a way to link fields in
the word document to the data in the cross tab report
(like a mail merge or a form letter).

My thought process was this.... Export the access report
to excel and create fields in the word document that link
to the appropriate excel worksheet that has the raw data.

I arrived at using docproperty PATH and FILE because I
wanted to be able to switch raw data source files on the
fly to quickly create different word documents. I guess
you could say that I want my word document to be a
template that can be used again and again with different
excel source files..

Sorry if this is a bit wordy - I don't know any other way
to explain it in email.

Regards,
Bryan

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Bryan > écrivait :
In this message, < Bryan > wrote:

| Hello again - Just wanted to say thanks for your help in
| advance.
|
| If you can help me think of an easier way to accomplish
| the same task then I would be very grateful.
|
| Here is the problem I'm trying to solve:
|
| An Access database is currently used to massage data
| downloaded from an AS400. I'm not quite sure what's
| going on in Access but the output is a fairly
| sophisticated cross tab report. The data in the cross
| tab report is used to populate multiple tables in a MS
| Word document as well as to create pie and bar charts
| which are also imbedded in the same word document. The
| creation of the word document from the Access report is a
| manual process and takes a lot of time to complete.
|
| Since the cross tab report output is always the same
| format and since the word document is always the same
| format too I figure there must be a way to link fields in
| the word document to the data in the cross tab report
| (like a mail merge or a form letter).
|
| My thought process was this.... Export the access report
| to excel and create fields in the word document that link
| to the appropriate excel worksheet that has the raw data.
|
| I arrived at using docproperty PATH and FILE because I
| wanted to be able to switch raw data source files on the
| fly to quickly create different word documents. I guess
| you could say that I want my word document to be a
| template that can be used again and again with different
| excel source files..
|
| Sorry if this is a bit wordy - I don't know any other way
| to explain it in email.
|

What Word version are you using?
Your plan would work on my machine with Word XP.

I did create fields like
{ LINK Excel.Sheet.8 "{ DOCPROPERTY Path}{ DOCPROPERTY File}" "Sheet1! R2C3"
\a \af 4 \h \*MERGEFORMAT }
that worked on my machine (except for " \af 4", my Word version did not
recognize that switch). The only thing is that it works only once, i.e.,
once you have established the link (updated it) the DOCPROPERTY fields are
gone and replaced by their value. But it would not matter in your case since
the template would retain the proper fields and only the generated documents
would have the links without the DOCPROPERTY fields.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Bryan]

Thanks Jean-Guy. You have been a very generous resource.

I've got 2003 on my laptop but I think I'll need to do
the work on one of our office computers which are running
either XP or 2000. I'll give it a try when I get to the
office tomorrow...

I'd like to keep you posted on my progress. I guess you
check this board regularly so I'll post feedback when I
complete the task. If you want to check in with how I'm
doing send me an email at (e-mail address removed).

Thanks again.

Regards,
Bryan

 

[Guest]

Actually, that will pose somewhat of a user challenge.
Can you think of any way to get around this hurdle? Is
there a way to accomplish the same objective without
using DOCPROPERTY?

I've never done any programming in office so I wouldn't
really know where to begin....

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < (e-mail address removed) > écrivait :

| Actually, that will pose somewhat of a user challenge.
| Can you think of any way to get around this hurdle? Is
| there a way to accomplish the same objective without
| using DOCPROPERTY?
|

In your original plan, how did you plan on changing the values of your
DOCPROPERTY fields for every new document?

Was the user suppose to go into the Property dialog to type in the new path?
Did you have an idea on automating this?
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Dave Lett]

Hi all,

Just had a quick look at your issue, and you might find the following
useful.

Dim sFilePath As String
Dim sFieldString
Dim sNewFilePath As String
Dim iCount As Integer

iCount = 3
sFieldString = ActiveDocument.Fields(1).Code

Do While Right(sFilePath, 3) <> "xls"
iCount = iCount + 1
sFilePath = Mid(sFieldString, 21, iCount)
Loop
sNewFilePath = InputBox("Insert the path of the new .xls file." & vbCrLf _
& "Be sure to use double backslashes.", _
Title:="InsertPath", _
Default:=sFilePath)

ActiveDocument.Fields(1).Code.Text = Replace(ActiveDocument.Fields(1).Code,
sFilePath, sNewFilePath)

You mentioned "user friendly" before, but never really described
what/when/how a user might interface with your routine.

HTH,
Dave

Actually, that will pose somewhat of a user challenge.
Can you think of any way to get around this hurdle? Is
there a way to accomplish the same objective without
using DOCPROPERTY?

I've never done any programming in office so I wouldn't
really know where to begin....