To open the current Windows user’s Documents folder from Excel VBA, get its location with the Windows Script Host’s SpecialFolders("MyDocuments") property, then pass that path to File Explorer. The macro below does this without assuming the folder is literally at C:\Users\Name\Documents, which matters when Windows redirects or relocates it.
Open Documents from a VBA macro
In Excel for Microsoft 365 on Windows, this macro opens the Documents folder in a separate File Explorer window. It checks that Windows returned a path and that the folder can be found before attempting to open it.
Sub OpenDocumentsFolder()
' Open the current user's Documents folder in File Explorer.
Dim shellObject As Object
Dim documentsPath As String
Dim taskID As Variant
Set shellObject = CreateObject("WScript.Shell")
documentsPath = shellObject.SpecialFolders("MyDocuments")
If Len(documentsPath) = 0 Then
MsgBox "Windows did not return a Documents folder path.", vbExclamation
Exit Sub
End If
If Len(Dir$(documentsPath, vbDirectory)) = 0 Then
MsgBox "The Documents folder could not be found: " & documentsPath, vbExclamation
Exit Sub
End If
taskID = Shell("explorer.exe " & Chr$(34) & documentsPath & Chr$(34), vbNormalFocus)
End Sub
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup gives you a way back if you later change the code or the workbook. To add the macro, press Alt+F11 in Excel for Windows, choose Insert > Module in the Visual Basic Editor, and paste the code into the module. Return to Excel and run OpenDocumentsFolder from the editor or from Developer > Macros.
Why use SpecialFolders instead of typing a path?
A common first attempt is to build a path from the Windows profile, such as C:\Users\YourName\Documents. That may work on one computer but is not a dependable way to locate the folder for every user. The profile name varies, and the Documents folder can be moved, redirected by an organization, or synchronized through a service such as OneDrive. A hard-coded path can therefore point to a folder that does not exist or is not the user’s actual Documents location.
WScript.Shell is a Windows component that exposes special-folder locations. In the example, SpecialFolders("MyDocuments") asks Windows for the current user’s Documents folder rather than guessing its location. The macro uses late binding through CreateObject, so it does not require you to select a reference in the Visual Basic Editor. This approach is convenient for a workbook that may be opened on different Windows computers.
The code then places quotation marks around the folder path using Chr$(34). Those quotation marks are important: if a path contains spaces, such as C:\Users\Avery Smith\Documents, Windows needs the path treated as one command-line argument. The Shell function launches File Explorer, and vbNormalFocus requests a normal, visible window.
What happens when you run the macro?
The macro does not import, edit, or move any files. It simply asks Windows to display the folder in File Explorer, where the user can browse files in the usual way. Excel remains open, and the Explorer window can be used independently. The returned value from Shell is stored in taskID because the function returns a task identifier; this example does not need to use that identifier afterward.
The existence check is a useful guard, not a guarantee that every possible access problem has been handled. For example, a folder may exist but still be unavailable because of a network, permission, or synchronization problem. For a simple personal macro, the check and message are often enough to make a missing path clearer than a failed launch. A larger business tool may need additional error handling and a way to report unexpected Windows errors.
Open a different folder or the workbook’s folder
If the goal is to open the folder containing the workbook rather than the user’s Documents folder, use ThisWorkbook.Path. This refers to the folder where the workbook containing the macro is saved. It is not necessarily the same as the Documents folder: a workbook might be saved on a shared drive, in a project folder, or in a synchronized location.
Sub OpenWorkbookFolder()
' Open the saved workbook's containing folder in File Explorer.
Dim workbookFolder As String
Dim taskID As Variant
workbookFolder = ThisWorkbook.Path
If Len(workbookFolder) = 0 Then
MsgBox "Save the workbook before opening its folder.", vbInformation
Exit Sub
End If
taskID = Shell("explorer.exe " & Chr$(34) & workbookFolder & Chr$(34), vbNormalFocus)
End Sub
An unsaved workbook has no containing folder, which is why this version checks whether ThisWorkbook.Path is empty. To open a known subfolder under Documents, first obtain the Documents path as in the main example, then append a backslash and the subfolder name. Check that the combined path exists before launching Explorer. Avoid assuming the subfolder exists just because the parent folder does.
Opening a folder is not the same as choosing a file
Readers sometimes mean something different by “open the Documents folder.” The macro above displays the folder in File Explorer; it does not show a file-selection window inside Excel and does not open a document in the workbook. If the user needs to pick a file for Excel to process, use Excel’s file picker instead. If the user needs to select a folder, Excel’s Application.FileDialog(msoFileDialogFolderPicker) provides a folder-picker dialog.
These choices suit different tasks:
- Show the folder in Explorer: use the
Shellapproach when the user should browse or manage files outside Excel. - Let the user select a file: use a file picker when the macro needs a specific workbook, text file, or other input.
- Let the user select a folder: use a folder-picker dialog when the macro needs a directory path to use later.
A folder-opening macro should not be presented as if it has selected or loaded a file. Showing Explorer leaves the choice and any subsequent file operation to the user. That distinction can prevent confusion in a workbook with buttons such as “Browse,” “Import,” or “Open Documents.”
Common problems and fixes
If nothing appears, check that the code is running in desktop Excel on Windows and that the macro is enabled for this workbook. Also confirm the procedure is in a standard module and that the macro name selected in the Macros dialog is OpenDocumentsFolder. A typo in the procedure name or a compile error elsewhere in the project can stop the macro before Explorer opens.
- The path is wrong: display
documentsPathin a message box while diagnosing the issue. Do not replace the special-folder lookup with a guessed username path unless the workbook is deliberately limited to a known environment. - The path contains spaces: keep the quotation marks around it. Removing them can cause a command to be interpreted incorrectly.
- The folder is redirected or synchronized: use the returned special-folder path. It may differ from the traditional profile-based location.
- The workbook is blocked or macros are disabled: check the file’s origin and your organization’s policy. Do not enable macros from an unknown or untrusted workbook just to make this feature work.
- The folder exists but Explorer does not open as expected: check Windows access to the path and whether it is a local, network, or temporarily unavailable location.
For a workbook you created or trust, macro settings are managed through File > Options > Trust Center > Trust Center Settings > Macro Settings in Excel for Windows. The safest general practice is to allow macros only from sources you trust and to follow your organization’s rules. Avoid changing security settings globally to run a single downloaded workbook.
Saving and sharing the macro
A workbook containing VBA should be saved in a macro-enabled format, typically .xlsm. If you save it as a standard .xlsx file, Excel does not retain VBA code in that file. When sharing the workbook, explain what the macro does: this example opens a folder and does not need to read or change its contents. Recipients may still see security prompts or be prevented from running macros by their administrator.
When a macro is assigned to a worksheet button, use a clear label such as “Open Documents folder.” A plainly named button helps the user understand that File Explorer will open and that no file is automatically selected. If you later change the macro so it copies, deletes, or processes files, document those additional actions and test them on sample data before using them on important folders.
Windows, Mac, and Excel for the web
The sample uses explorer.exe and the Windows Script Host, so it is intended for Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros; download or open the workbook in desktop Excel to use VBA. Excel for Mac also does not provide Windows File Explorer or the same Windows Script Host object. The Windows code should not be expected to work there. A Mac-specific solution would need to use macOS paths and a Mac-compatible way to open Finder, and should be written and checked for that environment rather than copied unchanged.
In Excel for Mac, the Visual Basic Editor can generally be opened with Option+F11; some keyboards require Fn+Option+F11, depending on function-key settings. The Windows shortcut is Alt+F11. These shortcuts only open the editor; they do not make Windows-specific VBA commands compatible with macOS.
Frequently Asked Questions
How do I open the Documents folder from an Excel button?
Put the main macro in a standard VBA module, then assign that macro to a worksheet button. In Windows Excel, use Developer > Insert to add a Form Control button, and choose OpenDocumentsFolder when prompted to assign a macro.
Does this macro open Documents in Excel?
No. It opens the folder in Windows File Explorer. To choose a file for Excel to work with, use a file picker; to open a workbook itself, use Excel’s file-opening features or code designed to open a workbook.
Will the macro work if Documents is in OneDrive?
It can, provided Windows reports the synchronized Documents location as the user’s special Documents folder and that location is available. The macro asks Windows for the path rather than assuming a particular OneDrive directory. If synchronization is paused or access is restricted, the folder may not be available as expected.
Why does the code put quotation marks around the path?
Windows command lines use spaces to separate arguments. Quotation marks keep a path containing spaces together as a single argument when it is passed to Explorer. Chr$(34) supplies a double-quote character in VBA.
Can I use this macro in Excel for the web or on a Mac?
Excel for the web cannot run VBA. The code shown is Windows-specific and relies on Explorer and Windows Script Host, so it is not a drop-in solution for Excel for Mac. Use a solution built for the operating system where the workbook will run.
Related
- Center Across Selection in Excel: Add It to Quick Access
- Change Block Capitals to Lowercase in Excel
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA InputBox Cancel: Exit a Sub SafelyYes. To stop the current Excel VBA procedure when a user cancels an input box, test the returned…
- VBA Array Duplicate Values: Find, Count, and RemoveYes. VBA arrays can contain duplicate values; VBA does not automatically prevent or remove them. To find repeats,…
- Excel VBA to Open Another Workbook: Code and TipsTo open another Excel file with VBA, call Workbooks.Open and supply the file’s full path. For example, Set…
- Mouse Macro Recorder: How It Works and What to ChooseA mouse macro recorder is software or a device feature that records actions such as clicks, pointer movements,…
- Excel VBA Data Validation Drop-Down Lists: How to Build ThemTo create an Excel data-validation drop-down list with VBA, apply the xlValidateList validation type to the target cells…
- VBA Open Excel File and Save as CSV with Working CodeYes. In VBA, open the workbook with Workbooks.Open, then call SaveAs with FileFormat:=xlCSVUTF8 to create a UTF-8 CSV…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.