提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中將多個CSV文件批量轉換為XLS(X)文件?

通過應用“另存為”功能,將CSV文件轉換為XLS或XLSX文件非常容易。 但是,要將多個CSV文件從文件夾轉換為XLS或XLSX文件非常耗時,需要手動逐個保存。 這裡我介紹一個宏代碼來快速將所有CSV文件從文件夾批量轉換為XLS(x)文件。

使用宏代碼將CSV文件批量轉換為XLS(X)文件

Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel - 最佳辦公生產力工具將解決您的大部分Excel問題
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文本; 將數字和貨幣轉換為英語單詞...
  • 合併工具:多個工作簿和表格合二為一; 合併多個單元格/行/列而不丟失數據; 合併重複行和總和...
  • 拆分工具:根據價值將數據拆分為多個表格; 一個工作簿到多個Excel,PDF或CSV文件; 一列到多列......
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 創建郵件列表和 通過Cell的價值發送電子郵件...
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 適用於Office 2007-2019和365; 支持所有語言; 在公司輕鬆部署; 全功能60天免費試用。

使用宏代碼將CSV文件批量轉換為XLS(X)文件


要將多個CSV文件從一個文件夾轉換為XLS(X)文件,可以按照以下步驟進行操作:

Kutools for Excel, 與以上 120 方便的功能,使您的工作更輕鬆。

1。 按下啟用新工作簿 Alt + F11鍵 鍵打開 Microsoft Visual Basic for Applications 窗口,然後單擊 插入 > 模塊。 看截圖:
doc批量轉換cvs xls 1

注意:確保您要轉換的所有CSV文件都已關閉。

2。 然後將下面的宏代碼粘貼到 模塊 腳本和新聞 F5 鍵來運行代碼。

VBA:將CSV轉換為XLS

Sub CSVtoXLS()
'UpdatebyExtendoffice20170814
    Dim xFd As FileDialog
    Dim xSPath As String
    Dim xCSVFile As String
    Dim xWsheet As String
    Application.DisplayAlerts = False
    Application.StatusBar = True
    xWsheet = ActiveWorkbook.Name
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    xFd.Title = "Select a folder:"
    If xFd.Show = -1 Then
        xSPath = xFd.SelectedItems(1)
    Else
        Exit Sub
    End If
    If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
    xCSVFile = Dir(xSPath & "*.csv")
    Do While xCSVFile <> ""
        Application.StatusBar = "Converting: " & xCSVFile
        Workbooks.Open Filename:=xSPath & xCSVFile
        ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlNormal
        ActiveWorkbook.Close
        Windows(xWsheet).Activate
        xCSVFile = Dir
    Loop
    Application.StatusBar = False
    Application.DisplayAlerts = True
End Sub

3。 在彈出對話框中,選擇包含要轉換的CSV文件的指定文件夾。 看截圖:
doc批量轉換cvs xls 2

4。 點擊 OK,所選文件夾中的所有CSV文件都已轉換為XLS文件。
doc批量轉換cvs xls 3

小技巧: :如果您想將CSV文件轉換為XLSX文件,請使用下面的VBA代碼。

VBA:將CSV文件轉換為XLSX

Sub CSVtoXLS()
'UpdatebyExtendoffice20170814
    Dim xFd As FileDialog
    Dim xSPath As String
    Dim xCSVFile As String
    Dim xWsheet As String
    Application.DisplayAlerts = False
    Application.StatusBar = True
    xWsheet = ActiveWorkbook.Name
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    xFd.Title = "Select a folder:"
    If xFd.Show = -1 Then
        xSPath = xFd.SelectedItems(1)
    Else
        Exit Sub
    End If
    If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
    xCSVFile = Dir(xSPath & "*.csv")
    Do While xCSVFile <> ""
        Application.StatusBar = "Converting: " & xCSVFile
        Workbooks.Open Filename:=xSPath & xCSVFile
        ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlWorkbookDefault
        ActiveWorkbook.Close
        Windows(xWsheet).Activate
        xCSVFile = Dir
    Loop
    Application.StatusBar = False
    Application.DisplayAlerts = True
End Sub


好導出範圍到文件

Kutools for Excel's 導出範圍到文件功能 可以導出或保存一個範圍來分隔文件
工作簿,pdf,文本,csv或文本。
點擊 企業 > 進出口 > 導出範圍到文件.
doc將單元格範圍導出到文件

相關文章:


Kutools for Excel - 最佳辦公生產力工具提高80%的生產力

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能60天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    viper · 1 months ago
    it changes my date column format from dd-mm-yyyy to dd/mm/yyyy, please can anyone help with how to prevent this script from changing column formats.
    Thanks a lot.
  • To post as a guest, your comment is unpublished.
    lisa6538 · 3 months ago
    Thanks for providing the code. I modified the code to do the XLSX to CSV batch conversion. The code is provided below.


    Sub XLSXtoCSV()
    'UpdatebyExtendoffice20170814
    Dim xFd As FileDialog
    Dim xSPath As String
    Dim xCSVFile As String
    Dim xWsheet As String
    Application.DisplayAlerts = False
    Application.StatusBar = True
    xWsheet = ActiveWorkbook.Name
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    xFd.Title = "Select a folder:"
    If xFd.Show = -1 Then
    xSPath = xFd.SelectedItems(1)
    Else
    Exit Sub
    End If
    If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
    xXLSXFile = Dir(xSPath & "*.xlsx")
    Do While xXLSXFile <> ""
    Application.StatusBar = "Converting: " & xXLSXFile
    Workbooks.Open Filename:=xSPath & xXLSXFile
    ActiveWorkbook.SaveAs Replace(xSPath & xXLSXFile, ".xlsx", ".csv", vbTextCompare), xlCSV
    ActiveWorkbook.Close
    Windows(xWsheet).Activate
    xXLSXFile = Dir
    Loop
    Application.StatusBar = False
    Application.DisplayAlerts = True
    End Sub
    • To post as a guest, your comment is unpublished.
      Diego Jaime · 18 days ago
      Thank you for the modification, that´s what I was looking for!
  • To post as a guest, your comment is unpublished.
    Sam · 3 months ago
    One small issue with this code is that some accuracy (number of decimals) can be lost when opening a csv and saving it as xlsx.
    The way to prevent this is to select all cells right after opening, set the number of decimal places to the desired amount (15 for me personally), and *then* saving as xlsx

    Otherwise, the xlsx has less detail (less accuracy in terms of decimals) than the original csv, which can lead to problems in some applications
  • To post as a guest, your comment is unpublished.
    Rose · 3 months ago
    this macro does not show my csv file
  • To post as a guest, your comment is unpublished.
    stb · 4 months ago
    Cool bit of code. Very useful. If I'm not mistaken the code for .xlsx looks the same as the .xls code.

    This line:
    ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlWorkbookDefault

    I assume it should be: ".csv" , ".xlsx", vbTextCompare etc.
    • To post as a guest, your comment is unpublished.
      iBrezel · 4 months ago
      I've changed the code so, described above; now the script works as expected: it generates xlsx files
      • To post as a guest, your comment is unpublished.
        thy · 2 months ago
        no you haven't. it still says xls in both versions.
  • To post as a guest, your comment is unpublished.
    pramod koliar · 6 months ago
    Great Script after searching all other script available in google. Can anybody help how to modify this script by specifying a folder location in local drive instead of allowing it to open the dialog box as i am actually automating the system. Thanks in advance
  • To post as a guest, your comment is unpublished.
    Pramod Koliar · 6 months ago
    Hi, great script which worked for me after trying all other script available in google. I have added few more lines which performs the text to columns operation which i want. Can anybody help me how to modify the script by specifying the folder location in local drive instead of opening the dialog box as i am actually automating the system
    . Thanks in advance
  • To post as a guest, your comment is unpublished.
    HC · 8 months ago
    Hello,

    a great article - thank you! Almost works for me.

    Here's the problem: I have a pipe-delimited csv file. I have changed the default windows delimiter (via control panel/regional settings) to "|". So, when I open the csv file with Excel, it opens, directly reads and parses the fields to columns correctly. All I have to do is to save as xls or xlsx, done. When I look at your code, that should be what the code does: It opens the csv files in a folder, saves them as xls or xlsx, and loops over that.

    Here is the problem: When I open those Excel files, the pipe delimiters are still there, nothing is parsed to the columns. This was the behavior as before my change to the pipe delimited default setting in the Windows settings. So it seems like Excel doesn't use those Windows settings. I verified this by replacing the pipe delimiters with comma delimiters in a copy of the file - of the two files, the pipe-delimited remained unparsed, the comma delimited got converted just fine.

    Any idea where that Excel-inbuilt comma delimitation can be found or changed, or whether there is a way in the code to do that? I would prefer NOT to have to do a search and replace, then save...

    Thanks, HC
    • To post as a guest, your comment is unpublished.
      Sunny · 7 months ago
      If you want to split the data into column based on the pipe delimiter in Excel, just use Text to Columns function to split data by / after coverting the csv files to xls.
  • To post as a guest, your comment is unpublished.
    Fred Maye · 11 months ago
    WHen I gave the folder name, the program said, "No files match your search." But there are 2,609 .csv files in the folder
    • To post as a guest, your comment is unpublished.
      Sunny · 10 months ago
      The code not support the separated comma csv file, are your files in this case?
  • To post as a guest, your comment is unpublished.
    ptee · 1 years ago
    I used the file multiple csv to multiple xls but get wrong xls de csv files have ; seperated info but become together in cells. How can this been solved?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      The code cannot support the separated comma csv file.
  • To post as a guest, your comment is unpublished.
    Dragos · 1 years ago
    Hi,

    Your script is great but it doesn't convert the csv separated by semicolons. Can you please post a solution for those?

    Thank you
    • To post as a guest, your comment is unpublished.
      Younes · 1 years ago
      i might be late i just got the issue where i needed to convert the semicolons CSV files and i added this to the code and it worked hope this will help someone

      Change 'Workbooks.Open Filename:=xSPath & xCSVFile' By this 'Workbooks.Open Filename:=xSPath & xCSVFile, Delimiter:=";", Local:=True'
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Sorry,here I do not have any solution, maybe you can place your question to our forum, someone else may help you. https://www.extendoffice.com/forum.html
  • To post as a guest, your comment is unpublished.
    Sathish · 1 years ago
    Thankyou so much for your excellent script for CSV to XLS. Its much more useful to my work
  • To post as a guest, your comment is unpublished.
    Matt Leonard · 1 years ago
    "Tip: If you want to convert CSV files to XLXS files, you just need to change .xls to .xlsx in the macro ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare)."

    I tried this, and Excel could not open the resulting files. I got the following message: "Excel cannot open the file 'filename.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

    However, the script was successful for converting to .xls.
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      I did not find that problem before, thank you for your correct comment. I have corrected the tip.
  • To post as a guest, your comment is unpublished.
    Karen · 1 years ago
    I want to do the opposite - convert XLS to CSV. does this script work?
    Sub CSVtoXLS()
    'UpdatebyExtendoffice20170814
    Dim xFd As FileDialog
    Dim xSPath As String
    Dim xXLSFile As String
    Dim xWsheet As String
    Application.DisplayAlerts = False
    Application.StatusBar = True
    xWsheet = ActiveWorkbook.Name
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    xFd.Title = "Select a folder:"
    If xFd.Show = -1 Then
    xSPath = xFd.SelectedItems(1)
    Else
    Exit Sub
    End If
    If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
    xXLSFile = Dir(xSPath & "*.xls")
    Do While xXLSFile <> ""
    Application.StatusBar = "Converting: " & xXLSFile
    Workbooks.Open Filename:=xSPath & xXLSFile
    ActiveWorkbook.SaveAs Replace(xSPath & xXLSFile, ".xls", ".csv", vbTextCompare), xlNormal
    ActiveWorkbook.Close
    Windows(xWsheet).Activate
    xXLSFile = Dir
    Loop
    Application.StatusBar = False
    Application.DisplayAlerts = True
    End Sub