VBA & Macro Problems

Fix VBA Run-time Error 9: Activate Workbook with Wildcard Filename

Camila Milosovich Sep 28, 2026 870 views

Question details

The user needs to open and activate a workbook via VBA when the filename is only partially known, using a wildcard (*).

How to Open and Activate a VBA Workbook When Its Filename Contains a Wildcard

Product: Spreadsheet
Device & OS: not provided
Scenario: Running a VBA macro to open multiple files and attempting to activate a specific workbook using a wildcard string.
Observed behavior: The macro throws Run-time error 9 ('Subscript out of range') because wildcards cannot be directly used as a string reference within the Workbooks collection.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet application and save your current macro workbook to prevent data loss in case of a continuous loop or runtime crash during testing.

Solution 1Recommended

Assign the Opened Workbook to an Object Variable

This is the most reliable method. Instead of using a wildcard to find the workbook after it is open, you can capture the exact workbook reference immediately upon opening it.

The Workbooks collection expects an exact string match to identify an open file. By assigning the opened workbook directly to an object variable, you bypass the need to reference the file's name altogether.

Declare your variables

In the VBA editor, declare a Workbook object variable by typing `Dim wb As Workbook` and a string variable for the filename by typing `Dim fileName As String`.

Use the Dir function to resolve the wildcard

Use the `Dir` function to capture the exact file name from your wildcard path. For example: `fileName = Dir("C:\YourFolder\*Sales*.xlsx")`.

Open and assign the workbook

Open the file and simultaneously assign it to your object variable using the `Set` command: `Set wb = Workbooks.Open("C:\YourFolder\" & fileName)`.

Activate the workbook via the variable

Now you can safely activate or manipulate the workbook without using wildcards by referencing the variable, such as typing `wb.Activate` in your script.

Assign the Opened Workbook to an Object Variable

Best Practice: Using object variables (like `wb.Activate` or `wb.Sheets(1)`) is much faster and less prone to errors than relying on `ActiveWorkbook` or selecting windows by name.

Solution 2

Loop Through Open Workbooks to Match the Wildcard

If the workbook is already open in the background and you only know a partial name, you can iterate through all open workbooks using the VBA 'Like' operator.

Write and Execute VBA Macros Seamlessly in WPS Office

WPS Spreadsheet offers powerful built-in VBA support, allowing you to run macros, handle object variables, and automate repetitive tasks with identical syntax to Microsoft Excel.

1. Install WPS Office: Download and install the latest version of WPS Office Free.
2. Open your macro workbook: Launch WPS Spreadsheet and open your .xlsm or .xls file.
3. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the VBA Editor.
4. Run your fixed macro: Paste your updated object variable VBA code into the module and click the Run button to execute it without error.

Fully compatible with Microsoft Excel VBA macros (.xlsm) and scripts.Provides a built-in VBA editor with debugging tools to easily fix Run-time errors.Lightweight software that runs complex macros smoothly without lagging.Cost-effective alternative for advanced spreadsheet automation.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get Run-time error 9 when using wildcards in Workbooks.Activate?

Run-time error 9 ('Subscript out of range') occurs because the Workbooks() collection requires the exact string name of the open workbook to index it properly. The application does not parse wildcard characters like '*' or '?' when trying to locate an object by name.

How do I find a file path in a folder using a wildcard in VBA?

You can use the built-in VBA `Dir` function. For example, typing `Dir("C:\MyFolder\*.xlsx")` will return the exact filename string of the first file in that folder matching the wildcard pattern, which you can then safely pass to `Workbooks.Open`.

Are Excel VBA macros compatible with WPS Spreadsheet?

Yes, WPS Office offers excellent compatibility with Excel macros. WPS Spreadsheet natively supports standard VBA syntax, user forms, and object variables, allowing you to run, edit, and debug scripts written in Excel.

Excel & Data Problems