删除命名范围时出现运行时错误 [英] Run-time error when deleting a named range

问题描述

我不明白为什么在尝试删除命名范围时会不断收到运行时错误'1004':应用程序定义的错误或对象定义的错误"消息.

I don't understand why I keep getting a "Run-time error '1004': Application-defined or Object-defined error" message when trying to delete a named range.

这是用于从.csv文件导入数据并将该范围命名为历史记录"的代码.

Here's the code used to import data from a .csv file and name the range as "history"

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\<user name>\Downloads\history.csv", Destination:=Range(destCell))
    .Name = "history"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 3
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(3, 1, 2, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

这是用于删除历史记录"命名范围的代码.请注意，紧接其功能的那一行恰好可以找到命名范围.只是不会删除名称.

Here's the code used to delete the "history" named range. Note that the line immediately before it functions just fine to find the named range. It just doesn't won't delete the name.

Application.Goto Reference:="history"
ActiveWorkbook.Names("history").Delete

推荐答案

答案:问题是工作簿使用工作表名称作为命名范围的Name属性的一部分.具体来说，它使用的是history！history作为名称.

Answer: The problem was that the Workbook was using a worksheet name as part of the Name attribute of the named range. Specifically it was using history!history for the name.

故障排除方法:我使用了以下代码，该代码已发布到

Method of Troubleshooting: I used the following code that had been posted to a similar question at http://www.ozgrid.com/forum/showthread.php?t=49079&page=2

Dim nameRng As Name 
Dim varVal As Variant 
On Error Resume Next 
For Each nameRng In ActiveWorkbook.Names 
    varVal = Range(nameRng.Name).Value 
    nameRng.Delete 
Next

VBA编辑器中的本地窗口"显示该变量的nameRng.Name是字符串"history！history".

The Locals Window in the VBA Editor revealed that the nameRng.Name for this variable was the string "history!history".

修订后的代码:我删除了Application.Goto Reference:="history"行，因为它本质上是非功能性的代码行(类似于Select动作)，并保留为删除导入范围名称的代码:

Revised code: I removed the Application.Goto Reference:="history" line since it was essentially a non-functional line of code (similar to a Select action) and was left with this as the code to delete the Name of the Imported range:

ActiveWorkbook.Names("history!history").Delete

平台:我在Windows 7 Professional上使用Excel 2013

Platform: I was using Excel 2013 on Windows 7 Professional

这篇关于删除命名范围时出现运行时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！