Error while importing a text file and deleting the Range name that start with

2018-06-18 13:33:10

I was trying to import the text file(whose content is pasted here) in excel 2010.

Sample
0.000336179308182416 0.0003623072328649 0.000330580466347818 0.000328403140930966 0.000326536860231208 0.000316583362896417 0.000331824654826285 0.000376926431150615 0.000359196764802228 0.00035110955014428 0.000340222914920751 0.00038594678488213 0.000374438054920212 0.00038719097133022 0.000356708390252258 0.000355464204180755 0.000362307232454562 0.000408031102693052 0.00042793809249897 0.000361685139135481 =AVERAGE(B2:U2) =MEDIAN(B2:U2) =STDEV.P(B2:U2) 0.000317516503116533 0.000324670578360742 0.000343644429001611 0.00031720545739935 0.000316272316168398 0.000322182204319699 0.000319071737757159 =AVERAGE(B2:AE2) =MEDIAN(B2:AE2) =STDEV.P(B2:AE2)

The import worked properly. After the text file is opened in excel, I wrote the following VBA code to delete the range names that start with _.

Private Sub Workbook_Open()
 For Each n In ActiveWorkbook.Names
  If Mid$(n.Name, 1, 1) = "_" Then
    MsgBox ("The file being deleted is " + n.Name)
     n.Delete
  End If
 Next n
End Sub

and saved the file. Now again I opened this saved xlsm file and I have the msg displayed "The file being deleted is _xlfn.STDEV.P".

Even though I have an STDEV.P function specified in my text file,I dont know from where this range name has been added to the ActiveWorkBook.Names collection. And the subsequent delete operation is also throwing an error,as the range name is a excel funtion name. Please help me to rectify this issue.

Range names that start with _xlfn is created by excel, so any attempt to delete these range names will throw error. So I have avoided the deletion of range names that start with _xlfn.

For Each n In ActiveWorkbook.Names
    If Mid$(n.Name, 1, 5) = "_xlfn" Then
        continue
    Else  
        n.Delete
    End If
Next n

链接地址: http://www.djcxy.com/p/52346.html

上一篇: 使用OpenXML创建Excel文件，无法读取的内容

下一篇: 导入文本文件并删除以范围名称开头的错误