VBA & Macro Problems

How to Move the Original File After Saving a Converted Workbook in VBA

Tauseeq Magsi Oct 23, 2026 868 views

Question details

The user needs a VBA macro to move the original raw data file into an archive folder after converting and saving it in a new format.

How to Move the Original File After Saving a Converted Workbook in VBA

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Automating a workflow where a raw file is processed, saved in a new format, and the original file is archived to keep the working folder clean.
Observed behavior: The goal is to successfully move the original file after the SaveAs operation is executed without losing the reference to the original file's path and name.

Before you start

Ensure you have a destination archive folder already created, as the VBA Name statement will throw a 'Path not found' error if the target directory does not exist.

Solution 1Recommended

Store Original Path Before SaveAs and Move Using Name Statement

The SaveAs method changes the ActiveWorkbook reference to the new file. To move the original file, you must store its path and name in string variables before saving.

When you use ActiveWorkbook.SaveAs, VBA immediately changes the focus to the newly saved file. If you try to reference ActiveWorkbook.Path after saving, you will get the new file's location, not the original one. Capturing the original properties into variables beforehand is essential.

Declare string variables

Create variables for your paths by typing: Dim OldPath As String, OldName As String, NewPath As String.

Store the original file details

Before executing the save command, assign the active workbook's properties: OldPath = ActiveWorkbook.Path and OldName = ActiveWorkbook.Name. Also, define your NewPath variable with the destination folder path.

Save the converted workbook

Execute your save code. For example: ActiveWorkbook.SaveAs Filename:=Path & FName, FileFormat:=51, CreateBackup:=False.

Move the original file

Use the Name statement to move it: Name OldPath & Application.PathSeparator & OldName As NewPath & Application.PathSeparator & OldName.

Store Original Path Before SaveAs and Move Using Name Statement

Fast and Efficient Moving: Using the native VBA Name statement effectively moves a file from one directory to another without needing to use external libraries or open the file again.

Solution 2

Move the Original File Using FileSystemObject (FSO)

An alternative approach using the FileSystemObject, which provides more robust file management capabilities and easier error handling when moving files.

Advanced VBA Support in WPS

Automate Your Workflows with WPS Spreadsheet

WPS Spreadsheet offers excellent compatibility with Excel VBA, allowing you to run macros, handle file conversions, and manage automated file-moving workflows effortlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your raw data workbook in WPS Spreadsheet.
2. Access the Developer Tab: Go to the 'Developer' tab in the ribbon and click on 'Visual Basic' to open the VBA Editor.
3. Insert Your Macro Code: Insert a new Module, paste your file conversion and Name statement moving code, and run the macro to automate the process.

Seamless compatibility with Microsoft Excel VBA formats (.xlsm, .xlsb)Lightweight and fast execution for macro-heavy filesBuilt-in developer tools for easy code editing and debuggingFree and fully functional alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Path not found' error when using the Name statement?

This error occurs if the destination folder specified in your NewPath variable does not exist. Ensure the archive folder is created before running the macro, or add VBA code (using MkDir) to create the folder if it is missing.

Does the SaveAs method automatically delete the original file?

No, SaveAs creates a brand-new file in the specified format and location, leaving the original file exactly where it was. You must manually move or delete the original file using VBA commands like Name or Kill.

Can I use the Name statement to rename the original file while moving it?

Yes. By changing the file name string at the end of the destination path (the part after 'As' in the Name statement), you can simultaneously move and rename the file in a single line of code.

Excel & Data Problems