Return to VBA Code Examples

VBA – Create a Hyperlink Menu of Files in a Folder

This article will demonstrate how to Create a Hyperlink Menu of Files in a Folder.

This macro can be a great time saver if you have a lot of files in a particular folder that you access regularly.  You can open your Excel file, and click on the hyperlink to open any of the individual files in the folder.

VBA hyperlink intro

 

Create a Hyperlink Menu

1. Firstly, type the name of the folder to access in a location in your worksheet.

VBA hyperlink locations

2. Then, type the following macro into a module in the worksheet in the VBE Editor.

Sub HyperlinkMenuAllFiles()
  Dim fileName As Variant
  Dim strLocation As String
  Dim x As Integer

  strLocation = Range("C2")
  fileName = Dir(strLocation)
  x = 4

While fileName <> ""
   Sheets("Sheet1").Cells(x, 3).Select
   ActiveSheet.Hyperlinks.Add _
   Anchor:=Selection, Address:=strLocation & fileName, SubAddress:= _
   "", TextToDisplay:=fileName
   x = x + 1
   fileName = Dir
Wend

End Sub

This code will loop through all the files in a folder, creating a hyperlink to each file.

3. Now, return to the Excel window, and, in the Ribbon, select Developer > Code > Macros and then select the macro and click Run.

VBA hyperlink run

4. A quicker way to run the macro would be to create a button for the macro in your worksheet.

In the Ribbon, select Developer > Insert > Form Controls > Button.

VBA Buttons FormControl

Click and drag in the worksheet to create a button.  As soon as you release the mouse button, the assign macro dialog box will appear.

VBA hyperlink assign macro

(1) Select the macro you wish to assign to the button, and then, 2) click OK.

VBA hyperlink select macro

Type an appropriate name for the button and then click off the button.

Click the button to run the macro.

VBA hyperlink show button

vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples