Wauvel

Excel like a finance pro.

← All VBA snippets

Let someone choose the file or folder

A hard-coded path is the first thing that breaks on anybody else's machine.

1What it does

Every macro that reads a file starts life with a path typed into the code, and that path is right on exactly one computer. `Application.FileDialog` opens the normal Windows picker, hands back what the user chose, and tells you whether they cancelled — which is the part that gets skipped, producing an error 5 on the next line instead of a quiet exit. Use `msoFileDialogFolderPicker` for a folder and `msoFileDialogFilePicker` for a file; both return -1 when something was chosen and 0 when it wasn't.

2The code

Sub ImportFromChosenFolder()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.Title = "Pick the folder with this month's exports"

    ' .Show returns -1 if they chose something, 0 if they cancelled.
    If fd.Show <> -1 Then Exit Sub

    Dim folderPath As String
    folderPath = fd.SelectedItems(1)

    ' Dir needs the trailing separator; the picker doesn't give you one.
    If Right$(folderPath, 1) <> Application.PathSeparator Then
        folderPath = folderPath & Application.PathSeparator
    End If

    Dim fileName As String
    fileName = Dir(folderPath & "*.csv")

    Do While fileName <> ""
        Debug.Print "would import: " & folderPath & fileName
        fileName = Dir          ' bare Dir returns the NEXT match
    Loop
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Point a combine-files macro at whichever folder this month's exports landed in.
  • Let a colleague run your macro on their own machine without editing the code.
  • Ask for a save location instead of assuming one.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

folderPath = "C:\\Users\\blake\\Exports\\"

fd.Show returns −1 when they picked something and 0 when they cancelled — testing that is what stops the next line erroring.

5Pitfalls

Cancel isn't handled, so the code carries on and errors on `SelectedItems(1)` with 'subscript out of range'.

Fix: Always test `If fd.Show <> -1 Then Exit Sub` before touching SelectedItems.

The path comes back without a trailing backslash, so `folderPath & "*.csv"` becomes nonsense.

Fix: Append `Application.PathSeparator` when it isn't already there, as above — and use that rather than a typed backslash.

`Dir` is reset by any other Dir call inside the loop, so the loop never ends or skips files.

Fix: Don't call Dir for anything else mid-loop. Collect the names first, then process them.

`AllowMultiSelect` is left on and only the first item gets used.

Fix: Set `fd.AllowMultiSelect = False` explicitly, or loop `For Each item In fd.SelectedItems`.

6No-code alternatives

  • GetOpenFilename — A one-liner for a single file: `Application.GetOpenFilename("CSV files,*.csv")`. Returns False on cancel.
  • A path in a settings cell — Better when the folder genuinely never changes — visible and editable without opening the editor.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.