select a file name for a link?

[Larry M in NE]

I want to include a formula with a link name in a file template that will be
used by others. The file name to be in the file name must vary each time a
new file is created and named from the template. Each file includes the
person's name and a month year indication i.e. larry082005.xls each file
must link the prior month. Suggestions?

 

[Dave Peterson]

Create a dummy file in the same location.

Use that dummy file as the link.

Tell the users to edit|link|change source
(or edit|replace to modify the link)

after they've opened the workbook.

 

[Larry M in NE]

Thanks. I left out an important piece of information. Although the cells
that need the varibale file name are not locked, the sheet is protected. The
link update is not allowed in the protected sheet.

 

[Dave Peterson]

Give them a macro that
prompts for the new filename
validates that the file actually exists
unprotects the worksheet
does the change
reprotects the worksheet

Kind of:

Option Explicit
Sub testme()
Dim myFileName As Variant
Dim testStr As String

myFileName = Application.GetOpenFilename(filefilter:="Excel files, *.xls")

If myFileName = False Then
Exit Sub
End If

With Worksheets("sheet1")
.Unprotect Password:="hi"
.Cells.Replace what:="oldlinkname", replacement:=myFileName
.Protect Password:="hi"
End With
End Sub

(replace OldLinkName with the dummy workbook name--full path, too!)