Solved

VBA Excel - Assign saved filename and path to variable

Posted on 2010-11-26
5
764 Views
Last Modified: 2012-06-21
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,
0
Comment
Question by:csehz
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 34216819
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

0
 
LVL 1

Author Comment

by:csehz
ID: 34216828
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,
 
0
 
LVL 1

Author Comment

by:csehz
ID: 34216846
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,
0
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
ID: 34216922
Sorry about that it is getting late here. This will work for you
Sub fileName()
   Sheets(2).Hyperlinks.Add Anchor:=Sheets(2).Range("A1"), Address:=ThisWorkbook.FullName, _
        TextToDisplay:=ThisWorkbook.FullName
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:csehz
ID: 34216954
Never mind it was very good speed, thanks it works of course
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question