Linking Excel - Dynamic FileName

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Garth Wells - 19 Jun 2005 00:45 GMT

My client would like to create a Word and Excel template that are linked.
The Word doc will get a few calculations from Excel. The big challenge is
they want to be able to save versions of each Word/Excel combo that
are dynamically linked.

For example, assume you start off with Temp1.doc & Temp1.xls. The first
thing you would do is copy each file to a new subdirectory and rename to
something like: CusPro1.doc & CusPro1.xls. What I would like to do is
make the reference to the linked Excel dynamic...a function of the .doc's
name. So, when the Word file is opened it will replace all linked references
to

<MyFileName> & ".xls"

and open and update the linked fields accordingly.

Is this possible? If yes, could you give some general direction?

Thanks

Garth

Reply to this Message

Jezebel - 19 Jun 2005 01:06 GMT

On approach is to use nested fields. Within each of the fields that
instantiate the link, insert a DocProperty field in place of the name of the
linked file. --

{ LINK {DocProperty XLFile } ... }

Then you can reset the links by changing the value of the XLFile DocProperty
and updating fields.

> My client would like to create a Word and Excel template that are linked.
> The Word doc will get a few calculations from Excel. The big challenge is
[quoted text clipped - 18 lines]
>
> Garth

Reply to this Message

Garth Wells - 20 Jun 2005 06:05 GMT

> My client would like to create a Word and Excel template that are linked.
> The Word doc will get a few calculations from Excel. The big challenge is
[quoted text clipped - 17 lines]
>
> Garth

The following seems to work OK. Create a Bookmark in the .doc and define a
Name in the .xls. The files must have the same base name (e.g., File1.doc & File1.xls)

Sub UpdateFromExcel()
'
' UpdateFromExcel Macro
' Macro created 6/19/2005 by GW
'
' Get base file name
Dim FileName As String
FileName = FileName & Left((ActiveDocument.FullName), (Len(ActiveDocument.FullName) - 4))
FileName = FileName & ".xls"
' MsgBox (FileName)

' Create Excel object, open handle to spreadsheet
Dim myWB As Excel.Workbook
Set myWB = GetObject(FileName)

' Replace bookmarks w/ values...this method does not remove bookmark
If ActiveDocument.Bookmarks.Exists("DocumentTitle") Then
Set BMRange = ActiveDocument.Bookmarks("DocumentTitle").Range
BMRange.Text = (myWB.Sheets("Distribution Questions").Range("DocumentTitle"))
ActiveDocument.Bookmarks.Add "Title", BMRange
End If

' Clear reference to bookmark
Set myWB = Nothing
End Sub

Reply to this Message