asked on

VBA Excel - Assign saved filename and path to variable

Dear Experts,

I have a small macro by recorder below, it just save the actual excel file to directory D:\ASN \Test.xls

ChDir "D:\ASN"
ActiveWorkbook.SaveAs Filename:="D:\ASN\Test.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Could you please advise how to assign this saved filename to a variable, which after would be copied by the macro to Sheet2 as clickable hyperlink to cell A1?

thanks,

Michael Fowler

csehz

Here you go

Sub fileName()
   
   Sheets(2).Hyperlinks.Add Anchor:=Sheets(2).Range("A1"), Address:=ThisWorkbook.Path, _
        TextToDisplay:=ThisWorkbook.Path

End Sub

Open in new window

csehz

ASKER

Maybe a small addition about the target - when I am working on a file and finally finished, I would like to have a small macro which would know the path of the saved file and would be able to copy to a beside sheet as hyperlink.

thanks,

csehz

ASKER

Michael thanks, trying your macro it copied to A1 the D:\ASN, could you advise how to modifiy that not only the directory, but also the file name would be there? So D:\ASN\Test.xls

thanks,

ASKER CERTIFIED SOLUTION

Michael Fowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Never mind it was very good speed, thanks it works of course