Using the Immediate Window to Get a List of File Names

Here’s a trick to get a quick list of files from a certain directory.

Open the Excel or Access Visual Basic Editor and then press Ctrl+G to see the Immediate Window.

Enter the following syntax and then press Enter. Be sure to change the directory (in red) to the path of your directory.

File = Dir(“C:\MyDirectory\“): Do Until File = “”: Debug.Print File: File = DIR(): Loop

.

Your reward will be a list of all the files in that directory. You can now copy this list and use it as you see fit.


.

I’ll be honest. There is no real reason to do this in the Immediate Window. After all, you can write a permanent macro to list all the files in a directory.

But if you need a quick one-off list of files, this is a nifty way to get it.

12 thoughts on “Using the Immediate Window to Get a List of File Names

  1. Doug Glancy

    I write loops in the immediate window every once in a while – always have to remember that “:” is the delimiter.

    One of my favorite immediate window tricks was pointed out on DDOE years ago, I think by Stephen Bullen. It’s entering:

    Thisworkbook.Close False

    Just make sure you know which workbook is active in the VBE and that you’ve saved what you want!

  2. ruve1k

    This can also be done quite easily with Power Query. Power Query > From File > From Folder > Browse… to a folder path. Right-click on the Name column > Remove Other Columns. From the Home tab click Close & Load.

  3. milang

    Another way is to select all files in folder, then Shift + RightClick > Copy as Path and Paste it somewhere.

  4. AlexJ

    @milang: Thanks – didn’t know that with either individual or multiple files. Now they’ll think I’m smart again!

  5. MF

    I used to use the trick of using =FILES(“Directory”) in Name Manager, followed by using INDEX.

    But starting from today, I believe I would go for milang’s trick.

    Cheers, 🙂

  6. Jason Morin

    I still use Rob van Gelder’s super fast code once in a while:

    Sub Pull_File_Names()
    DumpDirectories “C:\Users\”, ActiveSheet.Cells(1)
    End Sub

    Sub DumpDirectories(Directory As String, rng As Range)
    Dim objFS As Object, objFolder As Object, obj As Object

    Set objFS = CreateObject(“Scripting.FileSystemObject”)
    Set objFolder = objFS.GetFolder(Directory)

    For Each obj In objFolder.SubFolders
    DumpDirectories obj.path, rng
    Next

    rng.Value = Directory
    For Each obj In objFolder.Files
    rng.Offset(, 1).Value = obj.Name
    Set rng = rng.Offset(1)
    Next
    End Sub

  7. Jim Cone

    And if you are using the Firefox browser…

    Paste the file path into the browser address bar
    Edit | Select all
    Edit | Copy
    Paste somewhere

  8. Kuzu

    Greetings from 2017,

    I just tried the syntax in immediate window and got the following error;

    “Compile error:
    Expected: list separator or )”

    Anyways, I just love the shift + right-click method.

Leave a Reply

Your email address will not be published. Required fields are marked *