Copying an open excel document with VBA

[HarlanJ]

Hello,

I've written a procedure that checks the current year against a user-input year. If the two differ (i.e. the user selects next year), the workbook will be copied and saved with the current year appended to the file name.

However, I'm using the FileCopy statement and it returns an error if used on an open file. How would I copy the currently open excel file? I could use the activeworkbook.savecopyas statement, but this would change the location and name of the open file.

I would like to keep the original file open while I save the content under a different filename. Is this possible?

Here is the relevant code so far:

Public Sub EndOfYearCheck(NewPageName As Variant)

    Dim BaseDirectory As String
    Dim OpenFileName As String
    Dim OldFileName As String
    Dim NewFileName As String
    
    'Checking if a new year has passed
    If NewPageName(2) <> Year(Now) Then
'        If NewPageName(2) = "" Then 'use didn't enter anything
'            MsgBox "No year entered. Aborting...", vbCritical
'            End 'stop execution
'        End If
        MsgBox "New Year Detected. The previous year's data will now be saved and a new workbook will be created.", vbInformation
       
        '*******Enter Saving Information*******
        BaseDirectory = "H:\Test Folder\"
        OpenFileName = ActiveWorkbook.Name
        OldFileName = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) 'I want to return just the name of the open workbook, not the .xls file extension
        'MsgBox FileName
        
        NewFileName = BaseDirectory & OldFileName & " - " & (Year(Now) - 1) & ".xls"
        [b]FileCopy "E:\QC Stuff\" & OpenFileName, NewFileName[/b]
    End If

End Sub

 

[ck1999]

I could use the activeworkbook.savecopyas statement, but this would change the location and name of the open file.

I just tried this by modifying your code and the original file name stays open! Not sure what you mean by location

ActiveWorkbook.SaveCopyAs NewFileName

ck1999

 

[HarlanJ]

Hey ck,

Hmm. I just used the savecopyas method and it worked perfectly. I'm not quite sure why I couldn't get it before.

Thanks for your help. ;-)