excel文件批量转换格式为csv文件，并合并成一个csv

一、excel文件批量转换格式为csv文件

        1. 打开一个Excel工具，查看菜单中是否有【开发工具】，如果没有，需要手动添加该工具。具体步骤如下，通过【文件】->【选项】，弹出Excel选项对话框，

        2. 菜单中出现了【开发工具】，

        3. 通过点击菜单【VB】，弹出代码编辑框，输入如下代码，

Sub SaveToCSVs()
    Dim fDir As String
    Dim wB As Workbook
    Dim wS As Worksheet
    Dim fPath As String
    Dim sPath As String
 
    fPath = "X:\excel文件目录\"
    sPath = "X:\CSV文件保存目录\"
 
    fDir = Dir(fPath)
    Do While (fDir <> "")
        If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then
            On Error Resume Next
            Set wB = Workbooks.Open(fPath & fDir)
            'MsgBox (wB.Name)
            For Each wS In wB.Sheets
                wS.SaveAs sPath & wB.Name & ".csv", xlCSV
            Next wS
            wB.Close False
            Set wB = Nothing
        End If
        fDir = Dir
        On Error GoTo 0
    Loop
End Sub

        4.  点击【运行】，

        5. 等待若干时间，在目标文件夹下，生成所要的csv文件。

二、将多个csv合并到一个csv中

1. 将全部的csv文件放到一个文件夹中cmd
2. 打开cmd，切换到存放csv的文件夹数据分析
3. 输入命令

   copy *.csv 你本身起的文件名.csv

   以后按回车等待完成，打开文件夹就能够看到合成的csv。

三、csv文件批量转换格式为excel文件

Sub SaveToXLSX()
    Dim fDir As String
    Dim wB As Workbook
    Dim wS As Worksheet
    Dim fPath As String
    Dim sPath As String
    fPath = "X:\csv文件目录\"
    sPath = "X:\excel文件保存目录\"
    fDir = Dir(fPath)
    Do While (fDir <> "")
        If Right(fDir, 4) = ".csv" Or Right(fDir, 5) = ".csv" Then
            On Error Resume Next
            Set wB = Workbooks.Open(fPath & fDir)
            'MsgBox (wB.Name)
            For Each wS In wB.Sheets
                wS.SaveAs sPath & wB.Name & ".xlsx" _
                , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            Next wS
            wB.Close False
            Set wB = Nothing
        End If
        fDir = Dir
        On Error GoTo 0
    Loop
End Sub