VBA & Macro Problems

How to Close and Delete a Temporary Excel Workbook Using VBA

WPS Editor Oct 10, 2026 869 views

Question details

The user needs to use a VBA script to automatically close a temporary Excel workbook and delete the file from the hard drive without causing errors.

How to Close and Delete a Temporary Excel Workbook Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating workbook management where a temporary file is generated, used, and then needs to be cleaned up and deleted via a macro.
Observed behavior: The VBA script may fail to delete the workbook if the file remains open, the designated path is incorrect, or the system hasn't released the file handle before executing the Kill command.

Before you start

Ensure you have saved any necessary data from the temporary workbook, and verify that the file is not actively being accessed by another user or application.

Solution 1Recommended

Use the Kill Command After Closing the Workbook

The most reliable method to delete a temporary workbook is to store its file path, close the workbook without saving, force the system to release the file handle, and then execute the Kill command.

When attempting to delete a workbook using VBA, Excel may throw a 'Permission Denied' error if the file is still considered 'open' by the operating system. To resolve this, you must explicitly close the workbook and use the 'DoEvents' command to briefly pause execution, ensuring the system has enough time to release the file lock.

Store the file path

Declare a string variable (e.g., Dim strPath As String) and assign the workbook's full path to it using 'strPath = wbOutput.FullName'. This ensures VBA remembers where the file is located after it is closed.

Close the workbook

Use the command 'wbOutput.Close SaveChanges:=False' to close the temporary workbook without triggering a save prompt.

Release the file handle

Insert the 'DoEvents' command on the next line. This yields execution to the operating system, allowing it to fully process the file closure.

Delete the file

Finally, use the command 'Kill strPath' to permanently delete the closed workbook from your storage drive.

Use the Kill Command After Closing the Workbook

Prompting Before Deletion: If you are not sure you want to delete the file every time, you can add a MsgBox function with vbYesNo before the Close and Kill commands to ask the user whether to delete or retain the temporary workbook.

WPS Macro Support

Manage Workbooks and Run VBA Macros seamlessly with WPS Office

WPS Spreadsheets offers comprehensive compatibility with Microsoft Excel VBA macros. You can easily write, edit, and execute your VBA scripts to manage temporary workbooks directly within the application.

1. Enable the Developer Tab: Open WPS Spreadsheets, go to the settings, and ensure the 'Developer' tab is enabled on your ribbon.
2. Open the VBA Editor: Click the 'Developer' tab and select 'Visual Basic' to launch the VBA Editor.
3. Insert your Code: Create a new module and paste your workbook deletion code, ensuring you include the Close and Kill commands.
4. Run the Macro: Click 'Run' or trigger the macro from a custom button to execute the automated close and delete process.

Highly compatible with Microsoft Excel VBA macros and .xlsm formatsFamiliar Developer tab interface for seamless macro editingLightweight software with fast execution of automated scriptsFree to use with comprehensive spreadsheet capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Permission Denied' error when using the Kill command?

This error occurs because the file is still open in Excel, or another program is actively accessing it. You must close the workbook via VBA first and include the 'DoEvents' command to allow the operating system to release the file handle before attempting to delete it.

Does the VBA Kill command send the deleted workbook to the Recycle Bin?

No, the 'Kill' command permanently deletes the file from your computer. It bypasses the Recycle Bin completely, so ensure you no longer need the temporary file data before running your code.

How can I verify if the file path is correct before deleting?

You can use the 'Dir' function in VBA to check if the file exists before executing the Kill command. For example, using 'If Dir(strPath) <> "" Then' allows you to confirm the file is located at the specified path, preventing runtime file-not-found errors.

Excel & Data Problems