Hyperlink to files?

razzandy

Active Member
Joined
Jun 26, 2002
Messages
390
Office Version
  1. 2007
Platform
  1. Windows
I have the below code to list the files in a directory. What I want the code to do is add hyperlinks to the files listed, so I can click the link and open the file direct.

Sub ListFiles()
Columns(1).Clear
Dim myRow As Integer
Dim myFile As String

myRow = 1
myFile = Dir(InputBox("Enter Directory"))
Do Until myFile = ""
Cells(myRow, 1) = myFile

myRow = myRow + 1
myFile = Dir
Loop
End Sub

Any ideas will be much appreciated. :)

Cheers

Ryan UK :wink:
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Turn on the macro recorder, create a hyperlink to a file, and turn off the recorder. Then, adapt the code you get and include it within the loop.
 
Upvote 0
Hi,

Try the code below. There are optional additional parameters in the hyperlinks.add method for tooltip text and subaddress. (Code mightn't be optimised - range always confuses me.)

Hope this helps,

Dave.

Sub ListFiles()
Columns(1).Clear
Dim myRow As Integer
Dim myFile As String
Dim myhyp As Hyperlink
Dim strOrigPath As String
Dim myrange As Range



myRow = 1

strOrigPath = "C:/tools/"
myFile = Dir(strOrigPath)

Do Until myFile = ""
Cells(myRow, 1) = myFile
Set myrange = Cells(myRow, 1)

sheet1.Hyperlinks.Add myrange, strOrigPath & myFile


myRow = myRow + 1
myFile = Dir
Loop
End Sub
 
Upvote 0
Thanks Tusher I had a go with the recorder b4 your posting I was really struggling to make it work!:(

A big thanks goes to Dave :wink:

I've tailored the code slightly and this is what I've come up with:


Sub ListFiles()
Columns(1).Clear
Dim myRow As Integer
Dim myFile As String
Dim myhyp As Hyperlink
Dim strOrigPath As String
Dim myrange As Range

myRow = 1

strOrigPath = InputBox("Enter Directory")
myFile = Dir(strOrigPath)

Do Until myFile = ""
Cells(myRow, 1) = myFile
Set myrange = Cells(myRow, 1)

Sheet1.Hyperlinks.Add myrange, strOrigPath & myFile


myRow = myRow + 1
myFile = Dir
Loop
End Sub

Many Thanks Mate

Ryan UK :)
 
Upvote 0
Dear Dave or anybody

The code in my last posting works fine until a use the wild card *.xls, it produces the list without any problems but the hyperlink for all files listed is linked to the file *.xls, so I get an error when clicking on the link. Is there any way to get around this problem?

Thanks in advance

Ryan UK :)
 
Upvote 0

Forum statistics

Threads
1,218,620
Messages
6,143,523
Members
450,492
Latest member
Rusbus1972

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top