Categories: Excel

Excel VBA 將資料匯出成 CSV 檔案教學與範例

介紹如何在 Excel 中使用 VBA 巨集將表格資料匯出為逗點分隔（csv）檔案。

另存為 CSV 檔案

若要將指定的工作表另存為 CSV 檔案，可以在選擇工作表之後，將工作表先複製到新的活頁簿中，再使用 Workbook.SaveAs 方法將活頁簿另存為 CSV 檔案：

' 選擇工作表
ThisWorkbook.Sheets("工作表1").Activate

' 將目前選擇的工作表複製至新的活頁簿
ActiveSheet.Copy

' 另存新檔
With ActiveWorkbook
    .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False ' 另存新檔
    .Saved = True  ' 避免出現儲存提示
    .Close         ' 關閉活頁簿
End With

Excel 以 VBA 另存為 CSV 檔案

這裡在另存新檔之前，先把工作表複製一份出來，可以避免在另存新檔之後影響到原本的 Excel 活頁簿。

另存為 UTF8 編碼 CSV 檔案

如果想要將 Excel 工作表匯出為 UTF8 編碼的 CSV 檔案，可以在呼叫 Workbook.SaveAs 方法的時候，將 FileFormat 參數設定為 xlCSVUTF8：

' 選擇工作表
ThisWorkbook.Sheets("工作表1").Activate

' 將目前選擇的工作表複製至新的活頁簿
ActiveSheet.Copy

' 另存新檔
With ActiveWorkbook
    .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSVUTF8, CreateBackup:=False ' 另存為 UTF8 編碼的 CSV 檔案
    .Saved = True  ' 避免出現儲存提示
    .Close         ' 關閉活頁簿
End With

將所有工作表匯出為 CSV 檔案

若要將 Excel 活頁簿中的所有工作表全部都匯出為 CSV 檔案，可以搭配 For Each 迴圈對每一張工作表逐一匯出：

Dim ws As Worksheet
Dim csvFilename As String
Dim csvDir As String

' 設定放置匯出 CSV 檔案的目錄
csvDir = ThisWorkbook.Path

' 對每一張工作表逐一處理
For Each ws In ActiveWorkbook.Worksheets

    ' 將工作表複製至新的活頁簿
    ws.Copy

    ' 設定匯出的 CSV 檔案名稱
    csvFilename = csvDir & "\" & ws.Name & ".csv"

    ' 將目前活頁簿另存為 CSV 檔案
    With ActiveWorkbook
        .SaveAs Filename:=csvFilename, FileFormat:=xlCSV, CreateBackup:=False ' 另存為 CSV 檔案
        .Saved = True  ' 避免出現儲存提示
        .Close         ' 關閉活頁簿
    End With

Next

這段 VBA 巨集程式碼在執行之後，會自動匯出目前 Excel 活頁簿中的每一張工作表，而每個 CSV 檔案則以工作表的名稱命名，儲存在原本 Excel 檔案的目錄中。

多 Excel 檔案批次匯出為 CSV 檔案

如果需要將指定目錄之下的所有 Excel 檔案批次轉換成 CSV 檔案，可以參考以下 VBA 巨集：

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object

' 所有 Excel 檔案放置的目錄
rootFolder = "C:\OfficeGuide\Excel"

' 建立 FileSystemObject 物件
Set oFSO = CreateObject("Scripting.FileSystemObject")

' 建立目錄物件
Set oFolder = oFSO.GetFolder(rootFolder)

' 逐一處理每個 Excel 檔案
For Each oFile In oFolder.Files

    ' 建立此 Excel 檔案輸出 CSV 檔案的目錄
    outputFolder = rootFolder & "\" & oFSO.GetBaseName(oFile.Name)
    MkDir outputFolder

    ' 開啟 Excel 檔案
    excelFilePath = rootFolder & "\" & oFile.Name
    Set wb = Workbooks.Open(excelFilePath)

    ' 對此 Excel 檔案的每一張工作表逐一處理
    For Each ws In wb.Worksheets

        ' 將工作表複製至新的活頁簿
        ws.Copy

        ' 設定匯出的 CSV 檔案名稱
        csvFilename = outputFolder & "\" & ws.Name & ".csv"

        ' 將目前活頁簿另存為 CSV 檔案
        With ActiveWorkbook
            .SaveAs Filename:=csvFilename, FileFormat:=xlCSV, CreateBackup:=False ' 另存為 CSV 檔案
            .Saved = True  ' 避免出現儲存提示
            .Close         ' 關閉活頁簿
        End With

    Next

    ' 關閉 Excel 檔案
    wb.Saved = True
    wb.Close

Next oFile