PDA

View Full Version : remove corrupt excel names - programmatically?


brettdj
05-16-2008, 05:18 AM
tricky one

I have a spreadsheet from hell with 6000 useless range names that I want to cull. Normally a trivaila exercise for a number of utilities such as name manager

In this case though many of the names are corrupt. There is a solution at
http://support.microsoft.com/kb/555127 (http://support.microsoft.com/kb/555127) that prompts the user to enter a usuable name (Name Manager uses a similar approach), but this is piecemeal. I would prefer not changing 1000+ corrupt names one by one.

Regards

Dave
Bob Phillips
05-16-2008, 05:44 AM
Dave,

In what way are they corrupt? Is it non-printables, a defined set of such?
brettdj
05-16-2008, 06:29 AM
Dave,

In what way are they corrupt? Is it non-printables, a defined set of such?


Bob,

Some names are like
'Jun-2007PF'!?0O

Others like
'A+BC (JUNE)'!y6

Cheers

Dave
Jan Karel Pieterse
05-16-2008, 06:39 AM
Hi Brett,

I have a tool that does it all for you, but it isn't free.
Bob Phillips
05-16-2008, 06:44 AM
Can you post a workbook, and I will see if I can do anything.
rory
05-16-2008, 06:56 AM
Kevin wrote some code on EE to automate this using SendKeys, though I never had occasion to test it. I could dig out the link if you're interested?
brettdj
05-16-2008, 07:43 AM
Bob,

I'll try Rory's link first, else I'll post a sanitised workbook. Did you get my PM re the website files?

Rory,

How's it going? I hadn't realised you were so prolific here as well as at EE where I see that not even Kevin and Brad are keeping up with you. Pls send me through Kevin's link.

The weather in the UK is under-rated, its been perfect since we arrived late last week, other than today

Regards

Dave
rory
05-16-2008, 07:54 AM
Hi Dave,
I reckon you must have brought the weather across with you, so thanks! :)
Kevin's code is here (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_22639501.html). I assume it works. ;)
I wouldn't say I'm prolific here, certainly not compared to certain others... I might catch Kevin and Brad in about 10 years at EE, but now that I've joined another couple of forums, it's even more unlikely!!
Rory
brettdj
05-16-2008, 08:48 AM
I'm getting Name Conflicts (name cannot resemeble a reference) on Kevin's code. I'll dig deeper into the VBA to see if I can Sendkeys this

Cheers

Dave