Need Help with a Hyperlink Excel Macro

**pac1990** · 07-17-2012, 02:01 PM

I have a worksheet with about 20,000 rows. One of the columns is a file name i.e. 00001ab.txt, what i have been doing is clicking the cell, clicking hyperlink, copying the "Text to Display" into the "address" and hitting ok. This is incredible time consuming and would take me weeks to do one by one. Can anyone think of a way to automate this process so it automatically opens up hyperlink copies the "Text to Display" into the "Address" and then hitting ok?

Thanks in Advance

**tlafferty** · 07-17-2012, 02:55 PM

Simply click in the column where you want to enter a hyperlink, and enter it as a formula:
=HYPERLINK(A2,A2)

The first part of the formula is the location of the link, and the second part is the text to display. Double click the autofill handle in the lower right hand corner of the cell to fill down.

**pac1990** · 07-17-2012, 03:53 PM

Thank You That Worked Great.

One more quick question is there a way I can then copy those hyperlinks and have them appear as just the file name and not the formula. So if someone clicks on the cell they would just see "Title" not Hyperlink(A2,A2)?

**tlafferty** · 07-17-2012, 05:07 PM

Hmmm.....you could always hide the formula bar. Go to the view tab and remove the check from formula bar. Another option would be to put in a reference to the cell that contains the hyperlink. IE: =A2. The problem with this is that removes the hyperlink in the cell containing the formula =A2

**pac1990** · 07-18-2012, 09:55 AM

how would I go about linking to a file in a different folder. This works great for the files in the same folder as the Excel Sheet however I have a handful in other folders.

**tlafferty** · 07-20-2012, 01:09 PM

Use Windows explorer to browse to the file location, then select it's path from the address bar. Paste that into your hyperlink function, and be sure to include your filename and extension like this:
=HYPERLINK("C:\Users\Yourname\Desktop\Excelfilename.xlsx","friendly name"). Alternatively, put the path into a cell in the workbook, and point the hyperlink formula to it as described above.

**tlafferty** · 07-20-2012, 01:14 PM

BTW, if you'd like a utility which will return all filenames and their paths for a given folder, take a look at PowerShell. It's a Microsoft product kind of like a command line prompt on steroids. Using Powershell, and a single line of commands, you can send all spreadsheet names to Excel like this:
Get-ChildItem c:\scripts\*.* -include *.xlsx|export-csv "c:\users\yourusername\desktop\filenames.csv"

change the -include filter to something other than *.xlsx to look at other filetypes...