有机会在Excel(2007/2010)中删除以编程方式损坏的远程名称(带空格) [英] Any chance to delete programatically corrupt ranged names (with spaces) in Excel (2007/2010)

问题描述

有什么方法可以重命名或删除删除损坏的远程名称，例如空格.在Powershell的帮助下Excel文件中的"ACVCutoff_4 1"吗?我被卡住了.重命名或删除名称失败.

Is there any way to rename or/and delete corrupted ranged names, which include spaces, e.g. "ACVCutoff_4 1" in Excel file with help of powershell? I got stuck on this. Renaming or deleting names fails.

$Excel = New-Object -Com Excel.Application
$wbTo = $Excel.workbooks.open($path)
foreach($name in $wbTo.Names)
{
    $name.Name = $tt -replace '\s+', '_' # name is not affected
    $name.delete() # returns an error Exception calling "Delete" with "0" argument(s): "Exception from HRESULT: 0x800A03EC"
}

我尝试过的事情:

相关问题

1. 包括名称管理器加载项不能解决问题.
2. 检查R1C1参考样式"(检查链接)-如何通过编写代码来实现?

还有其他建议吗?多谢您的帮助

Any other suggestions? I would appreciate your help

我正在使用Powershell v2和Excel 2007/2010.

I am using powershell v2 and Excel 2007/2010.

该文件的名称损坏: XlsWithBrokenNames

克里斯·尼尔森(Chris Neilsen)的解决方案:

SOLUTION BY Chris Neilsen for those who are also stuck on this:

#$wbTo - is workbook with corrupt names
$tempFilePath = $path + "temp.xlsm"
$goodFilePath = $path + "cleanFile.xls"
$wbTo.SaveAs($tempFilePath,52) #save as xlsm
$wbTemp = $Excel.workbooks.open($tempFilePath) # open target
# delete names here
$wbTemp.SaveAs($goodFilePath,56) #save as xls

推荐答案

对我尝试过的损坏名称的所有直接操作都失败了！

All direct manipulation of the corrupt names i have tried has failed!

但是，这是一个可能的选择:
SaveAs您的工作簿为.xlsm您应该看到一个对话框，提示您输入无效的名称，并带有一个重命名选项和一个Ok to All按钮.保存后，关闭并重新打开文件Save As和.xls，您应该会很好

But, here's a possible alternative:
SaveAs your workbook as a .xlsm You should get a dialog complaining about invalid names, with a option to rename and a Ok to All button. Once saved, close and reopen the file, Save As an .xls and you should be good to go

这篇关于有机会在Excel(2007/2010)中删除以编程方式损坏的远程名称(带空格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！