Author: Oscar Cronquist Article last updated on September 05, 2019

This article 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. Click on InsertFilesInFolder to select it.
  3. Click "Run" button to execute 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 click 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. Click on "Insert" on the menu.
  5. Click 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.

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!