vba 保存时设置默认文件名的 Excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21465105/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Macro to set a default filename when saving
提问by user3254556
I am not a programmer, but I understand a little about it. I currently have a macro in word that saves a new document with a default name and current date. I am trying to do the same in excel 2010 but don't know how.
我不是程序员，但我对它有一点了解。我目前在 word 中有一个宏，可以使用默认名称和当前日期保存新文档。我试图在 excel 2010 中做同样的事情，但不知道如何。
Any help would be greatly appreciated. This is the macro for word that I use.
任何帮助将不胜感激。这是我使用的 word 宏。
Sub FileSave()
If ActiveDocument.Path = "" Then
With Dialogs(wdDialogFileSaveAs)
.Name = MakeDocName
.Show
End With
Else
ActiveDocument.Save
End If
End Sub
Function MakeDocName() As String
Dim theName As String
Dim uscore As String
uscore = "_"
theName = "DocType_DocDescription_"
theName = theName & Format(Now, "yyyy-mm-dd")
MakeDocName = theName
End Function
Sub FileSaveAs()
With Dialogs(wdDialogFileSaveAs)
.Name = MakeDocName
.Show
End With
End Sub
回答by Joe
Updated in response to comment. Adding this code to a ThisWorkbookmodule will produce the customized file name when any open document is saved.
更新以回应评论。将此代码添加到ThisWorkbook模块将在保存任何打开的文档时生成自定义文件名。
Option Explicit
Private WithEvents App As Excel.application
Private Sub Workbook_Open()
Set App = application
End Sub
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
App.EnableEvents = False
If Wb.Path = "" Then
With App.Dialogs(xlDialogSaveAs)
Call .Show(MakeDocName, xlOpenXMLWorkbookMacroEnabled)
End With
Else
Wb.Save
End If
App.EnableEvents = True
Cancel = True
End Sub
Function MakeDocName() As String
Dim theName As String
Dim uscore As String
uscore = "_"
theName = "DocType_DocDescription_"
theName = theName & Format(Now, "yyyy-mm-dd")
MakeDocName = theName
End Function
回答by Siddharth Rout
Use the inbuilt .GetSaveAsFilename
使用内置的 .GetSaveAsFilename
The syntax is
语法是
expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)
expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)
Example
例子
Option Explicit
Sub Sample()
Dim Ret
Dim InitFile As String
InitFile = "MyFile" '<~~ You can set Default name here
Ret = Application.GetSaveAsFilename(InitialFileName:=InitFile, _
fileFilter:="Excel Files (*.xlsx), *.xlsx", _
FilterIndex:=1, _
Title:="Save As")
If Ret <> False Then
'
'~~> Code to save the file
'
End If
End Sub
