为多个 Excel 文件运行 VBS 脚本

Question

我想为文件夹中的每个.xlsx文件运行VBS宏。该宏将删除每个文件中的所有名称范围。

我已经测试过并具有删除所有名称范围的工作代码。ALT + F11，然后插入一个模块并按 F5 运行它，但是我希望它自动运行并针对文件夹中的每个文件运行，而无需打开 xlsx 文件。

Dim RangeName As Name

On Error Resume Next
For Each RangeName In Names
    ActiveWorkbook.Names(RangeName.Name).Delete
Next
On Error GoTo 0
End Sub

Answer 1

Dim Path, Filename As String
Dim nName As Name
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Path = "" 'FolderPath
Filename = Dir(Path & "*.xlsx") 'xlsx, xls etc.
Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, IgnoreReadOnlyRecommended:=True
    For Each nName In ActiveWorkbook.Names
        nName.Delete
    Next nName
    Workbooks(Filename).Close savechanges:=True
    Filename = Dir()
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True