Editing links to excel in form-protected word document

[Laura]

I have a Word document that contains tables that are linked Excel worksheets
(Windows XP 2002). Some sections of the document are form protected, but the
sections in which the excel links are found are not (so that they can be
manipulated). Unfortunately, when the doc is locked, if I right-click on the
linked object and go to "Linked Worksheet Object", I can no longer select
"Links..." The practical result of this is that there is no way to change
the source document without re-pasting all of the tables, rather than through
a dialogue box... is there?

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?TGF1cmE=?=,

I have a Word document that contains tables that are linked Excel worksheets
(Windows XP 2002). Some sections of the document are form protected, but the
sections in which the excel links are found are not (so that they can be
manipulated). Unfortunately, when the doc is locked, if I right-click on the
linked object and go to "Linked Worksheet Object", I can no longer select
"Links..." The practical result of this is that there is no way to change
the source document without re-pasting all of the tables, rather than through
a dialogue box... is there?

Assuming these objects are formatted in-line with the text, you should be able
to edit the LINK field code directly by pressing Alt+F9 to toggle it on (and
then off).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Laura]

Cindy,
I don't know what you mean. If I select the excel worksheet object or put
my cursor next to it, & hit ALT+F9, nothing happens. Is a dialogue box
supposed to open? Or is there a way to have the "path" appear in text in the
doc so that it can be edited directly? (Why can't I just select "Links..."
like usual?!?!?)
Please help!

Thanks

 

[Suzanne S. Barnhill]

If nothing happens when you hit Alt+F9 (which toggles the display of fields
from field results to field codes and vice versa), then your link is not a
field, and therefore not a link.

 

[Laura]

Thanks, Suzanne, but I'm still not sure what you mean. Perhaps it is not a
"link" per se, but it certainly is linked. I put the Excel worksheet object
into the Word doc by doing a "Paste Special" and selecting "Paste Link."
Changes in the Excel file are reflected in the Word document.

I would like other users to be able to change the source file without having
to repaste all of the Excel tables, even when other sections of the Word doc
are locked as a form. Is there a way to do this? Why does that option
disappear when other sections of the doc are form protected? Thanks.

 

[Suzanne S. Barnhill]

If the linked object is wrapped, you won't see a field when you toggle field
display; it is displayed only when the linked object is In Line With Text.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?TGF1cmE=?=,

I don't know what you mean. If I select the excel worksheet object or put
my cursor next to it, & hit ALT+F9, nothing happens. Is a dialogue box
supposed to open? Or is there a way to have the "path" appear in text in the
doc so that it can be edited directly? (Why can't I just select "Links..."
like usual?!?!?)

When I say "inline with the text" I mean that no textwrap formatting has been
applied to the graphical object. You need to get these out of the drawing layer
in order for Edit/Links to "see" them.

Format them "in line with the text" and you should be able to see the links
(either through Edit/links or Alt+F9). Make sure they're in their own paragraph,
select them, then click the Insert Frame button on the FORMS toolbar. Now you
can position them with text wrapping around the frame, and the graphic's link is
still accessible.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Laura]

Thanks for the explanation, but unfortunately that is not the issue. The
Edit/Links & ALT+F9 functions both work beautifully when I paste in my Excel
charts, since they are formatted in line with text and there are no text
wrapping issues.

The problem occurs when the Word document is LOCKED. Parts of my document
are form-protected for other users, but since it is meant to be a sort of
template, they need to be able to reference a different spreadsheet file for
the excel charts. I have tried to retain the Edit/Links & ALT+F9
functionality by placing the excel charts in UNPROTECTED SECTIONS of the word
document, but the Edit/Links ability disappears completely when the LOCK is
flipped on. There are lots of charts, so re-pasting each one would not work.
I would also not like to allow users to unlock the document. Is there some
way to get around this? i.e., Change some aspect of the form-protection?
Have the file path appear next to the linked object in hidden text so that it
can be manipulated that way? Super-unprotect the unprotected sections of the
word doc? Set the entire thing up a different way? Please & thank you thank
you thank you.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?TGF1cmE=?=,

The problem occurs when the Word document is LOCKED. Parts of my document
are form-protected for other users, but since it is meant to be a sort of
template, they need to be able to reference a different spreadsheet file for
the excel charts. I have tried to retain the Edit/Links & ALT+F9
functionality by placing the excel charts in UNPROTECTED SECTIONS of the word
document, but the Edit/Links ability disappears completely when the LOCK is
flipped on.

Right. Sorry for going 'round the ring again; got a bit distracted...

Try it with this macro, assigned to a toolbar button/and or keyboard shortcut

Sub ToggleFieldCodesInForm()
ActiveDocument.ActiveWindow.View.ShowFieldCodes = _
Not ActiveDocument.ActiveWindow.View.ShowFieldCodes
End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Laura]

That's absolutely fantastic, thank you. This may be shooting the moon, but
is it possible to write the code to open the Edit/Links dialogue box that
disappears when it's locked?

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?TGF1cmE=?=,

This may be shooting the moon, but
is it possible to write the code to open the Edit/Links dialogue box that
disappears when it's locked?

Only by unprotecting the entire document. The dialog box is locked out
completely as long as the document is protected. The macro could do that;
question is, do you want to take the chance that the macro might fail and
leave the document unlocked?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Laura]

I'm ok with that. If it does happen, it can be locked again by clicking on
the padlock (Forms toolbar), and just won't be password protected, right?
I'd just love for it to be as user friendly as possible, and the dialogue box
would help immensely. (I would just need to know -- would it clear all the
fields as it does when you unprotect the doc now? Best if not, but I'd take
it regardless.)

Thanks for all your help on this!

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?TGF1cmE=?=,

I'm ok with that. If it does happen, it can be locked again by clicking on
the padlock (Forms toolbar), and just won't be password protected, right?
I'd just love for it to be as user friendly as possible, and the dialogue box
would help immensely. (I would just need to know -- would it clear all the
fields as it does when you unprotect the doc now? Best if not, but I'd take
it regardless.)

OK, here you go. And when it reprotects it does retain the data in fields:

Sub ShowEditLinks()
Dim doc As Word.Document

Set doc = ActiveDocument
If doc.ProtectionType <> wdNoProtection Then
doc.Unprotect
End If
Dialogs(wdDialogEditLinks).Show
doc.Protect wdAllowOnlyFormFields, True
End Sub


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Laura]

THANK YOU!!!

Hi =?Utf-8?B?TGF1cmE=?=,

OK, here you go. And when it reprotects it does retain the data in fields:

Sub ShowEditLinks()
Dim doc As Word.Document

Set doc = ActiveDocument
If doc.ProtectionType <> wdNoProtection Then
doc.Unprotect
End If
Dialogs(wdDialogEditLinks).Show
doc.Protect wdAllowOnlyFormFields, True
End Sub


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)


This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail