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.
Create a Hyperlink Menu
1. Firstly, type the name of the folder to access in a location in your worksheet.
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.
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.
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.
(1) Select the macro you wish to assign to the button, and then, 2) click OK.
Type an appropriate name for the button and then click off the button.
Click the button to run the macro.