Relative file paths

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have an excel spreadsheet that contains calculations and a Word document
that has a number of link references to it within the text of a report.
Everything works fine and the excel sheets show up in the report as another
page with headers and footers and correct page numbers etc.

The problem occurs when I want to copy the two files to another project
subdirectory and then try to make chnges to both. All the links in the Word
document still refer back to the original excel spreadsheet back in the
first project subdirectory. I have to manually repoint all the links to the
new project subdirectory. Is there any way to cause the link to be to a
relative address, that is make it look for the excel file in the same
directory that contains the Word file? I can only figure out how to tell it
absolute addresses of links

I will also post this in the Word group..
 
Jim,

One solution is to embed the Excel workbook in the Word document. Now to
update it, just double-click it, change data as necessary in the worksheet,
then click outside of it to be back in Word. You have only one file to cart
around now, and no links to worry about. Updating the worksheet requires
the user has Excel installed, but viewing it inside the Word document does
not.

To embed it, select the range to be displayed (changeable later), and Copy.
Go to Word, Edit - Paste special - Excel object. Don't use Paste Link.
Done. OLE!

Pun intended.
 
I can't do that because the spreadsheet contains from about 12 to 75 sheets
depending on the project size. Most sheets read project information within
the first few sheets and use that in their calculations. I need to make
changes throughout the spreadsheet to fit the individual project.

I then use the individual sheets as pages within the Word document.with
report text (which varies from project to project) interspersed between the
pages. This allows me to produce a finished report with Table of Contents,
correct page numbering and referencing and the other polishes of Word.
 
Jim,

You may still want to use an embedded workbook. When you embed any part of
any sheet, it actually embeds the entire workbook in the Word document.
From now on, the workbook lives in the Word document, and you can then
access any part of it by double-clicking it into edit mode (you're in Excel
now). You can copy stuff from any sheet, and paste-link it to other parts
of the Word document. Note that if you copy the embedded object to another
part of the Word document, you'll create another independent copy, which you
don't want.
 
Well I tried that approach today but it embeds a new copy of the workbook at
each embed point. Not only does the Word file get Huge but the workbooks
are different so they don't produce the same correct answer that one
workbook does.

I guess I will have to try automating the link repoiinting procedure. I
used the pair of files again today to prepare a report for a project and it
was a pain to go through all the links. Fortunately it was a small project
with few (16) pages.
 
Jim,

Tolja. Every time you embed, or even copy an embedded object, you get
another copy. File gets huge. Disk manufacturers profits soar. Keep one
workbook embedded, and edit it as necessary -- any part of any sheet -- you
have access to the entire embedded workbook. Copy and paste-link stuff from
anywhere in that embedded workbook to anywhere in the Word document as
needed. It will update as the workbook is updated.
 
Earl Kiosterud wrote...
. . . Every time you embed, or even copy an embedded object, you get
another copy. File gets huge. Disk manufacturers profits soar. Keep one
workbook embedded, and edit it as necessary -- any part of any sheet -- you
have access to the entire embedded workbook. Copy and paste-link stuff from
anywhere in that embedded workbook to anywhere in the Word document as
Click to expand...
needed. It will update as the workbook is updated.
Click to expand...
....

Maybe your experience with this has been MUCH better than mine. If I
try to create multiple links from an embedded Excel document into a
Word document, whenever I try to edit any *subsequent* link, Word very
unhelpfully changes the first link.

When I right-click on the first link in Word, the second section of the
pop-up context menu shows 2 items: a 'Worksheet Object' submenu and
'Show Picture Toolbar'. When I right-click on any subsequent link in
Word, the context menu shows 3 items, the previous 2 as the last two
plus an 'Update Link' entry above them. Also, the second and subsequent
links' 'Worksheet Object' submenus show an extra entry at the bottom
compared to the first link, and that additional entry is labelled
'Links...'.

It'd seem the robust way to do this would be to crop the image of the
1st link so that it's effectively invisible, and put it in a margin so
it's out of the way. Then use the 2nd and subsequent actual links into
the embedded Excel workbook as the 1st and subsequent logical links
into it.
 
Earl,

Thanks for the tip.

It works as expected. I was considering triing this approach tonight so
after reading your post I tried it ealy it worked. This is what I was
looking for.

Jim Magee
 
Back
Top