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


EDIT

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

http://www.siddharthrout.com/2011/09/02/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

Hope it helps Smile

Advertisements

2 responses to “Using Save As and keeping Original File and New File open at the same time

  1. sam October 16, 2011 at 6:23 am

    Siddharth…

    You can use the SaveCopyAs method instead…

    Sub NewSaveAsRoutine()

    ThisWorkbook.SaveCopyAs “D:\Data\CopyOf_” & ThisWorkbook.Name
    Workbooks.Open Filename:=”D:\Data\CopyOf_” & ThisWorkbook.Name

    End sub

  2. Siddharth Rout October 16, 2011 at 1:53 pm

    Nice Code Sam! It works perfectly. Thanks for sharing. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: