'一、删除本工作簿内所有工作表的定义名称  

Sub 删除定义名称()

  Dim wb As Workbook, MyF$, MyS
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    MyF = Dir(ThisWorkbook.Path & "\*.xls*")
    Do
        If MyF <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & MyF)
            For Each MyS In ActiveWorkbook.Names
                MyS.Delete
            Next MyS
            wb.Close
        End If
        MyF = Dir
    Loop While MyF <> ""
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
    Dim wb As Workbook, MyF$, MyS
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    MyF = Dir(ThisWorkbook.Path & "\*.xls*")
    Do
        If MyF <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & MyF)
            For Each MyS In ActiveWorkbook.Names
                MyS.Delete
            Next MyS
            wb.Close 1
        End If
        MyF = Dir
    Loop While MyF <> ""
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

'二、删除不某文件夹下非在本工作簿定义的定义名称及错误引用遗留的定义名称

Sub 删除某工作簿中所有excel表的非法遗留的定义名称()
    Dim wb As Workbook, MyF$, MyS
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    MyF = Dir(ThisWorkbook.Path & "\下料单\*.xls*")
    Do
'        If MyF <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\下料单\" & MyF)
            Debug.Print wb.Name
            For Each MyS In ActiveWorkbook.Names
               Debug.Print MyS.Name & " " & MyS
               If Left(MyS, 5) = "='E:\" Or Right(MyS, 6) = "!#REF!" Then
                  MyS.Delete
               End If
            Next MyS
            wb.Close (True)
'        End If
        MyF = Dir
    Loop While MyF <> ""
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

’三、'在打开表的情况下执行
Sub 删除不在本工作簿定义的定义名称()
 For Each d In ActiveWorkbook.Names
 Debug.Print d.Name & " " & d
  If Left(d, 5) = "='E:\" Or Right(d, 6) = "!#REF!" Then
   d.Delete
  End If
 Next
End Sub