• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Delete a certain name in all workbooks in a specific folder

Dear Experts:

I got a - workbook level scope -  name called 'MyNamedRange' in more than 200 xlsx-workbooks that all reside in the same folder C:\ExcelFolder\

I would like to run a macro that deletes this 'workbook level scope' name in each and every workbook in one go.

Help is much appreciated. Thank you very much in advance.

Regards,  Andreas
Andreas Hermle
Andreas Hermle
  • 2
1 Solution
As requested, the starting point is on the initial sheet, but you are prompted for the directory in case you'd like to either confirm/change it, then all files are opened, the workbook scoped name is deleted if it exists, then the file is closed and saved in the loop.

Here's the code:
Option Explicit
Sub deleteNamedRangeFilesInFolder()
Dim dialogFile As FileDialog
Dim strpath As String
Dim fName As String
Dim wkb As Workbook
Dim wks As Worksheet
Dim rgName As String
Dim myName As Name
Dim wkbProcess As Workbook
Dim wksProcess As Worksheet
Dim fPathFname As String

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Control Panel")
    strpath = IIf(Right([folderPath], 1) = "\", [folderPath], [folderPath] & "\")
    rgName = [rngName]
    ' Open the file dialog
    Set dialogFile = Application.FileDialog(msoFileDialogFolderPicker)
    With dialogFile
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        .InitialFileName = strpath
        .Title = "Select Folder for Processing"
    End With
    If dialogFile.SelectedItems.Count > 0 Then
        strpath = dialogFile.SelectedItems(1)
        fName = Dir(strpath & "\*.xlsx")
        If fName <> "" Then
            Application.EnableEvents = False
            Application.DisplayAlerts = False
                fPathFname = strpath & "\" & fName
                Application.StatusBar = "Processing File: " & fPathFname
                Set wkbProcess = Workbooks.Open(Filename:=fPathFname)
                On Error Resume Next
                On Error GoTo 0
                wkbProcess.Close savechanges:=True
                fName = Dir()
            Loop Until fName = vbNullString
        End If
    End If
    MsgBox "Process Complete!"
    Set dialogFile = Nothing
    Application.StatusBar = False
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub

Open in new window

See attached.

Andreas HermleTeam leaderAuthor Commented:
Wow, I am really impressed Dave! Great job, very sophisticated coding. I have tested it several times with different folders and files. It works just fine.

Thank you very much for your time taken, your swift support and your more than professional work.

This forum deserves its name. There is no doubt about it.

Regards, Andreas
Andreas HermleTeam leaderAuthor Commented:
Again, this forum is more than one can dream of.

Great job, Dave!

Regards, Andreas
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now