excel打开csv文件并进行 操作代码小结-注意 区分thiworkbook与activeworkbook

第一篇：excel打开csv文件并进行 操作代码小结-注意 区分thiworkbook与activeworkbook

注意1：如下这两句意思相同，都是判断sheet1的E列中大于maxv * r的单元格个数。

nv” &maxv * r)nv” &Cstr(maxv * r))注意2:在vba中sheets.cout默认是activeworkbook.sheets.cout，而不是thisworkbook.sheets.count，同理，sheets(i)默认是activeworbook.sheets(i)，而不是thisworkbook.sheets(i)。当前thisworkbook指的是当前vba代码所在的workbook，thisworkbook不一定是activeworkbook，activeworkbook指的是人工或者vba命令最新打开或者编辑的workbook。一定要注意这一点。

主函数

Public Sub abcmodified()

Dim MyFileAs String

Dim nam, nam1 As String

Dim num As Integer

'vv = Application.InputBox(prompt:=“请输入取值范围（0~1）”, Type:=1)MyFile = Dir(ThisWorkbook.Path& “*.csv”)

'MyFile = Dir(“C:UsersliyangtjuDesktop新建文件夹” & “*.csv”)

'读入文件夹中的第一个.csv文件

Do While MyFile<> “"

Workbooks.Open Filename:=ThisWorkbook.Path& ”“ &MyFile Debug.PrintMyFile

flag = 0

For i = 1 To ThisWorkbook.Sheets.Count '注意这里如果是Sheets.Count那么寻找的就是当前活动的workbook的sheets的数目

If Left(MyFile, Len(MyFile)4)

Sheets(s).Name = strname

Sheets(s).Cells(1, ”G“).NumberFormatLocal = ”0.00%“

Sheets(s).Cells(1, ”G“).HorizontalAlignment = xlCenter

'Sheets(s).Activate

Sheets(s).Rows(”1:1“).Select

'Selection.AutoFilter Selection.AutoFilter Field:=7, Criteria1:=”<>“

Sheet1.Activate

Sheet1.Cells.Select Selection.ClearContents

kk:

Workbooks(MyFile).Close Savechanges:=True

MyFile = Dir

'第二次读入的时候不用写参数

Loop

'For i = 2 ToThisWorkbook.Sheets.Count 'Sheets(i).Cells(1, 2).Formula = ”=Sheet1!$B$1“

'Next i

End Sub

调用的函数

Public Sub UserDefinedFunction()ThisWorkbook.Activate

'有了这一句，使得thisworkbook成为activeworkbook Columns(”B:B“).Select '这一句全称默认是activeworkbook.activesheet.columns(”B:B“).select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns(”A:A“).Select Selection.TextToColumns Destination:=Range(”A1“), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns(”E:E“).Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns(”E:E“).Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns(”E:E“).Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Rows(”1:1“).Select Selection.AutoFilter ThisWorkbook.Sheets(”Sheet1“).AutoFilter.Sort.SortFields.Clear ThisWorkbook.Sheets(”Sheet1“).AutoFilter.Sort.SortFields.Add Key:=Range _

(”B1“), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal

With ActiveWorkbook.Worksheets(”Sheet1“).AutoFilter.Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With ActiveWorkbook.Worksheets(”Sheet1“).AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets(”Sheet1“).AutoFilter.Sort.SortFields.Add Key:=Range _

(”A1“), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal

With ActiveWorkbook.Worksheets(”Sheet1“).AutoFilter.Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With i = 2 Do If Sheet1.Cells(i, 1)= ”“ Then Exit Do End If k = 1 Do If Sheet1.Cells(i, ”A“)= Sheet1.Cells(i + 1, ”A“)Then k = k + 1 i = i + 1 Else i = i + 1 Exit Do End If Loop Sheet1.Cells(ik, ”D“), Sheet1.Cells(i1 For i = 3 To N If Sheet1.Cells(i, ”E“)= ”“ Then Sheet1.Cells(i, ”E“)= Sheet1.Cells(i2, ”D“), Cells(i + 2, ”D“)), Cells(i, ”E“))> 0 And Cells(i, ”E“)>= vv * maxv And Cells(i, ”E“)<=(vv + 0.1)* maxv Then Sheet1.Cells(i, ”F“).Formula = ”=D“ & i & ”/max(E2:E“ & N & ”)“ 'If Abs(Sheet1.Cells(i, ”E“)2, ”D“), Cells(i + 2, ”D“)), Cells(i, ”E“))> 0 And Cells(i, ”E“)>= vv * maxv And Cells(i, ”E“)<=(vv + 0.1)* maxv Then Sheet1.Cells(i, ”G“).Formula = ”=(F“ & i & ”-F“ & 1 & ”)^2“ 'If Abs(Sheet1.Cells(i, ”E“)-Sheet1.Cells(i, ”D“))/ Sheet1.Cells(i, ”E“)> 0.5 Then 'Sheet1.Cells(i, ”F“)= 1 'Else 'Sheet1.Cells(i, ”F“)= 0 'End If End If Next i

'Sheet1.Cells(1, 5)= WorksheetFunction.CountIf(Sheet1.Range(Cells(2, ”F“), Cells(N, ”F“)), 1)/ WorksheetFunction.Count(Sheet1.Range(Cells(2, ”F“), Cells(N, ”F“)))Sheet1.Cells(1, ”G“).Formula = ”=(sum(G2:G“ & N & ”)/count(G2:G“ & N & ”))^0.5/F1“ 'MsgBox ”执行完毕" End Sub