Creating a Workbook with a custom name without saving it to disk
Is it possible to create a Workbook with a custom name without saving it to disk? I want to avoid the default "Workbook x" names but I don't want to require the user to save the workbook. If I save it automatically in some temporary, the user won't get the "Save As..." dialog if he clicks on "Save", which may be confusing.
Simply create the workbook 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, then the user will get a prompt whether the users want to save (Again a Save As dialog) the file before closing. Now the appearance of this prompt will depend on the fact that you have made some changes to the newly created workbook.
For example
Sub Sample()
Dim wb As Workbook
Set wb = Workbooks.Add
End Sub
By default the workbook will be named as "Book*" but that really shouldn't matter as the user will get a chance to do do a "Save As"
FOLLOWUP
By pressing Ctrl + S. It would show the Save As... dialog just as if the workbook had never been saved.
Though 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 workbook
b) Save it as say, JAN 2012.XLSM, to user's Temp directory
c) Change the file properties to Readonly
d) Now when the user presses CTRL+S, Excel will prompt a 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 (Complicated way of doing it)
a) Create a new workbook
b) Save it as say, JAN 2012.XLSM, to user's Temp directory
c) Inject a code to disable Ctrl + S and only allow Save As
You can use Application Events to manage creating and saving workbooks.
See Application Events from CPearson's site for more info.
My suggestion: create an Addin with an Application Event handler to manage new workbooks:
In the addin ThisWorkbook module (or use a class module as described by Pearson), incluse 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