Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 897
  • Last Modified:

Delete names from excel workbook semi-manually - VBScript

I'm trying to set a vbs tool that will delete all defined names in a workbook. I have names that don't delete automatically in a vba script, so I can't use exclusively
for each nm in activeworkbook.names
nm.delete
next nm

Open in new window

It needs to work in excel 2003 and excel 2007/10. If I run a sendkeys macro in excel 2003, it waits as soon as the name manager opens and resumes when it's closed. Not working well on that front.
I'm attaching the current vbscript I have, which doesn't delete the names and errors out, as well as a test file containing non-valid names.

Thanks for your help,

Thomas
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False

myExcelWorker.Workbooks.Open("C:\Book1.xlsx")

Set oWB =myExcelWorker.ActiveWorkbook
myExcelWorker.Visible=True

set WshShell = WScript.CreateObject("WScript.Shell")
WScript.Sleep 100
WshShell.SendKeys "^{F3}"
WScript.Sleep 200

'I need a first pass to make all names visible

'A second pass to delete all valid names

'A third pass to delete through the name manager the non-valid names


For i = 1 to oWB.Names.Count

	WScript.Sleep 100
	WshShell.SendKeys "%D"
	WScript.Sleep 250
	WshShell.SendKeys "~"
	WScript.Sleep 250
	WshShell.SendKeys "{RIGHT}"
	WScript.Sleep 100
Next

'myExcelWorker.ActiveWorkBook.Close True

'myExcelWorker.Quit
'set myExcelWorker = Nothing

Open in new window

Book1.xlsx
0
nutsch
Asked:
nutsch
  • 10
  • 9
  • 3
1 Solution
 
RobSampsonCommented:
Hi, this post covers a few methods of removing Names...hopefully it can help you.
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25156886.html

Regards,

Rob.
0
 
nutschAuthor Commented:
Thanks Rob, I've checked that and that's not really working for me. Do you know why vbscript would wait for name manager to close to sendkeys?

T
0
 
dlmilleCommented:
Thomas, you have tried this in vbscript using directly the loop through Excel.Application.Names and it does not work?

Dave
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
nutschAuthor Commented:
Tried as it is, and I get a call rejected by callee error. Did you try and get the same? It seems that the name manager being opened stopped the execution of the script.

T
0
 
dlmilleCommented:
nutsch - The following code works for me.  See attached workbook I ran against.  There's a couple macros, one to list names, visibility and one to hide two of the names.  A few have bad references, of course the links won't work to linked files, etc.

Seems to work without a hitch....  Don't know what your workbook looks like, but give this a shot.

Code:  
 
Dim myName
	Dim oApp
	Dim oWB
	
	set oApp = CreateObject("Excel.Application")
	oApp.Displayalerts = False
	oApp.AskToUpdateLinks = False
	oApp.AlertBeforeOverwriting = False
	
	oApp.Workbooks.Open "C:\Book1.xlsm"
	
	set oWB = oApp.ActiveWorkbook
	oApp.Visible = True
	
	For Each myName in oWB.Names
		myName.Delete
	Next
	
	oWB.Save
	oWB.Close
	oApp.Quit
    set oApp = Nothing

Open in new window


See attached file (be sure to change the filename reference in the code, when you go against yours.  I used a .xlsm file as I wrote a couple scripts to list names, etc...)

Dave
Book1.xlsm
deleteAllNames-R1.vbs
0
 
nutschAuthor Commented:
I have attached my workbook in the initial question and it has some nasty looking names. Try and give it a whirl. I'm trying your code in parallel.

T
0
 
dlmilleCommented:
Your book1.xlsx uploaded unrepairable unreadable content.

Dave
0
 
RobSampsonCommented:
OK, try this.  I've shelled out to a second script to run the SendKeys parts.

Regards,

Rob.
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False

myExcelWorker.Workbooks.Open("C:\temp\scripts\excel\Book1.xlsx")

Set oWB =myExcelWorker.ActiveWorkbook
myExcelWorker.Visible=True

intNames = oWB.Names.Count

strTempScript = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "DeleteNamesTemp.vbs"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objScript = objFSO.CreateTextFile(strTempScript, True)
objScript.WriteLine "WScript.Sleep 5000"
objScript.WriteLine "Set WshShell = CreateObject(""WScript.Shell"")"
objScript.WriteLine "WshShell.SendKeys ""{TAB}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{TAB}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{TAB}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{DOWN}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "For i = 1 to " & intNames
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""%D"""
objScript.WriteLine "WScript.Sleep 250"
objScript.WriteLine "WshShell.SendKeys ""{DOWN}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{UP}"""
objScript.WriteLine "Next"
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{ESC}"""
objScript.Close


Set WshShell = CreateObject("WScript.Shell")

WScript.Sleep 100
WshShell.SendKeys "^{F3}"
WScript.Sleep 200

WshShell.Run "wscript.exe """ & strTempScript & """", 1, True

objFSO.DeleteFile strTempScript, True

MsgBox "Finished"

'I need a first pass to make all names visible

'A second pass to delete all valid names

'A third pass to delete through the name manager the non-valid names

'myExcelWorker.ActiveWorkBook.Close True

'myExcelWorker.Quit
'set myExcelWorker = Nothing

Open in new window

0
 
dlmilleCommented:
nutsch - any luck?

Dave
0
 
RobSampsonCommented:
Mine works in Excel 2007. One odd thing I noticed is that the code *did* continue after the Name Manager was displayed, it's just that the SendKeys methods didn't run until after it was closed....very strange....

Anyway, my Excel 2007 was able to repair the workbook, which I have attached for others to test against.

Rob.
Book1.xlsx
0
 
nutschAuthor Commented:
Rob,

When I tried your script it went through half the names then lost the focus. Might have been a move I made on the mouse but here goes. When run on another file, it took out all names but 4. Works pretty good for me.

Final version including file selection is below.

Do you see a way I can launch the vbscript from vba and run it on the activeworkbook? I'm asking a separate question for that.

Thanks,

Thomas
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False

myExcelWorker.Workbooks.Open(GetFileName( "", "*.xlsx;*.xls;*.xlsxm"))

Set oWB =myExcelWorker.ActiveWorkbook
myExcelWorker.Visible=True

intNames = oWB.Names.Count

strTempScript = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "DeleteNamesTemp.vbs"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objScript = objFSO.CreateTextFile(strTempScript, True)
objScript.WriteLine "WScript.Sleep 5000"
objScript.WriteLine "Set WshShell = CreateObject(""WScript.Shell"")"
objScript.WriteLine "WshShell.SendKeys ""{TAB}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{TAB}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{TAB}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{DOWN}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "For i = 1 to " & intNames
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""%D"""
objScript.WriteLine "WScript.Sleep 250"
objScript.WriteLine "WshShell.SendKeys ""{DOWN}"""
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{UP}"""
objScript.WriteLine "Next"
objScript.WriteLine "WScript.Sleep 100"
objScript.WriteLine "WshShell.SendKeys ""{ESC}"""
objScript.Close


Set WshShell = CreateObject("WScript.Shell")

WScript.Sleep 100
WshShell.SendKeys "^{F3}"
WScript.Sleep 200

WshShell.Run "wscript.exe """ & strTempScript & """", 1, True

objFSO.DeleteFile strTempScript, True


Function GetFileName( myDir, myFilter )
' This function opens a File Open Dialog and returns the
' fully qualified path of the selected file as a string.
'
' Arguments:
' myDir is the initial directory; if no directory is
' specified "My Documents" is used;
' NOTE: this default requires the WScript.Shell
' object, and works only in WSH, not in HTAs!
' myFilter is the file type filter; format "File type description|*.ext"
' ALL arguments MUST get A value (use "" for defaults), OR otherwise you must
' use "On Error Resume Next" to prevent error messages.
'
' Dependencies:
' Requires NUSRMGRLib (nusrmgr.cpl), available in Windows XP and later.
' To use the default "My Documents" WScript.Shell is used, which isn't
' available in HTAs.
'
' Written by Rob van der Woude
' http://www.robvanderwoude.com

    ' Standard housekeeping
    Dim objDialog

    ' Create a dialog object
    Set objDialog = CreateObject( "UserAccounts.CommonDialog" )

    ' Check arguments and use defaults when necessary
    If myDir = "" Then
        ' Default initial folder is "My Documents"
        objDialog.InitialDir = CreateObject( "WScript.Shell" ).SpecialFolders( "MyDocuments" )
    Else
        ' Use the specified initial folder
        objDialog.InitialDir = myDir
    End If
    If myFilter = "" Then
        ' Default file filter is "All files"
        objDialog.Filter = "All files|*.*"
    Else
        ' Use the specified file filter
        objDialog.Filter = myFilter
    End If

    ' Open the dialog and return the selected file name
    If objDialog.ShowOpen Then
        GetFileName = objDialog.FileName
    Else
        GetFileName = ""
    End If
End Function

Open in new window

0
 
nutschAuthor Commented:
Thank you Rob for your expert answer.

Thanks David for your efforts.

Thomas
0
 
nutschAuthor Commented:
David, for info, your code works fine for regular names, but some of the names I have are not responding favorably to a nm.delete command.

T
0
 
dlmilleCommented:
T - it would be appreciated if you'd upload an uncorrupt version, that way I'd see what the issue really was...

Sendkeys should be a last resort option.

Cheers,

Dave
0
 
nutschAuthor Commented:
The version Rob uploaded works.
0
 
dlmilleCommented:
I understand that.
0
 
dlmilleCommented:
I can't help in the related question if I can't open your original file which is corrupt.  Good luck.

Dave
0
 
dlmilleCommented:
Apparently Rob uploaded a repaired version of the file you uploaded.  I was able to delete all the names.  I guess I don't get what the problem is, so without more info, I'll leave the related question to others.

Cheers,

Dave
0
 
dlmilleCommented:
Sorry for the SPAM messages - should have put this all in one - here's your "repaired" file with no names.
Book1.xlsx
0
 
nutschAuthor Commented:
I'll try and reload the file when I'm back at work. Monday latest.

Thanks Dave
0
 
nutschAuthor Commented:
Here is a sample file

T
Sample.xls
0
 
dlmilleCommented:
Thanks, T

It appears that either Excel 2003 or earlier (if rule was allowed) or some other app generated this file, thus bypassing the collection rules for the names class.  I can see without similar access, using SendKeys appears to be the only option.  I think Lotus was fond of having spaces in named ranges...

Dave
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now