Power Query Problems

Use the Windows TEMP Folder in Excel Power Query

Chanuka GeekiyanageChanuka Geekiyanage Nov 12, 2026 868 views

Question details

The user needs to use the Windows temporary-folder path as a file location in an Excel Power Query.

Use the Windows TEMP Folder in Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A query must load a file from the Windows TEMP folder without hard-coding a user-specific path such as %TEMP%.
Observed behavior
Power Query does not directly expand the Windows %TEMP% environment variable in a file path, and Excel formulas cannot reliably retrieve operating-system environment variables without additional automation.
Before you start

Confirm the exact temporary-folder path and decide whether the query should work for one user or across multiple users. If automation is unavailable, prepare a stable workbook folder where the source file can be copied before each refresh.

Solution 1Recommended

Retrieve the TEMP path with VBA and pass it to Power Query

Use this approach when the workbook runs in desktop Excel and needs to resolve each Windows user's TEMP folder automatically.

Power Query does not evaluate Windows environment variables such as %TEMP% inside a source path. VBA can read the environment variable, write the resolved path to a worksheet cell or named range, and let Power Query use that value as a parameter.

1
Create a parameter cell

In Excel, enter a heading such as TempFolder and reserve a nearby cell for the resolved path. Select the path cell, open Formulas > Define Name, and name it TempFolder.

2
Read the Windows variable in VBA

Press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and add VBA that uses Environ$("TEMP") to write the current user's TEMP path into the named range TempFolder.

3
Load the named range into Power Query

Select the parameter cell and choose Data > From Table/Range. In Power Query, reference the resulting value when constructing the file path, or use Manage Parameters to connect the query to the workbook value.

4
Refresh the query

Return to Excel and select Data > Refresh All. Confirm that the query uses the resolved path rather than the literal text %TEMP%.

Macro security applies: The workbook must allow VBA to run. If macros are blocked or the workbook is used in Excel for the web, use a stable folder and copy-file workflow instead.
Free Microsoft Office alternative

Move routine spreadsheet work to WPS Office with familiar file support

WPS Office is a free, lightweight Microsoft Office alternative for everyday spreadsheet editing and file preparation. It supports familiar Excel formats, offers a familiar interface for migrating workbooks, and can help organize source files before they are processed by a dedicated Power Query workflow.

  1. 1. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheets from the home screen.
  2. 2. Open the Excel workbook: Choose Open, select the existing .xlsx or .xls workbook, and review the sheets and source-file references.
  3. 3. Organize the source file: Use Save As or File Manager to place the workbook and its input files in a stable, shared folder instead of relying on a user-specific %TEMP% path.
Free and lightweight for everyday spreadsheet editing and file preparationFamiliar spreadsheet interface that supports a smooth migration from ExcelCompatible with Microsoft Excel XLS and XLSX filesUseful for organizing source files in a stable workbook folder before refresh workflows
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel Power Query use %TEMP% directly in a file path?

No. Power Query does not directly expand the Windows %TEMP% environment variable in a source path. Resolve the value with VBA or automation, or use a stable known folder.

Can an Excel formula return the Windows TEMP folder?

Not reliably with standard worksheet formulas alone. Use VBA, such as the Environ function, to retrieve the environment variable and place the result in a named cell or table for Power Query.

How do I pass a VBA-generated TEMP path to Power Query?

Write the path to a worksheet cell, define a name such as TempFolder for that cell, and load or reference the value in Power Query as a parameter before selecting Data > Refresh All.

What is the easiest no-macro workaround?

Copy the required file from the temporary folder into a known workbook or shared folder, then connect Power Query through Data > Get Data > From File > From Folder.

Will the VBA method work in Excel for the web?

VBA requires desktop Excel and appropriate macro permissions. For Excel for the web, use Power Automate or place the source file in a stable OneDrive or SharePoint location instead.