Using Save As and keeping Original File and New File open at the same time
Today while answering one of the questions in the MSDN forum, I came across an interesting question. The question was, “Can we save an excel file with a new name generated by the macro and keep the original file and the new file both open?”
We are aware that the moment you do a SaveAs in Excel, the original file is closed and the new saved file remains open. What if we want to keep the original file and the new file open at the same time? Is it possible?
The answer is; “Yes“, it is possible.
After struggling with it for a file, an interesting solution came to my mind and to my surprise it worked as expected. The trick is NOT to use the inbuilt .SaveAs command. The alternative, is to save the original file first and then make a copy of it and then finally open it.
As expected, I used the FileCopy command and then realized that FileCopy doesn’t make a copy of the file if the file is open. You will get a “Permission Denied” error. Instead of FileCopy, I then used CopyFile API and it worked just fine.
Here is the code:
Private Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" _ (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _ ByVal bFailIfExists As Long) As Long Sub NewSaveAsRoutine() Dim FileExtStr As String, sPath As String, NewFileName As String Dim wb1 As Workbook, wb2 As Workbook Dim FileFormatNum As Long Set wb1 = ActiveWorkbook sPath = wb1.Path FileExtStr = ".xlsm": FileFormatNum = 5 NewFileName = sPath & "" & "NewFile" & FileExtStr '~~> Save the Original file before making a copy of it wb1.Save '~~> Make a copy of the original File with a new name CopyFile sPath & "" & wb1.Name, NewFileName, 0 '~~> Open the New Workbook (Copy of Original) Set wb2 = Workbooks.Open(NewFileName) End Sub