VBA批量导入CSV文件、批量改数据标题、批量做数据透视表

'本代码是利用VB批量导入CSV文件，并且每个文件存为一个Sheet
'前提是所有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