Monthly Archives: September 2011

Using Save As and keeping Original File and New File open at the same time


I have stopped updating this blog. This link can also be found in my Website.

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

    '~~> 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

Hope it helps Smile