Runtime error while deleting a named range
I don't understand why, when I try to delete a named range, I keep getting the error "1004: Application-defined error or object error."
Here's the code used to import data from CSV file and range name 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 remove the "history" of the named range. Note that the line immediately before it works great to find the named range. It just won't remove the name.
Application.Goto Reference:="history"
ActiveWorkbook.Names("history").Delete
source to share
Answer: The problem was that the workbook was using the worksheet name as part of the Name attribute of the named range. Specifically, history was used for history.
Troubleshooting Method: I used the following code which was posted for 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
The Locals window in the VBA editor showed that the nameRng.Name for this variable is the string "history! History".
Revised code: I removed the Application.Goto: = "history" reference as it was essentially a non-functional line of code (similar to the Select operation) and was left with that as the code to remove the imported range name:
ActiveWorkbook.Names("history!history").Delete
Platform: I used Excel 2013 for Windows 7 Professional
source to share