vba 使用自定义名称创建工作簿而不将其保存到磁盘

Creating a Workbook with a custom name without saving it to disk

提问by Cutter

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.

是否可以使用自定义名称创建工作簿而不将其保存到磁盘？我想避免使用默认的“工作簿 x”名称，但我不想要求用户保存工作簿。如果我将它自动保存在某个临时文件中，则用户在单击“保存”时将不会看到“另存为...”对话框，这可能会造成混淆。

采纳答案by Siddharth Rout

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"

默认情况下，工作簿将被命名为“Book*”，但这并不重要，因为用户将有机会执行“另存为”

FOLLOWUP

跟进

By pressing Ctrl + S. It would show the Save As... dialog just as if the workbook had never been saved.

通过按 Ctrl + S。它会显示另存为...对话框，就像从未保存过工作簿一样。

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 :)

虽然我提到我只能想到一种方法，但是在处理代码时，我想出了 2 个选项:)

WAY 1

方式一

a) Create a new workbook

a) 创建一个新的工作簿

b) Save it as say, JAN 2012.XLSM, to user's Temp directory

b) 将其另存为 JAN 2012.XLSM，到用户的 Temp 目录

c) Change the file properties to Readonly

c) 将文件属性更改为只读

d) Now when the user presses CTRL+S, Excel will prompt a Save As

d) 现在当用户按下 CTRL+S 时，Excel 会提示另存为

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)

方法2（复杂的方法）

a) Create a new workbook

a) 创建一个新的工作簿

b) Save it as say, JAN 2012.XLSM, to user's Temp directory

b) 将其另存为 JAN 2012.XLSM，到用户的 Temp 目录

c) Inject a code to disable Ctrl + S and only allow Save As

c) 注入代码以禁用 Ctrl + S 并只允许另存为

回答by chris neilsen

You can use Application Eventsto manage creating and saving workbooks.

您可以使用应用程序事件来管理创建和保存工作簿。

See Application Eventsfrom CPearson's site for more info.

有关更多信息，请参阅CPearson 站点上的应用程序事件。

My suggestion: create an Addin with an Application Event handler to manage new workbooks:

我的建议：创建一个带有应用程序事件处理程序的插件来管理新工作簿：

In the addin ThisWorkbookmodule (or use a class module as described by Pearson), incluse this code

在插件ThisWorkbook模块（或使用 Pearson 描述的类模块）中，包含此代码

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