Open Workbook, save as a new file, work in the new file, close the original Workbook

[Vizar]

I have a Template document on the Desktop which I want to open, then it must ask me if I want to save a copy of the Template document to a New Document, and if so, I want to continue to work in the New Document. The Template document must be closed without saving. The code below create and opens a New Document but on the SaveCopyAs line, I get a Run-time error 1004 indicating Excel cannot access the New Document file although the document was created. Please help!

Private Sub Workbook_Open()

Dim answer As Integer
Dim TemplatePath, TemplateName As String

' Ask if a copy of the template document must be saved. If 'No', exit
answer = MsgBox("Save a copy of the template document as 'New Document'?", _
vbQuestion + vbYesNo + vbDefaultButton2)
If answer = vbNo Then Exit Sub

' Save the current filename and path
TemplateName = ThisWorkbook.Name
TemplatePath = ThisWorkbook.Path

' Save a copy of the Template as a new file with filename "New Document"
' Continue to work in the New Document. Close the Template document.
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs TemplatePath & "\New Document.xlsm"
Workbooks.Open (TemplatePath & "\New Document.xlsm")
Workbooks(TemplatePath & "\" & TemplateName).Close SaveChanges:=False
Application.DisplayAlerts = True

End Sub

 

[dmt32]

Hi welcome to forum
Have you saved your Template with FileFormat xlOpenXMLTemplateMacroEnabled (.xltm)?
If this is so, SaveCopyAs method does not allow you to change the the FileFormat of the file you are copying & may explain your error.

To save in another fileformat, you should use SaveAs.

Dave

 

[Vizar]

Hi Dave,
No, I am saving the new file in the same file format (.xlsm).
SaveAs will not work because it will re-name the template file and I want to keep the template file unchanged.

I have tried various options and the code below finally works -

Private Sub Workbook_Open()

Dim answer As Integer
Dim TemplatePath, TemplateName, NewFileName As String

' Open the Project Document template file, ask if a copy of the file must be saved for a new project request.
' If not, the Project Document template file is open for editing / updates.
' If required, make a copy of the Project Document template file and name it 'New Project Document.xlsm'.
' Continue to work in the New Project Document file.
' Close the Project Document template file without saving (no changes have been made to the template file).

' Save the Project Document template file name and path
TemplateName = ThisWorkbook.Name
TemplatePath = ThisWorkbook.Path

' Define the filename for the New Project Document file
NewFileName = TemplatePath & "\New Project Document.xlsm"

' If there is an existing New Project Document file when the template document is opened, then an error message
' will be displayed requesting that the file is deleted in order to proceed. The template document will be closed.
' If the active file is the New Project Document file (re-opened), then the rest of the procedure will be ignored
' because the file exists already (the user opened it to edit it).
If Dir(NewFileName) <> "" Then
' Ignore the rest of the procedure if an existing New Project Document file is opened
If ThisWorkbook.Name = "New Project Document.xlsm" Then
Exit Sub
Else
' Else, if the template document is opened and there is an existing New Project Document, request that the
' New Project Document is deleted in order to proceed. Close the template document.
MsgBox ("There is an existing 'New Project Document.xlsm' file. Please delete the file if a new " & _
"Project Document file must be created.")
ActiveWorkbook.Close SaveChanges:=False
End If
End If

' Ask if a copy of the Project Document template file must be saved. If 'No', exit
answer = MsgBox("Save a copy of the Project Document template file as 'New Project Document'?", _
vbQuestion + vbYesNo + vbDefaultButton2)
If answer = vbNo Then Exit Sub

' Save a copy of the Project Document template file as a new file with the name "New Project Document"
Application.DisplayAlerts = False
ThisWorkbook.SaveCopyAs NewFileName
Application.DisplayAlerts = True

' Open the new Project Document file
Workbooks.Open NewFileName

' Close the Project Document template file without saving it (it is a template - no changes made)
ThisWorkbook.Close SaveChanges:=False

End Sub

 

[dmt32]

Hi
good that you have managed to resolve yourself and appreciate your feedback.

As a friendly tip, next time need assistance from forum, take time to have a read How To Post Code
This makes reading & copying your code much easier for those giving you help.

Good luck with your project


Dave