Note: The other languages of the website are Google-translated. Back to English
Masuk  \/ 
x
or
x
Daftar  \/ 
x

or

Bagaimana cara mengonversi banyak file Excel ke file CSV di Excel?

Di Excel, kita dapat mengonversi buku kerja ke file CSV dengan fungsi Simpan sebagai, tetapi apakah Anda tahu cara mengonversi banyak file Excel menjadi file CSV terpisah? Pada artikel ini, saya memperkenalkan kode VBA untuk mengonversi semua file Excel dalam folder menjadi file CSV di Excel.

Batch mengkonversi file Excel dari sebuah folder ke file CSV dengan VBA

Ubah lembar buku kerja menjadi file CSV terpisah dengan Kutools for Excelide bagus3


Batch mengkonversi file Excel dari sebuah folder ke file CSV dengan VBA

Di Excel, tidak ada fungsi bawaan yang dapat menyelesaikan pekerjaan ini dengan cepat kecuali VBA.

1. Aktifkan Excel, dan tekan Alt + F11 kunci terbuka Microsoft Visual Basic untuk Aplikasi jendela.

2. klik Menyisipkan > Modul untuk membuat Modul baru.

3. Salin kode di bawah ini dan tempelkan ke jendela Modul baru.

VBA: Batch mengkonversi file Excel ke CSV

Sub WorkbooksSaveAsCsvToFolder()
'UpdatebyExtendoffice20181031
Dim xObjWB As Workbook
Dim xObjWS As Worksheet
Dim xStrEFPath As String
Dim xStrEFFile As String
Dim xObjFD As FileDialog
Dim xObjSFD As FileDialog
Dim xStrSPath As String
Dim xStrCSVFName As String

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    On Error Resume Next
Set xObjFD = Application.FileDialog(msoFileDialogFolderPicker)
    xObjFD.AllowMultiSelect = False
    xObjFD.Title = "Kutools for Excel - Select a folder which contains Excel files"
    If xObjFD.Show <> -1 Then Exit Sub
    xStrEFPath = xObjFD.SelectedItems(1) & "\"

    Set xObjSFD = Application.FileDialog(msoFileDialogFolderPicker)

    xObjSFD.AllowMultiSelect = False
    xObjSFD.Title = "Kutools for Excel - Select a folder to locate CSV files"
    If xObjSFD.Show <> -1 Then Exit Sub
    xStrSPath = xObjSFD.SelectedItems(1) & "\"

    xStrEFFile = Dir(xStrEFPath & "*.xls*")

    Do While xStrEFFile <> ""
        Set xObjWB = Workbooks.Open(Filename:=xStrEFPath & xStrEFFile)
        xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & ".csv"
        xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSV
        xObjWB.Close savechanges:=False
        xStrEFFile = Dir
  Loop
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

4. tekan F5 kunci, pilih folder berisi file Excel yang ingin Anda konversi ke file CSV di dialog yang pertama muncul.
doc batch ke csv 1

5. klik OK, lalu di dialog popping kedua, pilih folder untuk menempatkan file CSV.
doc batch ke csv 2

6. klik OK, sekarang file Excel di folder tersebut telah diubah menjadi file CSV dan disimpan di folder lain.
doc batch ke csv 3


Ubah lembar buku kerja menjadi file CSV terpisah dengan Kutools for Excel

Seperti yang kita ketahui, kita hanya bisa mengonversi seluruh workbook menjadi satu file CSV di Excel dengan fungsi Save As-nya. Namun terkadang, Anda ingin mengonversi satu lembar menjadi file CSV, dalam hal ini, file Pisahkan Buku Kerja kegunaan Kutools untuk Excel bisa membantumu.

Kutools untuk Excel, dengan lebih dari 300 fungsi praktis, membuat pekerjaan Anda lebih mudah. 

Setelah menginstal Kutools for Excel, lakukan seperti di bawah ini:(Unduh Gratis Kutools untuk Excel Sekarang!)

1. Aktifkan buku kerja yang ingin Anda ubah lembarannya sebagai file CSV terpisah, klik Kutools Plus > Buku catatan > Pisahkan Buku Kerja.
doc batch ke csv 4

2. Dalam Pisahkan Buku Kerja dialog, centang nama lembar yang ingin Anda pisahkan (semua lembar dicentang secara default), centang Simpan sebagai ketik, pilih CSV (Macintosh) (* .CSV) dari daftar drop-down
doc batch ke csv 5

3. klik Membagi untuk memunculkan Jelajahi Folder dialog, pilih atau buat folder untuk menempatkan file CSV.
doc batch ke csv 6

4. klik OK, sekarang buku kerja telah dipisahkan sebagai file CSV terpisah.
doc batch ke csv 7


Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Super Formula Bar (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
tab kte 201905

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Say something here...
symbols left.
You are guest
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.
    TBenson85 · 2 months ago
    Hello, is there a quick change to the code that would allow me to change from a CSV UTF-8 (Comma delimited) to just CSV (Comma delimited)? I tried the first method and was hopeful but it seems like it won't change them since they are already in some form of CSV.  Maybe there is an easier process but I can't find anything.  I have to convert maybe 150 files that were saved in this format and I don't want to open every file and Save As if I can avoid it.  Any help is appreciated!
    • To post as a guest, your comment is unpublished.
      TBenson85 · 2 months ago
      So, after reviewing the code a little closer, I saw where the initial file type had to be .xls.  So replacing with .csv has solved the issue.  
  • To post as a guest, your comment is unpublished.
    Jared · 3 months ago
    This is amazing. Thank-you!
  • To post as a guest, your comment is unpublished.
    wilfried · 4 months ago
    top thanks :) 
  • To post as a guest, your comment is unpublished.
    Sen · 1 years ago
    I think it is worth adding better error handling for files with special characters, currently they are simply ignored.
  • To post as a guest, your comment is unpublished.
    Justin · 1 years ago
    Thank you for sharing. I'm trying to save out multiple xls files which contain a unique value, producing a prompt asking yes or no before saving. The prompt reads..

    "Some features in your workbook might be lost if you save it as a CSV (Comma delimited). Do you want to keep using that format?"

    Would someone know where to add the code to answer yes to this prompt?
  • To post as a guest, your comment is unpublished.
    Sam · 2 years ago
    Another small remark:

    If the cells in the original Excel files are all formatted as "General", some accuracy is lost when the file is saved as a CSV
    For example, if a cell value in Excel is 0.123456789123456, then the value in the CSV will be 0.123456789 (missing the remaining decimals), as long as the cell was formatted as 'General'. This can be solved by formatting all cells in the Excel file to anything else than 'General' (for example, 'Text'). In that case, the CSV *will* still have the full detail/accuracy. I.e. the values in the Excel files will be fully intact after saving as a CSV.

    How could this macro be changes, so it sets the formatting of all cells in the Excel file to 'Text', before saving as a CSV?
    I imagine that it must somehow make use of the following, but I can't figure out how to correctly include in within the macro:

    Cells.Select
    Selection.NumberFormat = "@"
  • To post as a guest, your comment is unpublished.
    Sam · 2 years ago
    Works great, thanks for the code!
    My only remark would be that this code cuts of file names when there is a "." in the filename itself (e.g. file.123.csv turns into file.csv).
    • To post as a guest, your comment is unpublished.
      Carol · 1 years ago
      Have you found a way around this issue?
      • To post as a guest, your comment is unpublished.
        Clara · 1 years ago
        Carol,

        On line 33 I've replaced this code:

        xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & ".csv"

        With this code:

        xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".xlsx") - 1) & ".csv"

        Note that if you're using some other excel extension (.xls, .xlsm, etc.) you should change it as such :)
        • To post as a guest, your comment is unpublished.
          Sonya · 1 years ago
          Thank you so much! This has saved me so much time!!
  • To post as a guest, your comment is unpublished.
    Aman · 2 years ago
    Ty it really works dear !!