我有一個 excel 檔案，我們在其中創建了一個自定義子檔案，在保存時，向用戶顯示一個“另存為”對話框，該對話框限制了用戶可以另存為的可用檔案型別串列。這已被一家公司用作模板多年 - 因此他們在不同位置保存了數百個 Excel 檔案，其中包含以下宏：
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' If user is doing a 'Save As', set the file type as "Excel Macro-Enabled Workbook",
' and set the list of available extensions they can save as.
Dim xFileName As String
Dim xFileExt As String
If SaveAsUI <> False Then
Cancel = True
xFileName = Application.GetSaveAsFilename("<job name goes here> CHANNEL SCHEDULE", "CSV UTF-8 (Comma delimited) (*.csv), *.csv," _
& "Excel Macro-Enabled Template (*.xltm), *.xltm," _
& "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm," _
& "Excel Workbook (MACROS DISABLED) (*.xlsx), *.xlsx," _
& "", 3, "Save As xlsm file")
If xFileName <> "False" Then
Application.EnableEvents = False
' Get the file extension.
xFileExt = Right(xFileName, Len(xFileName) - InStrRev(xFileName, "."))
' Save the file.
Select Case xFileExt
Case "csv"
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlCSV
Case "xltm"
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLTemplateMacroEnabled
Case "xlsm"
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Case "xlsx"
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbook
Case Else
MsgBox "Error saving as the selected file type. Please try again, or try a different file type. Sorry."
End Select
Application.EnableEvents = True
Else
'MsgBox "Save As Operation Cancelled."
Cancel = True
Exit Sub
End If
End If
End Sub
同一個檔案也恰好啟用了“始終創建備份”設定，如下圖所示，因此當打開這些檔案中的任何一個時，excel 會自動在與剛剛打開的檔案相同的目錄中創建一個名為“ <檔案的備份”的檔案您剛剛打開的名稱> “。
問題是，現在他們需要在所有 excel 檔案上禁用此功能，或者至少讓最終用戶能夠自己禁用它，以便他們可以手動完成。
我發現禁用此功能的唯一方法是轉到另存為 > 工具 > 常規選項：
但是，使用該Application.GetSaveAsFilename方法時，工具下拉串列中的“常規選項”選項不再可用，如下所示：
我的問題：
- 如果無法從當前的“另存為”對話框訪問“始終創建備份”設定，Excel GUI 中是否還有另一個選項可以禁用“始終創建備份”設定？
- 或者是否有另一種方法可以從該方法創建的自定義另存為對話框中“啟用”常規選項對話框
Application.GetSaveAsFilename？ - Is there a way to programmatically change (disable) this backup setting?
uj5u.com熱心網友回復：
感謝上面評論中的 VBasic2008 和 Skin。
將CreateBackup:=False標志添加到保存/另存為行程將禁用“始終創建備份”設定。
所以：
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlCSV
更改為：
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlCSV, CreateBackup:=False
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/373213.html