When you create a file name under "Save As", is it possible (through a macro)
at the same time to automatically create a hyperlink to that "file name and
path" in a specific cell?

Yes, but...
I think you'd be better off doing it in the Workbook's _BeforeClose() event
rather than the _BeforeSave() event. BeforeSave doesn't see the changed path
if you decide to store it in a folder other than the current folder.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Worksheets("Sheet1").Range("A1").Formula = _
"=HYPERLINK(" & Chr$(34) & ThisWorkbook.FullName & Chr$(34) _
& "," & Chr$(34) & "Link to this file" & Chr$(34) & ")"
On Error GoTo 0
End Sub

Change the sheet name and cell address as required, and you can change the
text displayed from "Link to this file" to anything you want, including the
..FullName of the workbook again.

To put the code in the proper place (Excel 2003 & earlier): right-click on
the Excel icon immediately to the left of the word "File" in the menu toolbar
and choose [View Code] from the popup list. Copy and paste the code into the
code module presented to you.

If you want to put that into some other workbook, as in building a 'table of
contents' workbook, there's more work to be done. I'm not certain what the
practical advantage of putting it in the same workbook is: if you can see it,
you have the book open and you already know where it came from? Also, you
can't click and use it because you'll get a "file is already open" alert.


"I think I need to rephrase the question" wrote:

When you create a file name under "Save As", is it possible (through a macro)
at the same time to automatically create a hyperlink to that "file name and
path" in a specific cell?

If you just want a hyperlink that points to a specific location in the same
workbook, then you can use the =hyperlink() function. It won't depend on the
location of that workbook.

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheeton e!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

I think I need to rephrase the question wrote:

When you create a file name under "Save As", is it possible (through a macro)
at the same time to automatically create a hyperlink to that "file name and
path" in a specific cell?

--

Dave Peterson

Thanks for the information. It works perfectly.

"JLatham" wrote:

Yes, but...
I think you'd be better off doing it in the Workbook's _BeforeClose() event
rather than the _BeforeSave() event. BeforeSave doesn't see the changed path
if you decide to store it in a folder other than the current folder.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Worksheets("Sheet1").Range("A1").Formula = _
"=HYPERLINK(" & Chr$(34) & ThisWorkbook.FullName & Chr$(34) _
& "," & Chr$(34) & "Link to this file" & Chr$(34) & ")"
On Error GoTo 0
End Sub

Change the sheet name and cell address as required, and you can change the
text displayed from "Link to this file" to anything you want, including the
.FullName of the workbook again.

To put the code in the proper place (Excel 2003 & earlier): right-click on
the Excel icon immediately to the left of the word "File" in the menu toolbar
and choose [View Code] from the popup list. Copy and paste the code into the
code module presented to you.

If you want to put that into some other workbook, as in building a 'table of
contents' workbook, there's more work to be done. I'm not certain what the
practical advantage of putting it in the same workbook is: if you can see it,
you have the book open and you already know where it came from? Also, you
can't click and use it because you'll get a "file is already open" alert.


"I think I need to rephrase the question" wrote:

When you create a file name under "Save As", is it possible (through a macro)
at the same time to automatically create a hyperlink to that "file name and
path" in a specific cell?

Thanks for the information.

"Dave Peterson" wrote:

If you just want a hyperlink that points to a specific location in the same
workbook, then you can use the =hyperlink() function. It won't depend on the
location of that workbook.

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheeton e!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

I think I need to rephrase the question wrote:

When you create a file name under "Save As", is it possible (through a macro)
at the same time to automatically create a hyperlink to that "file name and
path" in a specific cell?

--

Dave Peterson