shacho
asked on
Can't Delete Broken "Print_Area" Name
I am working on a project that cleans garbage out of old, abused spreadsheets.
It's almost done but there are a few outstanding problems to tackle. This is one
of them. This book contains an invalid name "Print_Area", which is global, not
a sheet name, and which I cannot delete programatically. I can remove it manually
using the Define Names dialog, but not with VBA. Is there a workaround?
Mike
PAProblem.xls
It's almost done but there are a few outstanding problems to tackle. This is one
of them. This book contains an invalid name "Print_Area", which is global, not
a sheet name, and which I cannot delete programatically. I can remove it manually
using the Define Names dialog, but not with VBA. Is there a workaround?
Mike
PAProblem.xls
ASKER
Have a look at the code in the file.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I suspect that "Print_Area" is a protected name name. Strange that it doesn't give an error though. I may go the SendKeys route. I've already had to use it twice in this project anyway. Incidentally, I discovered through experimentation today that although SendKeys is notoriously unreliable, it's more reliable if it's invoked from outside Excel, or at least that's been my observation on this project. I wrote a VBScript that lives on a hidden spreadsheet in the project. When it's needed, it get's spit out into a textfile and saved in a temporary folder, then called by the shell. Sounds like a lot of work, and it was, but it's working well.
Mike
Mike
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>> Have a look at the code in the file
Ah!, I saw the graphic and thought that was all there was!
Looks like fun!
Chris
Ah!, I saw the graphic and thought that was all there was!
Looks like fun!
Chris
Using sendkeys then the following works as long as it called from excel itself
Chris
Chris
Sub TryToDeleteNames()
Dim nm As Name
For Each nm In ThisWorkbook.Names
nm.Delete
Next
If Not ThisWorkbook.Names("print_area") Is Nothing Then
ThisWorkbook.Activate
With ThisWorkbook.Application
.SendKeys ("%i")
.SendKeys ("n")
.SendKeys ("d")
.SendKeys ("Print_Area")
.SendKeys ("%D")
.SendKeys ("{ENTER}")
End With
End If
End SubSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FWIW, it works fine in 2007 I tested it at the time therein but the comment is agreed in re using teh dialog..
Chris
Chris
ASKER
Interesting. I thought the menus were gone in 2007.
The short cut keys still work which is what is being used but the ability to create menus except via the ribbon is gone.
Chris
Chris
ASKER
That's good news. I just assumed that short keys wouldn't work since the accelerators will have vanished with the menus. Good to know.
The accelerators should actually reappear when you press the Alt key.
ASKER
Also good to know.
Hi all,
I realise this is an old post, but figure I may as well post here, since it answers my question (although not what I wanted to hear). I have the same issue, in which the "global" Print_Area name (of which there is one for each sheet, but not named "locally") cannot be deleted by code. As the author stated, deleting the name from the inbuilt Name Manager (Insert --> Name --> Define) does work. Annoyingly though, using
ActiveWorkbook.Names("Prin
does not work, and neither does
ActiveWorkbook.Sheets(1).P
ActiveWorkbook.Names("Prin
But, it does appear to work, but then reverse itself. To illustrate, if you use the following:
For Each objName In Names
MsgBox "Name before deleting: " & objName.Name
objName.Delete
MsgBox "Name after deleting: " & objName.Name
Next
MsgBox Names(1).Name
The first message is displayed, but the second (after deleting) throws an error of "Run time error 424: Object Required", suggesting that it does actually delete, but if you comment that line out during the debug, you get the last message of the Name, which means it's back again! But on closer inspection, what it is doing is changing the "Reference" of the name. Before the .Delete, the reference is local, as in Sheet1!$A$1:$C$5, but after the .Delete fires, it becomes just $A$1:$C$5, which means the reference has been globalised, and the name cannot be deleted.
Also, as has been mentioned, JKP's Name Manager isn't able to delete it, and it specifies the Name as being Global. So I tried to "localise" it, by using:
ActiveWorkbook.Names("Prin
which did seem to duplicate it, according to JKP's Name Manager, but the original still remained.
So, ultimately, the key thing is that the built in Name Manager does something "special" when deleting the Name manually, but no-one knows what that is....if only it could be replicated!!!
Oh well, I guess I'll have to resort to SendKeys....
Regards,
Rob.
I realise this is an old post, but figure I may as well post here, since it answers my question (although not what I wanted to hear). I have the same issue, in which the "global" Print_Area name (of which there is one for each sheet, but not named "locally") cannot be deleted by code. As the author stated, deleting the name from the inbuilt Name Manager (Insert --> Name --> Define) does work. Annoyingly though, using
ActiveWorkbook.Names("Prin
does not work, and neither does
ActiveWorkbook.Sheets(1).P
ActiveWorkbook.Names("Prin
But, it does appear to work, but then reverse itself. To illustrate, if you use the following:
For Each objName In Names
MsgBox "Name before deleting: " & objName.Name
objName.Delete
MsgBox "Name after deleting: " & objName.Name
Next
MsgBox Names(1).Name
The first message is displayed, but the second (after deleting) throws an error of "Run time error 424: Object Required", suggesting that it does actually delete, but if you comment that line out during the debug, you get the last message of the Name, which means it's back again! But on closer inspection, what it is doing is changing the "Reference" of the name. Before the .Delete, the reference is local, as in Sheet1!$A$1:$C$5, but after the .Delete fires, it becomes just $A$1:$C$5, which means the reference has been globalised, and the name cannot be deleted.
Also, as has been mentioned, JKP's Name Manager isn't able to delete it, and it specifies the Name as being Global. So I tried to "localise" it, by using:
ActiveWorkbook.Names("Prin
which did seem to duplicate it, according to JKP's Name Manager, but the original still remained.
So, ultimately, the key thing is that the built in Name Manager does something "special" when deleting the Name manually, but no-one knows what that is....if only it could be replicated!!!
Oh well, I guess I'll have to resort to SendKeys....
Regards,
Rob.
ASKER
Glad it helped. Hopefully it will be fixed in future versions. Or maybe it doesn't matter. Chances are the faulty name is in a workbook that was created in an older version of Excel, or contains a sheet that was merged in from a workbook that was created in an older version. I'm guessing it would be hard to create a workbook with this kind of problem in 2007 or newer.
Yes, I agree that it's been a bug in older versions, and hopefully it's been fixed for current and future versions. Anyway, I have come up with a method that can be use without SendKeys, which involves opening the problem workbook in Office 2000 or 2003, and copying each sheet in the workbook to a new workbook, which doesn't have those global Print_Area names. Then I also make sure that any cell references are not created as links to the original workbook, and finally, save the new workbook in another folder. All of this is automated with the following VBScript. Hopefully others can find it useful. The only thing, I guess, that this will remove, is the VBA macros that might exist in the original workbook, but in my case, these workbooks didn't have VBA code.
Regards,
Rob.
Regards,
Rob.
strSourceFolder = "C:\Temp\Rename\Test"
strDestinationFolder = "C:\Temp\Rename\Test\PrintAreaFree"
If Right(strDestinationFolder, 1) <> "\" Then strDestinationFolder = strDestinationFolder & "\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const xlPart = 2
Const xlByRows = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
For Each objFile In objFSO.GetFolder(strSourceFolder).Files
If Right(LCase(objFile.Name), 4) = ".xls" Then
Set objWB = objExcel.Workbooks.Open(objFile.Path, False, False)
Set objNewWB = objExcel.Workbooks.Add
While objNewWB.Sheets.Count > 1
objExcel.DisplayAlerts = False
objNewWB.Sheets(objNewWB.Sheets.Count).Delete
objExcel.DisplayAlerts = True
Wend
For intSheet = 1 To objWB.Sheets.Count
If intSheet > 1 Then objNewWB.Sheets.Add , objNewWB.Sheets(objNewWB.Sheets.Count)
objNewWB.Sheets(intSheet).Name = objWB.Sheets(intSheet).Name
objWB.Sheets(intSheet).Cells.Copy objNewWB.Sheets(intSheet).Range("A1")
Next
For intSheet = 1 To objNewWB.Sheets.Count
objNewWB.Sheets(intSheet).Cells.Replace "[" & objWB.Name & "]", "", xlPart, xlByRows, False
Next
strName = objWB.Name
objWB.Close False
objExcel.DisplayAlerts = False
objNewWB.Close True, strDestinationFolder & strName
objExcel.DisplayAlerts = True
End If
Next
objExcel.Quit
ASKER
Rob,
Thanks for sharing. I've used brute force rebuilding like this a few times when all else fails. It does the trick, but there are a few pitfalls you should look out for:
1) Sometimes formulas as they are shown in the cell don't mean the same thing from one workbook to the next depending on the state of the application. It looks like you may be partially addressing that with .Cells.Replace "[" & objWB.Name & "], but that might not clear it all up. If you open the new workbook in a new instance of Excel (or just close all instances of Excel and open it normally) and the formulas are doing what you expect and look as you expect your problem is probably solved.
2) This method might not copy over all your names, if you have any. Especially names that are not tied to worksheet ranges.
3) Pivot tables wont copy over this way (I think), and query tables definitely won't. Lists might not either.
4) Any drawing shapes you have tied to code will need to be re-tied to the code in your new workbook. You can address this with code using the OnAction property.
5) Sheet page formatting will be lost.
6) You method might miss or trip on hidden sheets.
7) This won't work if the source book has protection.
8) Any event handlers you have in ThisWorkbook or your worksheet modules will need to be moved over by hand, unless you want to try to script the VBE itself (which can be done).
There are probably others, but those are the ones I can remember off the top of my head. I hope that helps. One other thing - If your doing an operation like this, turn off calculation in your Excel instance as soon as you create it, then turn it back on just before you save the new workbook. It will run much more quickly and may save you from a crash:
objExcel.Calculation = xlCalculationManual
'YOUR CODE......
objExcel.Calculation = xlCalculationAutomatic
Mike
PPS - You might be able to save yourself a lot of trouble by copying the entire lineup of sheets into the new workbook in one go - as sheets (not ranges). This would sidestep most of the above problems. I think the code is something like this:
objWB.Sheets(Array(Sheet1.
Thanks for sharing. I've used brute force rebuilding like this a few times when all else fails. It does the trick, but there are a few pitfalls you should look out for:
1) Sometimes formulas as they are shown in the cell don't mean the same thing from one workbook to the next depending on the state of the application. It looks like you may be partially addressing that with .Cells.Replace "[" & objWB.Name & "], but that might not clear it all up. If you open the new workbook in a new instance of Excel (or just close all instances of Excel and open it normally) and the formulas are doing what you expect and look as you expect your problem is probably solved.
2) This method might not copy over all your names, if you have any. Especially names that are not tied to worksheet ranges.
3) Pivot tables wont copy over this way (I think), and query tables definitely won't. Lists might not either.
4) Any drawing shapes you have tied to code will need to be re-tied to the code in your new workbook. You can address this with code using the OnAction property.
5) Sheet page formatting will be lost.
6) You method might miss or trip on hidden sheets.
7) This won't work if the source book has protection.
8) Any event handlers you have in ThisWorkbook or your worksheet modules will need to be moved over by hand, unless you want to try to script the VBE itself (which can be done).
There are probably others, but those are the ones I can remember off the top of my head. I hope that helps. One other thing - If your doing an operation like this, turn off calculation in your Excel instance as soon as you create it, then turn it back on just before you save the new workbook. It will run much more quickly and may save you from a crash:
objExcel.Calculation = xlCalculationManual
'YOUR CODE......
objExcel.Calculation = xlCalculationAutomatic
Mike
PPS - You might be able to save yourself a lot of trouble by copying the entire lineup of sheets into the new workbook in one go - as sheets (not ranges). This would sidestep most of the above problems. I think the code is something like this:
objWB.Sheets(Array(Sheet1.
Hey Mike,
Thanks alot for your helpful info. I will have to examine my workbooks for the caveats you have mentioned, although I think they're pretty straight forward data sheets, so I should be OK.
I forgot about Calculation, and also ScreenUpdating, so I'll turn those off, and add the calculation constants:
Const xlCalculationManual = -4135
Const xlCalculationAutomatic = -4105
I did try to overcome the formula issue with the Replace, which has worked for any links that had been reverted to the original workbook (I assume because I didn't have the proper referenced sheet in the new workbook yet).
Other named ranges don't matter to these workbooks, as they're purely data only. However, you could script the identification and creation of those if required.
I think this would also pick up hidden sheets, because I'm not distinguishing between hidden or visible sheets, but the sheet would be visible in the new workbook since I haven't made that test.
I know VBA wouldn't be copied, which could be a problem, but in my case there isn't any, so that's good. Copying that over would required manually allowing VBA access to the project, and then using the Module copy methods available.
I did actually try copying sheet object itself, but interestingly (or annoyingly) this also copied the associated Print_Area name into the new workbook for that sheet, despite the fact the Print_Area was still thought of as global. So it was somehow tied to the sheet, but because I had multiple sheets in the problem workbooks, they were not technically "unique". So, seeing as the Print_Area was still copied with the sheet, and the .Delete method still didn't work in VBA, I would have had to use SendKeys with that as well.
Thanks for the insight. I'll let you know if I make any improvements to this.
Regards,
Rob.
Thanks alot for your helpful info. I will have to examine my workbooks for the caveats you have mentioned, although I think they're pretty straight forward data sheets, so I should be OK.
I forgot about Calculation, and also ScreenUpdating, so I'll turn those off, and add the calculation constants:
Const xlCalculationManual = -4135
Const xlCalculationAutomatic = -4105
I did try to overcome the formula issue with the Replace, which has worked for any links that had been reverted to the original workbook (I assume because I didn't have the proper referenced sheet in the new workbook yet).
Other named ranges don't matter to these workbooks, as they're purely data only. However, you could script the identification and creation of those if required.
I think this would also pick up hidden sheets, because I'm not distinguishing between hidden or visible sheets, but the sheet would be visible in the new workbook since I haven't made that test.
I know VBA wouldn't be copied, which could be a problem, but in my case there isn't any, so that's good. Copying that over would required manually allowing VBA access to the project, and then using the Module copy methods available.
I did actually try copying sheet object itself, but interestingly (or annoyingly) this also copied the associated Print_Area name into the new workbook for that sheet, despite the fact the Print_Area was still thought of as global. So it was somehow tied to the sheet, but because I had multiple sheets in the problem workbooks, they were not technically "unique". So, seeing as the Print_Area was still copied with the sheet, and the .Delete method still didn't work in VBA, I would have had to use SendKeys with that as well.
Thanks for the insight. I'll let you know if I make any improvements to this.
Regards,
Rob.
ASKER
>interestingly (or annoyingly) this also copied the associated Print_Area name
Man, that's obnoxious.
>I did try to overcome the formula issue with the Replace
The trick I use is to convert all formulas to strings by replacing every "=" in the entire workbook with "!!!!".
Then I do the copy and paste, and once all is in, I replace the "!!!!"s with "=".
Cheers,
Mike
OK, how about this? Shacho, I'm not really familiar with older versions of Excel, but if you know of any possible pitfalls in this approach, sing out.
My approach now involves a two stage process:
1) On a machine with Office 2003 or earlier, use a script to open all XLS files and Save As back to an Excel 95 workbook format.
2) On a machine with Office 2007 or greater, use a script to open all Excel 95 XLS files and Save As to the Excel 2007 XLSX format.
One drawback that I can see straight away, is that VBA will be lost because I am hardcoding the XLSX file extension, but I guess I could add the constant for a macro-enabled workbook, and use the XLSM extension if I wanted to.
Other than that, it seems to work pretty well, even though it leaves a single instance of a Print_Area name, that has a null reference.
Here is the code for Step 1.
Regards,
Rob.
My approach now involves a two stage process:
1) On a machine with Office 2003 or earlier, use a script to open all XLS files and Save As back to an Excel 95 workbook format.
2) On a machine with Office 2007 or greater, use a script to open all Excel 95 XLS files and Save As to the Excel 2007 XLSX format.
One drawback that I can see straight away, is that VBA will be lost because I am hardcoding the XLSX file extension, but I guess I could add the constant for a macro-enabled workbook, and use the XLSM extension if I wanted to.
Other than that, it seems to work pretty well, even though it leaves a single instance of a Print_Area name, that has a null reference.
Here is the code for Step 1.
Regards,
Rob.
strSourceFolder = "C:\Temp\Rename\Test"
strDestinationFolder = "C:\Temp\Rename\Test\Office95Versions"
If Right(strDestinationFolder, 1) <> "\" Then strDestinationFolder = strDestinationFolder & "\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const xlCalculationManual = -4135
Const xlCalculationAutomatic = -4105
Const xlExcel5 = 39
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
If objExcel.Version < 12 Then
objExcel.EnableEvents = False
objExcel.ScreenUpdating = False
For Each objFile In objFSO.GetFolder(strSourceFolder).Files
If Right(LCase(objFile.Name), 4) = ".xls" Then
Set objWB = objExcel.Workbooks.Open(objFile.Path, False, False)
objExcel.Calculation = xlCalculationManual
strName = objWB.Name
objExcel.DisplayAlerts = False
objWB.SaveAs strDestinationFolder & strName, xlExcel5
objExcel.DisplayAlerts = True
objExcel.Calculation = xlCalculationAutomatic
objWB.Close False
End If
Next
objExcel.ScreenUpdating = True
objExcel.EnableEvents = True
Else
MsgBox "You cannot open these workbooks in Excel 2007. Please run this script on a machine with Office 2003 or earlier installed."
End If
objExcel.Quit
And here's the code for step 2.
Regards,
Rob.
Regards,
Rob.
strSourceFolder = "C:\Temp\Rename\Test\Office95Versions"
strDestinationFolder = "C:\Temp\Rename\Test\Office95Versions\Office2007Versions"
If Right(strDestinationFolder, 1) <> "\" Then strDestinationFolder = strDestinationFolder & "\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const xlCalculationManual = -4135
Const xlCalculationAutomatic = -4105
Const xlOpenXMLWorkbook = 51
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
If objExcel.Version > 11 Then
objExcel.EnableEvents = False
objExcel.ScreenUpdating = False
For Each objFile In objFSO.GetFolder(strSourceFolder).Files
If Right(LCase(objFile.Name), 4) = ".xls" Then
Set objWB = objExcel.Workbooks.Open(objFile.Path, False, False)
objExcel.Calculation = xlCalculationManual
strName = objWB.Name
objExcel.DisplayAlerts = False
objWB.SaveAs strDestinationFolder & strName & "x", xlOpenXMLWorkbook
objExcel.DisplayAlerts = True
objExcel.Calculation = xlCalculationAutomatic
objWB.Close False
End If
Next
objExcel.ScreenUpdating = True
objExcel.EnableEvents = True
Else
MsgBox "You cannot convert these workbooks to Excel 2007. Please run this script on a machine with Office 2007 or greater installed."
End If
objExcel.Quit
ASKER
Hey Rob,
Interesting approach. A couple things jump out at me:
1) For your 2003 version, what's the purpose of saving to an older format? Just to "wash out" the funkyness? If that works, great, but you run the risk of destroying everything in your newer version workbook that isn't supported by the older format.
2) For your 2007 version if all you want to do is convert to the new format, you might be able to just use SaveAs with no parameters beyond the filepath. Just a guess. Anyway, you can probably preserve the code by using "xlsm", not "xlsx", and xlOpenXMLWorkbookMacroEnab
Cheers,
Mike
Interesting approach. A couple things jump out at me:
1) For your 2003 version, what's the purpose of saving to an older format? Just to "wash out" the funkyness? If that works, great, but you run the risk of destroying everything in your newer version workbook that isn't supported by the older format.
2) For your 2007 version if all you want to do is convert to the new format, you might be able to just use SaveAs with no parameters beyond the filepath. Just a guess. Anyway, you can probably preserve the code by using "xlsm", not "xlsx", and xlOpenXMLWorkbookMacroEnab
Cheers,
Mike
>> what's the purpose of saving to an older format?
A colleague of mine discovered that if it was saved as Excel 95, then only one Print_Area existed, so when it came to then opening that in Excel 2007, it didn't have a hissy fit. So this is what I've replicated. Yes, it may lose some functionality, but I might use both approaches, since they're now automated, and get the guys who own the files to look over them a bit more carefully.
>> you might be able to just use SaveAs with no parameters beyond the filepath
That was my first guess, and I also tried xlNormal, but both resulted in keeping the current file format, which was annoying.
Anyway, I think that's about it for me on this one. It was an interesting problem, but I think the solutions here are about the best we can do. Thanks a lot for sharing your thoughts.
Regards,
Rob.
P.S. Given your obvious knowledge of Excel, have you thought about joing the Experts here in volunteering to share your skills and help others? I think you'd do quite well.
A colleague of mine discovered that if it was saved as Excel 95, then only one Print_Area existed, so when it came to then opening that in Excel 2007, it didn't have a hissy fit. So this is what I've replicated. Yes, it may lose some functionality, but I might use both approaches, since they're now automated, and get the guys who own the files to look over them a bit more carefully.
>> you might be able to just use SaveAs with no parameters beyond the filepath
That was my first guess, and I also tried xlNormal, but both resulted in keeping the current file format, which was annoying.
Anyway, I think that's about it for me on this one. It was an interesting problem, but I think the solutions here are about the best we can do. Thanks a lot for sharing your thoughts.
Regards,
Rob.
P.S. Given your obvious knowledge of Excel, have you thought about joing the Experts here in volunteering to share your skills and help others? I think you'd do quite well.
Try not specifying the extension, just the fileformat
ASKER
Hey Rob,
Yeah, it's nice to collaborate. Honestly, this site has been the most important resource for me over the last 8 or so years. I could not have reached my level of knowledge in several technologies without it. You're right - I could contribute as an Excel expert at this point but it's really hard to find the time. I'd love to have the outlet to share my knowledge and to keep current but I can't justify the time expense. It's wonderful that so many people do contribute and I'm grateful. Maybe in a couple years.
Cheers,
Mike
Yeah, it's nice to collaborate. Honestly, this site has been the most important resource for me over the last 8 or so years. I could not have reached my level of knowledge in several technologies without it. You're right - I could contribute as an Excel expert at this point but it's really hard to find the time. I'd love to have the outlet to share my knowledge and to keep current but I can't justify the time expense. It's wonderful that so many people do contribute and I'm grateful. Maybe in a couple years.
Cheers,
Mike
thisworkbook.names("print_
Chris