Author: Oscar Cronquist Article last updated on March 16, 2022

1. List files in a folder and create hyperlinks (VBA)

This section demonstrates a macro that populates a new worksheet with filenames from the active folder which is the same folder as the workbook was saved in. The filenames are hyperlinks that make it easy for you to quickly open any file.

What you will learn in this article

  • How to not show changes made from macro, until it is finished processing, in order to speed up the macro.
  • Save a worksheet object to a variable.
  • Create a new worksheet and save it to an object.
  • Iterate through files in a given folder programmatically.
  • Ignore the current folder and parent folder.
  • Write hyperlinks to a worksheet programmatically.
  • Move from one cell to another using VBA.
  • Identify folders using DIR function.

How the macro works

The animated picture below shows how the macro works.

  1. Press Alt + F8 to open the macro dialog box.
  2. Press with mouse on InsertFilesInFolder to select it.
  3. Press with left mouse button on "Run" button to run the selected macro.
  4. A new worksheet is automatically inserted to your workbook.
  5. The macro populates column A with file names from the same directory that the workbook was saved in.
  6. The filenames are hyperlinks that you can press with left mouse button on to open.

VBA Code

'Name macro
Sub InsertFilesInFolder()

'Don't show changes on worksheet to user, this will speed up the macro a lot
Application.ScreenUpdating = False
'Dimension variables and declare data types
Dim sPath As String, Value As String
Dim WS As Worksheet

'Add a new worksheet and save it to variable WS
Set WS = Sheets.Add

'Save workbook path to variable sPath and append \
sPath = ActiveWorkbook.Path & "\"

'Get filename from files in folder sPath
Value = Dir(sPath, &H1F)

'Save text Filename to cell A1
WS.Range("A1") = "Filename"

'Save cell A2 to variable StartCell
Set StartCell = WS.Range("A2")

'Loop through filenames until value is equal to nothing
Do Until Value = ""

'Ignore . (represents the folder you are in) and .. which is the parent folder
If Value = "." Or Value = ".." Then

'Continue with the following lines if value is not equal to . or ..
Else

    'Check if value is a folder name
    If GetAttr(sPath & Value) = 16 Then

    'Continue with values that are not folder names or . or ..
    Else

        'Check if value is equal to your workbook name or a temporary file that is created when the workbook is opened
        If Value <> ActiveWorkbook.Name And Value <> "~$" & ActiveWorkbook.Name Then

            'Create hyperlink
            StartCell.Hyperlinks.Add Anchor:=StartCell, Address:= _
            Value, TextToDisplay:=Value

            'Move to cell below
            Set StartCell = StartCell.Offset(1, 0)
        End If
    End If
  End If

'Move to next value in folder 
Value = Dir

'Continue macro with Do
Loop

'Show changes on worksheet to user
Application.ScreenUpdating = False
End Sub

Where to put the code?

  1. Copy above VBA code
  2. Press Alt+F11 to open the VB Editor.
  3. Select your workbook in the Project Explorer
  4. Press with mouse on "Insert" on the menu.
  5. Press with mouse on "Module" to create a module in your workbook.
  6. Paste VBA code to code module.
  7. Return to Excel.

Note, make sure you save your workbook with the *.xlsm file extension. This will save the VBA code to the workbook.

Get the Excel file


Insert-files-in-current-folder.xlsm

Back to top

2. How to navigate quickly in a complex workbook using hyperlinks

Question: I have a workbook containing a lot of worksheets and data. I need a way to find certain data/sheets quickly. How can I do this?

Answer: Create a table of contents and use hyperlinks to move around in a large complex workbook.

Here is how to do this in Excel:

  1. On your front sheet create a list with important workbook sheets you often visit
    table-of-contents.png
  2. Press with right mouse button on on January and select Hyperlink...
  3. Press with left mouse button on "Link to: A place in this document"
    table-of-contents_1.png
  4. Type cell reference. You can link to the same sheet as many times as you want. But by using a cell reference you can link to a certain place in that sheet, creating a more sophisticated navigation system.
  5. Press with left mouse button on OK!
    table-of-contents_2.png
  6. Try press with left mouse button oning on January. Now can find important places in a workbook really quick!
  7. Repeat with remaining sheets.

Get Excel sample file for this tutorial.
table-of-contents.xls (Excel 97-2003 Workbook *.xls)

Back to top