Use the Windows TEMP Folder in Excel Power Query
Question details
The user needs to use the Windows temporary-folder path as a file location in an 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.
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.
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.
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.
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.
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.
Return to Excel and select Data > Refresh All. Confirm that the query uses the resolved path rather than the literal text %TEMP%.
Copy the source file to a known workbook folder
Use this simpler option when the query must avoid macros, environment variables, and user-specific paths.
Use Power Automate or another automation workflow
Use automation when the temporary file is created by a repeatable process and the path or file needs to be supplied before the query runs.
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. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheets from the home screen.
- 2. Open the Excel workbook: Choose Open, select the existing .xlsx or .xls workbook, and review the sheets and source-file references.
- 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.

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.




