VBA & Macro Problems

How to Use Excel VBA to Search and Open Files from a Cell Value

WPS Content Manager Oct 10, 2026 872 views

Question details

The user wants to click or select a cell containing a filename in Excel, and use a VBA macro to search for that file in a designated folder and open it using its associated application.

How to Use Excel VBA to Search and Open Files

Product: Excel
Device & OS: Windows
Scenario: Automating file access from an Excel worksheet to quickly pull up related documents, images, or PDFs stored in a specific local or network directory.
Observed behavior: A VBA script is needed to dynamically read the active cell's value, combine it with a predefined folder path, verify if the file exists using the Dir function, and launch it via the Windows Shell command.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and verify that the target cells contain the exact filename, including the file extension (e.g., 'report.pdf').

Solution 1Recommended

Use VBA Shell and Dir Commands to Open the File

Implement a VBA macro that reads the active cell's content, checks for the file's existence in a specified path, and opens it using the Windows command prompt.

This solution utilizes the 'Dir' function to ensure the file exists before attempting to open it, preventing runtime errors. The 'Shell' command combined with 'cmd /c start' allows Windows to handle the file association natively, meaning it works for any file type (PDFs, Word documents, images, etc.).

Open the VBA Editor

Press the ALT + F11 keys simultaneously on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click on 'Insert' in the top menu and select 'Module'. This will open a blank code window.

Paste the Macro Code

Copy the following VBA script and paste it into the module window: Sub OpenFile() Dim fileName As String Dim filePath As String fileName = ActiveCell.Value filePath = "C:\Users\Username\Documents\Files\" If Dir(filePath & fileName) <> "" Then Shell "cmd /c start " & filePath & fileName, vbNormalFocus Else MsgBox "File not found." End If End Sub

Customize the Folder Path

In the pasted code, change 'C:\Users\Username\Documents\Files\' to the actual folder path where your files are stored. Ensure the path ends with a backslash (\).

Run the Macro

Close the VBA editor and return to your Excel worksheet. Click on a cell containing a filename with its extension. Go to the 'View' tab, click 'Macros', select 'OpenFile', and click 'Run'.

Use VBA Shell and Dir Commands to Open the File

Automated Application Launching: Because the macro uses the Windows 'start' command, it will automatically figure out which application to use based on your default Windows settings. You don't need to specify the executable paths for Acrobat, Word, or other applications.

WPS Spreadsheet VBA Support

Automate Your File Management Seamlessly with WPS Office

WPS Spreadsheet fully supports VBA macros, allowing you to run your exact same Excel automation scripts natively. You can easily read cell values, search directories, and launch files using WPS Office's highly compatible and lightweight environment.

1. Download and Install WPS Office: Download WPS Office from the official website and complete the installation, ensuring that you select the version equipped with VBA support.
2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the file list.
3. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'VBA Editor' to view or paste your OpenFile macro code.
4. Execute the Macro: Select the cell with your desired filename, go back to the 'Developer' tab, click 'Macros', select your macro, and click 'Run' to open the file.

Outstanding compatibility with Microsoft Excel VBA syntax and modulesLightweight installation and lightning-fast script executionNative support for Microsoft Office file formats (.xlsx, .xlsm, .docx, .pptx)Free to download with a familiar, tabbed user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro return a 'File not found' message?

This error typically occurs if the cell value does not include the file extension (e.g., it says 'Report' instead of 'Report.pdf') or if the 'filePath' variable in the VBA code is incorrect. Double-check that your folder path ends with a backslash (\).

Can I make the file open by simply clicking the cell instead of running the macro manually?

Yes. Instead of a standard Module, you can place the code inside the specific sheet's 'Worksheet_SelectionChange' event in the VBA editor. This will trigger the code automatically whenever a user clicks a cell within a designated target range.

Does this VBA method search through subfolders?

No, the standard 'Dir' function used in this script only searches the exact folder path specified. To search through subfolders, you would need a more advanced VBA script utilizing a recursive function or the FileSystemObject (FSO).

Will this macro work on Mac?

No, this specific macro relies on the Windows Command Prompt ('cmd /c start') and Windows file paths (C:\...). For Mac, you would need to use AppleScript integrated with VBA and adjust the file paths to macOS standards.

Excel & Data Problems