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

注意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 MyFile As String Dim nam, nam1 As String Dim num As Integer vv = Application.InputBox(prompt:=请输入取值范围（01）, Type:=1) MyFile = Dir(ThisWorkbook.Path & *.csv) MyFile = Dir(C:UsersliyangtjuDesktop新建文件夹 & *.csv) 读入文件夹中的第一个.csv文件 Do While MyFile Workbooks.Open Filename:=ThisWorkbook.Path & & MyFile Debug.Print MyFile flag = 0 For i = 1 To ThisWorkbook.Sheets.Count 注意这里如果是Sheets.Count那么寻找的就是当前活动的workbook的sheets的数目 If Left(MyFile, Len(MyFile) - 4) = ThisWorkbook.Sheets(i).Name Then MsgBox hello flag = 1 Exit For End If Next i If flag = 1 Then GoTo kk End If Application.DisplayAlerts = False Set arr = Workbooks(MyFile).Sheets(1).Cells 注意这里得有.cells如果没有.cells，那么复制的是sheet1中的vba代码内容 arr.Copy ThisWorkbook.Activate ThisWorkbook.Sheets(Sheet1).Activate ThisWorkbook.Sheets(Sheet1).Cells(1, 1).Select Selection.PasteSpecial Paste:=xlPasteAll UserDefinedFunction Sheet1.Cells.Copy num = ThisWorkbook.Sheets.Count ThisWorkbook.Sheets.Add After:=ThisWorkbook.Sheets(num) 在当前所有所有sheet中最后一个sheet后新建一个sheet s = ThisWorkbook.Sheets.Count ThisWorkbook.Sheets(s).Activate ThisWorkbook.Sheets(s).Cells(1, 1).Select Selection.PasteSpecial Paste:=xlPasteAll strname = 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 To ThisWorkbook.Sheets.Count Sheets(i).Cells(1, 2).Formula = =Sheet1!$B$1 Next i End Sub 调用的函数Public Sub UserDefinedFunction()ThisWorkbook.Activate 有了这一句，使得thisworkbook成为activeworkbookColumns(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 Withi = 2DoIf Sheet1.Cells(i, 1) = ThenExit DoEnd Ifk = 1DoIf Sheet1.Cells(i, A) = Sheet1.Cells(i + 1, A) Thenk = k + 1i = i + 1Elsei = i + 1Exit DoEnd IfLoopSheet1.Cells(i - k, E) = WorksheetFunction.Max(Range(Sheet1.Cells(i - k, D), Sheet1.Cells(i - 1, D)LoopN = i - 1For i = 3 To NIf Sheet1.Cells(i, E) = ThenSheet1.Cells(i, E) = Sheet1.Cells(i - 1, E)End IfNext imaxv = WorksheetFunction.Max(Sheet1.Columns(E:E)Sheet1.Cells(1, F) = 0.5nv = 1r = 0.5Do While r = 0.9If nv & maxv * r, Sheet1.Columns(E:E), & maxv * r, Sheet1.Columns(E:E), 0 And Cells(i, E) = vv * maxv And Cells(i, E) 0.5 ThenSheet1.Cells(i, F) = 1ElseSheet1.Cells(i, F) = 0End IfEnd IfNext iSheet1.Cells(1, F).Formula = =sum(F2:F & N & )/count(F2:F & N & )For i = 2 To N 求偏差If i = 2 Thentemp = i + 1Elsetemp = iEnd IfIf Application.WorksheetFunction.CountIf(Sheet1.Range(Cells(temp - 2, D), Cells(i + 2, D), Cells(i, E) 0 And Cells(i, E) = vv * maxv And Cells(i, E) 0.5 ThenSheet1.Cells(i, F) = 1ElseSheet1.Cells(i, F) = 0End IfEnd IfNext