Solved: Links to Embedded Spreadsheet

Not a VBA issue at this point but may evolve into one.

In an effort to automate some repetitive tasks I have embedded an Excel 2000 spreadsheet in a Word 2000(SP 3)document. This spreadsheet provides an overview or summary of the report. The Excel spreadsheet has no existence outside of within the Word document - it is embedded and Word assigns the 'filename'. Therefore to use the 'template' (it is a .doc, not a .dot), one just opens the Word file, does a file-Save As, fills in the Excel Sheet, and updates the fields (F9). There is only one file - no Excel file exists as a discrete entity.

The cells in the spreadsheet are named. Each cell is linked to the appropriate location in the Word document. The procedure (and I don't mean to sound patronizing) is to double click the embedded Excel sheet to open, click on the named cell you want, Edit-Copy, close Excel sheet and go to appropriate location in Word document, Edit-Paste Special-Paste Link - unformatted text. I manually add the Charformat switch.
A sample link is:

{Link.Excel.Sheet.8 "\\\server2\\jobs\\Miles Plaza\\Miles Plaza.doc" _1135171229!Sheet1!Address \a \t \* CHARFORMAT { }}

This inserts the appropriate street address in the appropriate locations in the document.

However when the location of the file is moved, for instance when I send it to my home office, or the file is renamed (File-Save As) Word is inconsistent in updating/renaming the filename and/or path, resulting in an "Error! Not a valid link" message for some or all of the fields.

There is only one document (the Word file) - the links point to an Excel file that is internal to the Word document. In other words, the links should always reference the document in which they are contained.

Lets say the Word document is named Template.doc. The links will be:

{Link.Excel.Sheet.8 "\\\..path..\\Template.doc" _1135171229!Sheet1!CELLNAME \a \t \* CHARFORMAT { }}

with CELLNAME being the name of the cell or range containing the data for any given link.

If I save this document as Job1.doc, the internal links should change to read:

{Link.Excel.Sheet.8 "\\\..new path..\\Job1.doc" _1135171229!Sheet1!CELLNAME \a \t \* CHARFORMAT { }}

Most of the time when the document is saved with a different name (and/or to a different location), upon updating links the path and/or document name in the link will change as required. However this is not consistent. In fact, I am currently working with one document in which the first link will change but the remainder will not. The codes for the links are identical with the exception of the CELLNAME (which I've checked and they do exist) and the numerical sequence following the filename.

There are no articles I can find in the MS Knowledge Base addressing this function. The fact that this behavior is not consistent (as it will update most but not all of the time) is preventing me from using it as a template for the rest of the office.
A sample is attached. Any insights would be appreciated.

Hi, David! Sorry. I just noticed you posted!

Is this still an issue? Or are you going to pursue the other avenues we discussed?

And now that I see it here (I hate that other way we talked, you know?), I'm wondering if you don't just change the link from:

{Link.Excel.Sheet.8 "\\\..new path..\\Job1.doc" _1135171229!Sheet1!CELLNAME \a \t \* CHARFORMAT { }}


to

{Excel.Sheet.8!Sheet1!CELLNAME \a \t \* CHARFORMAT { }}

?
Had you ever tried that? I mean...it's linking to another file I think 'cause you're saving the file to a new name. So if you just edit the links to make them look like the ones that are embedded... no?

Or instead of doing Save As, just copy the file FIRST, and then work on it for the *new* version.

Otherwise, of course, if anyone has any insight...they should post here.

I tried various permutations of that scheme, including inserting the document field Filename in the link. Got "Not a Valid Link" Removing the filename altogether as in you example results in "No topic specified!" - which did not turn up any results in MS KB.

I'm playing around with a VB approach but have decided to go listen to Zydeco music and lose some money at Churchill Downs today. Maybe it'll rain tomorrow and I'll get in a full day!

Thanks

As are many things in life, the answer was relatively simple. Simply replace the path and filename in the quotes with the builtin Word field {FILENAME \p}

Link.Excel.Sheet.8 "\\\server2\\jobs\\DRHogan\\Sherwood Plaza.doc" _1135171229!Sheet1!CELLNAME \a \t \* CHARFORMAT { }}

becomes either

Link.Excel.Sheet.8 {FILENAME \p} _1135171229!Sheet1!CELLNAME \a \t \* CHARFORMAT { }}
or
Link.Excel.Sheet.8 "{FILENAME \p}" _1135171229!Sheet1!CELLNAME \a \t \* CHARFORMAT { }}

This supplies the path and current document name within the linking code. If you leave the quotes around the field, after updating, the path and filename is shown in the code. Without the quotes, the {FIELDNAME /p} is shown. I would recommend that you leave out the quotes, as someone else looking at the link code would not be aware that there is a field embedded there. However the functionality is the same either way.

So, this is all solved then, Dave? That's terrific!
I'm marking it that way.