Solved

VBA Excel - Assign saved filename and path to variable

Posted on 2010-11-26
5
754 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delete all words in cell more than 5 words 7 48
Excel:  Find text between 2nd hyphen and next space 10 31
increment numbers by 10 11 31
Excel range I cannot find 8 26
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now