Solved

Delete names from excel workbook semi-manually - VBScript

Posted on 2011-09-14
22
860 Views
Last Modified: 2012-06-27
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
Comment
Question by:nutsch
  • 10
  • 9
  • 3
22 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 36540349
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
 
LVL 39

Author Comment

by:nutsch
ID: 36550460
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36551627
Thomas, you have tried this in vbscript using directly the loop through Excel.Application.Names and it does not work?

Dave
0
 
LVL 39

Author Comment

by:nutsch
ID: 36551766
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36552666
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
 
LVL 39

Author Comment

by:nutsch
ID: 36552668
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36552682
Your book1.xlsx uploaded unrepairable unreadable content.

Dave
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 36552815
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36552911
nutsch - any luck?

Dave
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36553093
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
 
LVL 39

Author Comment

by:nutsch
ID: 36553115
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Author Closing Comment

by:nutsch
ID: 36553117
Thank you Rob for your expert answer.

Thanks David for your efforts.

Thomas
0
 
LVL 39

Author Comment

by:nutsch
ID: 36553194
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36554833
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
 
LVL 39

Author Comment

by:nutsch
ID: 36554863
The version Rob uploaded works.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36554866
I understand that.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36554870
I can't help in the related question if I can't open your original file which is corrupt.  Good luck.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36554873
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36554876
Sorry for the SPAM messages - should have put this all in one - here's your "repaired" file with no names.
Book1.xlsx
0
 
LVL 39

Author Comment

by:nutsch
ID: 36554991
I'll try and reload the file when I'm back at work. Monday latest.

Thanks Dave
0
 
LVL 39

Author Comment

by:nutsch
ID: 36560986
Here is a sample file

T
Sample.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36563018
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now