Solved

VBA Excel - Assign saved filename and path to variable

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

15 Experts available now in Live!

Get 1:1 Help Now