Create a book with a custom name without saving to disk

Question

Is it possible to create a book with a custom name without saving to disk? I want to avoid the default names "Workbook x", but I do not want the user to save the workbook. If I save it automatically in a certain temporary mode, the user will not receive the "Save As ..." dialog box if he clicks on "Save", which may be misleading.

Answer 1

Just create a book and don’t save it, so when the user tries to save it, the user will get a “Save As” prompt. And if the user tries to close it, the user will receive a hint whether users want to save (again the "Save As" dialog) the file before closing. Now the appearance of this invitation will depend on the fact that you have made some changes to the newly created book.

for example

Sub Sample() Dim wb As Workbook Set wb = Workbooks.Add End Sub 

By default, the book will be called "Book *", but it really does not matter, since the user will be able to do "Save As"

Followup

By pressing Ctrl + S. The Save As ... dialog box appears, as if the book were not saved.

Although I mentioned that there is only one way that I can think of, but while working on the code, I came up with 2 options :)

WAY 1

a) Create a new book

b) Save it, for example, JAN 2012.XLSM, to the Temp user directory

c) Change the file properties to Readonly

d) Now when the user presses CTRL + S, Excel asks "Save As"

 Option Explicit Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _ (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long Private Const MAX_PATH As Long = 260 Sub Sample() Dim wb As Workbook Set wb = Workbooks.Add With wb .SaveAs Filename:=TempPath & "JAN 2012.xlsm" _ , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False '.SaveAs Filename:=TempPath & "JAN 2012.xlsx" _ , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False .ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin" End With End Sub Function TempPath() As String TempPath = String$(MAX_PATH, Chr$(0)) GetTempPath MAX_PATH, TempPath TempPath = Replace(TempPath, Chr$(0), "") End Function 

WAY 2 (Hard way to do it)

a) Create a new book

b) Save it, for example, JAN 2012.XLSM, to the Temp user directory

c) Enter the code to disable Ctrl + S and enable Save As

Answer 2

You can use Application Events to control the creation and storage of books.

For more information, see Application Events on CPearson.

My suggestion: create an Addin with an event handler for managing new books:

In the addin module of ThisWorkbook (or use the class module as described by Pearson), include this code

 Option Explicit ' Establish object to handle events Public WithEvents App As Application Private Sub Workbook_Open() Set App = Application End Sub ' Handle new workbook Private Sub App_NewWorkbook(ByVal Wb As Workbook) MsgBox "New Book..." Wb.SaveAs "Your Path and File Name Here" End Sub ' Intercept save event Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) MsgBox "Saving " & Wb.Name End Sub