Copying an open excel document with VBA
Copying an open excel document with VBA
(OP)
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:
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:
CODE
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"
FileCopy "E:\QC Stuff\" & OpenFileName, NewFileName
End If
End Sub
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"
FileCopy "E:\QC Stuff\" & OpenFileName, NewFileName
End If
End Sub
Talk To Other Members
RE: Copying an open excel document with VBA
I just tried this by modifying your code and the original file name stays open! Not sure what you mean by location
CODE
ck1999
RE: Copying an open excel document with VBA
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.