Using a conditional parameter within the LINK field

T

TishyMouse

I have a Word document that includes links to a number of tables in an Excel
spreadsheet. The links are defined in terms of defined names in the
spreadsheet. I'd like to be able to link to different tables depending on the
value of a custom property in the Word document but I can't get this to work.

e.g. the following works OK:
{LINK Excel.Sheet.8 \\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK
\f 0 \p}

but I'd like to use something like
{LINK Excel.Sheet.8 \\\\domain.com\\mypath\\MyExcelFile.xls
MySheet!Name_{DOCPROPERTY Region} \f 0 \p}

Where the names Name_UK, Name_US etc. are defined within the Excel sheet and
'Region' is a property of the Word document.

However it doesn't work at least with this syntax (I just get an 'Error! Not
a valid link' message). Does Word actually support this or does anyone know a
different way to implement this functionality? I realise one alternative
would be to have a number of nested {IF } statements for the different
regions but that will get very messy and hard to maintain.


TIA

TishyMouse
 
M

macropod

Hi TishyMouse,

Unfortunately, the approach you're after won't work as any field within a LINK field gets converted to its result the first time the
LINK field is updated. Consequently, you'll need to use IF tests coded along the lines of:
{IF{DOCPROPERTY Region}= "UK" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK" \f 0 \p} {LINK
Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US" \f 0 \p}}
or:
{IF{DOCPROPERTY Region}= "UK" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK" \f 0 \p}}
{IF{DOCPROPERTY Region}= "US" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US" \f 0 \p}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message. Note also the addition of the double quotes in the LINK fields - their omission may have had something to do
with your field's failure.
 
P

peter petrelli

Hi macropod,

My word field code reads

{ LINK Excel.Sheet.8 "D:\\uev\\Documents and Settings\\bb\\Desktop\\file.xlsx" "Hybrid!R3C40" \a \t }

I want to multiply 100 to this value. Can you please give me the code for this? I am quite new to Field codes. Thanks!



macropod wrote:

Hi TishyMouse,Unfortunately, the approach you are after will not work as any
19-Mar-10

Hi TishyMouse

Unfortunately, the approach you are after will not work as any field within a LINK field gets converted to its result the first time th
LINK field is updated. Consequently, you will need to use IF tests coded along the lines of
{IF{DOCPROPERTY Region}= "UK" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK" \f 0 \p} {LIN
Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US" \f 0 \p}
or
{IF{DOCPROPERTY Region}= "UK" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK" \f 0 \p}
{IF{DOCPROPERTY Region}= "US" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US" \f 0 \p}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you cannot simply type them or copy & past
them from this message. Note also the addition of the double quotes in the LINK fields - their omission may have had something to d
with your field's failure

-
Cheer
macropo
[Microsoft MVP - Word]

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Repeating Structures Table Looping and Table Extract
http://www.eggheadcafe.com/tutorial...0-a5704fe31a76/biztalk-repeating-structu.aspx
 
D

Doug Robbins - Word MVP

Use the following field construction

{ = { LINK Excel.Sheet.8 "D:\\uev\\Documents and
Settings\\bb\\Desktop\\file.xlsx" "Hybrid!R3C40" \a \t } * 100 }

Select your existing link, then use Ctrl+F9 to enclose it in a second pair
of field delimiters and then inser the = and the * 100 in the places as
shown above.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

in message news:[email protected]...
 
T

TishyMouse

Thanks for confirming that MP and saving my hair from more pulling out :). I
guess I'll have to go back to the {IF} syntax (or perhaps investigate using a
macro to do a global search and replace).

By the way AFAIK the double quotes aren't necessary unless there is a space
in any of the parameter values. My first example {LINK Excel.Sheet.8
\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK \f 0 \p} works fine.

TM

macropod said:
Hi TishyMouse,

Unfortunately, the approach you're after won't work as any field within a LINK field gets converted to its result the first time the
LINK field is updated. Consequently, you'll need to use IF tests coded along the lines of:
{IF{DOCPROPERTY Region}= "UK" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK" \f 0 \p} {LINK
Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US" \f 0 \p}}
or:
{IF{DOCPROPERTY Region}= "UK" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK" \f 0 \p}}
{IF{DOCPROPERTY Region}= "US" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US" \f 0 \p}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message. Note also the addition of the double quotes in the LINK fields - their omission may have had something to do
with your field's failure.


--
Cheers
macropod
[Microsoft MVP - Word]


TishyMouse said:
I have a Word document that includes links to a number of tables in an Excel
spreadsheet. The links are defined in terms of defined names in the
spreadsheet. I'd like to be able to link to different tables depending on the
value of a custom property in the Word document but I can't get this to work.

e.g. the following works OK:
{LINK Excel.Sheet.8 \\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK
\f 0 \p}

but I'd like to use something like
{LINK Excel.Sheet.8 \\\\domain.com\\mypath\\MyExcelFile.xls
MySheet!Name_{DOCPROPERTY Region} \f 0 \p}

Where the names Name_UK, Name_US etc. are defined within the Excel sheet and
'Region' is a property of the Word document.

However it doesn't work at least with this syntax (I just get an 'Error! Not
a valid link' message). Does Word actually support this or does anyone know a
different way to implement this functionality? I realise one alternative
would be to have a number of nested {IF } statements for the different
regions but that will get very messy and hard to maintain.


TIA

TishyMouse
Click to expand...

.
Click to expand...
 
M

macropod

Hi TishyMouse,

Yes, it's true that the double quotes aren't *required* unless the filename or path has spaces. Using them as a matter of course is
good practice though and, in this case, guards against any changes in the way Word works treating the {DOCPROPERTY Region} field as
if it were not part of the file/path name string (this is more relevant to INCLUDETEXT and INCLUDEPICTURE fields since, as
previously discussed, you can't use a field variable in a LINK field). Do note that the lack of spaces in your filepath, filename
and sheet reference were not apparent from your post. Conversely, I should have had:
"\\\\domain.com\\mypath\\MyExcelFile.xls" MySheet!Name_US
or:
"\\\\domain.com\\mypath\\MyExcelFile.xls" "MySheet!Name_US"
and not:
"\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US"


--
Cheers
macropod
[Microsoft MVP - Word]


TishyMouse said:
Thanks for confirming that MP and saving my hair from more pulling out :). I
guess I'll have to go back to the {IF} syntax (or perhaps investigate using a
macro to do a global search and replace).

By the way AFAIK the double quotes aren't necessary unless there is a space
in any of the parameter values. My first example {LINK Excel.Sheet.8
\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK \f 0 \p} works fine.

TM

macropod said:
Hi TishyMouse,

Unfortunately, the approach you're after won't work as any field within a LINK field gets converted to its result the first time
the
LINK field is updated. Consequently, you'll need to use IF tests coded along the lines of:
{IF{DOCPROPERTY Region}= "UK" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK" \f 0 \p} {LINK
Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US" \f 0 \p}}
or:
{IF{DOCPROPERTY Region}= "UK" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK" \f 0 \p}}
{IF{DOCPROPERTY Region}= "US" {LINK Excel.Sheet.8 "\\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_US" \f 0 \p}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message. Note also the addition of the double quotes in the LINK fields - their omission may have had something to
do
with your field's failure.


--
Cheers
macropod
[Microsoft MVP - Word]


TishyMouse said:
I have a Word document that includes links to a number of tables in an Excel
spreadsheet. The links are defined in terms of defined names in the
spreadsheet. I'd like to be able to link to different tables depending on the
value of a custom property in the Word document but I can't get this to work.

e.g. the following works OK:
{LINK Excel.Sheet.8 \\\\domain.com\\mypath\\MyExcelFile.xls MySheet!Name_UK
\f 0 \p}

but I'd like to use something like
{LINK Excel.Sheet.8 \\\\domain.com\\mypath\\MyExcelFile.xls
MySheet!Name_{DOCPROPERTY Region} \f 0 \p}

Where the names Name_UK, Name_US etc. are defined within the Excel sheet and
'Region' is a property of the Word document.

However it doesn't work at least with this syntax (I just get an 'Error! Not
a valid link' message). Does Word actually support this or does anyone know a
different way to implement this functionality? I realise one alternative
would be to have a number of nested {IF } statements for the different
regions but that will get very messy and hard to maintain.


TIA

TishyMouse
Click to expand...

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using DOCPROPERTY in LINK field 9
Can't link to Excel2007B2TR Spreadsheet Named Ranges from Word2007 2
How to reference a Sharepoint ContentType in field not QuickPart 3
Updating multiple worksheets within a document 1
Help with Tabbing within a Table 1
How do you insert a link in the footer 1
Create a link field between word and excel 1
can Word display a graphic from a link in excel file? (mail merge) 3

Top