VBA & Macro Problems

How to Retrieve an Excel Data Source Path with VBA

Tauseeq Magsi Oct 21, 2026 869 views

Question details

Identify the runtime file path used by a workbook connection or Power Query data source using Excel VBA.

How to Retrieve an Excel Data Source Path with VBA

Product: Excel
Device & OS: not provided
Scenario: A user needs a VBA script to detect the dynamic file path of an external data source or workbook connection, which may be stored in a query, parameter, or worksheet.
Observed behavior: Excel objects like Workbook.Connections do not expose a universal property for the final runtime file path, making it difficult to directly retrieve dynamically generated paths.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet ribbon so you can access the VBA Editor. Keep in mind that dynamic paths stored in Power Query parameter tables cannot always be determined without inspecting the specific query formulas or directly evaluating the worksheet cells containing the path text.

Solution 1Recommended

Open the Source Workbook Directly with VBA

If the data source path is known or mapped to a specific variable, the most reliable method is to use the Workbooks.Open method rather than relying on connection properties.

Because the Workbook.Connections collection does not guarantee a directly exposed runtime path (especially for dynamic Power Query connections), bypassing the connection object and using native workbook opening methods is often the cleanest solution.

Access the VBA Editor

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

Insert a New Module

In the Project Explorer pane on the left, right-click on your current workbook project, click Insert, and select Module.

Write the Macro Code

In the new module window, define a string variable for your path and use the Workbooks.Open method. For example, set sPath = "C:\Users\mynane\Desktop\SourceData\_Table.xlsx" and call Set wb2 = Workbooks.Open(sPath).

Run the Script

Place your cursor inside the newly written Sub procedure and press F5, or click the green Run button in the top toolbar to execute the code and open the target data source.

Open the Source Workbook Directly with VBA

Dynamic Input Alternative: Instead of hardcoding the path in VBA, you can reference a specific cell containing the path parameter, such as sPath = ThisWorkbook.Sheets("Settings").Range("B1").Value.

Solution 2

Inspect Queries and Connections using VBA

Loop through the workbook's built-in collections to identify hardcoded paths inside query definitions.

Execute VBA Macros Seamlessly in WPS Spreadsheets

WPS Office provides excellent built-in support for VBA and Excel macros. You can write, edit, and run your VBA scripts to manage data source paths and automate repetitive tasks just as you would in Microsoft Excel.

1. Launch WPS Spreadsheets: Open your macro-enabled workbook in WPS Spreadsheets.
2. Navigate to Developer Tools: Click on the Developer tab in the top ribbon menu to access advanced scripting options.
3. Open the VBA Editor: Click on the 'Visual Basic' icon (or press ALT + F11) to launch the integrated macro editor.
4. Run Your Custom Scripts: Insert your modules, paste your Workbooks.Open scripts, and execute them instantly to manage your data sources.

Full native support for VBA macros and scriptingHigh compatibility with Microsoft Excel formats (.xlsm, .xlsx, .csv)Lightweight architecture for faster data processingAdvanced developer tools available right out of the box

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I find the exact file path in the Workbook.Connections object?

Because modern Excel data connections, particularly those using Power Query, often rely on dynamic variables or parameter tables. As a result, the exact runtime file path is not always exposed as a simple text property within the standard Connections collection.

How do I dynamically pass a file path to VBA?

You can store the file path in a specific cell on a worksheet (for example, cell A1 on a 'Parameters' sheet). In your VBA code, assign that cell's value to a string variable (e.g., myPath = Sheets("Parameters").Range("A1").Value) and pass it to the Workbooks.Open(myPath) function.

Can I use VBA to change a Power Query data source path?

Yes, but you must modify the underlying query formula. Using the ActiveWorkbook.Queries("QueryName").Formula property in VBA, you can replace the string containing the old path with the new path string, which will update the connection upon refresh.

Excel & Data Problems