Excel VBA将工作表另存为Excel文件并覆盖文件

原创已于 2022-11-03 16:50:37 修改 · 8.4k 阅读

8 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#excel

收录于

于 2021-07-08 16:52:18 首次发布

excel随笔专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何使用VBA脚本在Excel中禁用显示保存提示，并批量将工作簿另存为指定文件。通过设置Application.DisplayAlerts属性来控制是否显示警告，适用于自动化工作流程中需要高效操作的情况。

保存覆盖不提示

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="d:\工作簿3.xlsx"

剩下的自己录制即可。

举例：

Export Worksheet

Option Explicit

Sub exportSheet()
    
    Const FolderPath As String = "C:\Test\"
    Const FileName As String = "ART.xlsx"
    Const SheetName As String = "Master List"
    
    On Error Resume Next
    Workbooks(FileName).Close
    On Error GoTo 0
    
    With ThisWorkbook
        Application.ScreenUpdating = False
        .Sheets(SheetName).Copy
        With ActiveWorkbook
            Application.DisplayAlerts = False
            .SaveAs FolderPath & FileName, xlOpenXMLWorkbook ' 51 (.xlsx)
            Application.DisplayAlerts = True
            .Close False
        End With
        ' Open Folder Path in Windows File Explorer.
        '.FollowHyperlink FolderPath
        '.Close
        Application.ScreenUpdating = True
    End With
    
End Sub