'前提是所有CSV文件和运行宏的这个文件在同一个文件夹
'不智能的地方是我预先知道有多少个CSV文件,然后把文件名改为1.csv,2.csv,...
'所以循环的时候总数是写死的
'另外我的数据导入处理方式是:文件包含标题且按逗号隔开,若不一样更改参数就行了
'下面就附上所有完整代码:
'主函数
Sub main()
Dim startNum,endNum as Integer
startNum = 1
endNum = 5
Call addSheet_openFile(startNum,endNum)
Call changeName(startNum,endNum)
Call perspectiveTable(startNum,endNum)
ActiveWorkbook.Save
End Sub
'批量导入CSV
Sub addSheet_openFile(startNum,endNum)
Dim i As Integer
Dim NewSheet As Worksheet
For i = startNum To endNum
ActiveSheet.Name = "Sheet" & CStr(i)
With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\xlh\201801\优惠券统计\" & CStr(i) & ".csv" _
, Destination:=Range("$A$1"))
.Name = CStr(i)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
If i < endNum Then
Set NewSheet = Worksheets.Add(after:=ActiveSheet)
End If
Next
End Sub
'批量导入之后需要同意更改所有Sheet的数据标题
Sub changeName(startNum,endNum)
Dim i As Integer
For i = startNum To endNum
Sheets("Sheet" + CStr(i)).Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "userno"
Range("B1").Select
ActiveCell.FormulaR1C1 = "cid"
Range("C1").Select
ActiveCell.FormulaR1C1 = "count"
Next
End Sub
'然后就需要对每个sheet做数据透视表进行分类统计
Sub perspectiveTable(startNum,endNum)
Dim i As Integer
Dim sheetName As String
For i = startNum To endNum
sheetName = "Sheet" & CStr(i)
Sheets(sheetName).Select
Columns("A:C").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
sheetName & "!R1C1:R1048576C3", Version:=6).CreatePivotTable TableDestination:= _
sheetName & "!R1C6", TableName:="数据透视表2", DefaultVersion:=6
Sheets(sheetName).Select
Cells(1, 6).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("数据透视表2").PivotFields("userno")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("数据透视表2").PivotFields("cid")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("数据透视表2").AddDataField ActiveSheet.PivotTables("数据透视表2" _
).PivotFields("count"), "求和项:count", xlSum
Columns("A:C").Select '删除原始数据
Selection.Delete Shift:=xlToLeft
Next
End Sub