Run VBS script for multiple excel files

Question

I want to run a VBS macro for every .xlsx file in a folder. The macro would delete all name ranges in each file.

I have tested and have working code which deletes all nameranges. ALT + F11 and then inserting a module and hitting F5 to run it, however I want this automated and run for every file in a folder without having to open the xlsx file.


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

Possible Duplicate of [Loop through files in a folder using VBA?](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) — Pᴇʜ, Jul 26 '19 at 06:06
You can't change the contents of *any* file without opening it. You can open Excel files with other programs (XML, Visual Studio, etc) but you would still have to open then save them. — , Jul 26 '19 at 07:24

score 0 · Answer 1 · answered Jul 26 '19 at 06:46

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

Run VBS script for multiple excel files

1 Answers1