Note: The other languages of the website are Google-translated. Back to English
Log på  \/ 
x
or
x
Tilmeld  \/ 
x

or

Hvordan batchkonverteres flere Excel-filer til CSV-filer i Excel?

I Excel kan vi konvertere projektmappen til CSV-fil med funktionen Gem som, men kunne du vide, hvordan du batchkonverterer flere Excel-filer til separate CSV-filer? I denne artikel introducerer jeg en VBA-kode til batchkonvertering af alle Excel-filer i en mappe til CSV-filer i Excel.

Batchkonverter Excel-filer i en mappe til CSV-filer med VBA

Konverter ark med projektmappe til separate CSV-filer med Kutools til Excelgod idé3


Batchkonverter Excel-filer i en mappe til CSV-filer med VBA

I Excel er der ingen indbygget funktion, der kan løse dette job hurtigt undtagen VBA.

1. Aktivér Excel, og tryk på Alt + F11 tasterne åbnes Microsoft Visual Basic til applikationer vindue.

2. klik indsatte > Moduler for at oprette et nyt modul.

3. Kopier nedenstående kode og indsæt dem i det nye modulvindue.

VBA: Batchkonvertere Excel-filer til 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. Trykke F5 nøgle, vælg mappen, der indeholder de Excel-filer, du vil konvertere til CSV-filer i den første poppende dialog.
doc-batch til csv 1

5. klik OK, vælg derefter i den anden poppedialog den mappe, der skal placere CSV-filerne.
doc-batch til csv 2

6. klik OK, nu er Excel-filerne i mappen konverteret til CSV-filer og gemt i en anden mappe.
doc-batch til csv 3


Konverter ark med projektmappe til separate CSV-filer med Kutools til Excel

Som vi vidste, kan vi kun konvertere hele projektmappen til en CSV-fil i Excel med dens Gem som-funktion. Men i nogle gange vil du konvertere det enkelte ark til CSV-fil, i dette tilfælde den Opdelt arbejdsbog nytte af Kutools til Excel kan hjælpe dig.

Kutools til Excel, med mere end 300 praktiske funktioner, der gør dine job lettere. 

Efter installation Kutools til Excel, gør venligst som nedenfor:(Gratis download Kutools til Excel nu!)

1. Aktivér projektmappe, du vil konvertere dens ark som separate CSV-filer, klik på Kutools Plus > Workbook > Opdelt arbejdsbog.
doc-batch til csv 4

2. i Opdelt arbejdsbog dialog, skal du kontrollere det arknavn, du vil opdele (alle ark er markeret som standard), skal du kontrollere Gem som skriv, vælg CSV (Macintosh) (* .CSV) fra rullelisten.
doc-batch til csv 5

3. klik Dele at poppe ud a Gennemse for mappe dialog, skal du vælge eller oprette en mappe for at placere CSV-filerne.
doc-batch til csv 6

4. klik OK, nu er projektmappen opdelt som separate CSV-filer.
doc-batch til csv 7


De bedste Office-produktivitetsværktøjer

Kutools til Excel løser de fleste af dine problemer og øger din produktivitet med 80%

  • Genbruge: Indsæt hurtigt komplekse formler, diagrammer og alt, hvad du har brugt før; Krypter celler med adgangskode Opret postliste og send e-mails ...
  • Super formel bar (let redigere flere linjer med tekst og formel); Læsning Layout (let at læse og redigere et stort antal celler); Indsæt til filtreret rækkevidde...
  • Flet celler / rækker / kolonner uden at miste data; Split celler indhold; Kombiner duplikerede rækker / kolonner... Forhindre duplikerede celler; Sammenlign områder...
  • Vælg Duplicate eller Unique Rækker; Vælg tomme rækker (alle celler er tomme); Super Find og Fuzzy Find i mange arbejdsbøger; Tilfældig valg ...
  • Præcis kopi Flere celler uden at ændre formelreference; Auto Opret referencer til flere ark; Indsæt kugler, Afkrydsningsfelter og mere ...
  • Uddrag tekst, Tilføj tekst, Fjern efter position, Fjern mellemrum; Opret og udskriv personsøgningssubtotaler; Konverter mellem celler indhold og kommentarer...
  • Superfilter (gem og anvend filterskemaer på andre ark); Avanceret sortering efter måned / uge / dag, hyppighed og mere; Specielt filter af fed, kursiv ...
  • Kombiner arbejdsbøger og arbejdsark; Fletabeller baseret på nøglekolonner; Opdel data i flere ark; Batch Konverter xls, xlsx og PDF...
  • Mere end 300 kraftfulde funktioner. Understøtter Office / Excel 2007-2019 og 365. Understøtter alle sprog. Nem implementering i din virksomhed eller organisation. Fuld funktioner 30-dages gratis prøveperiode. 60-dages pengene tilbage garanti.
kte-fane 201905

Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50% og reducerer hundredvis af museklik for dig hver dag!
officetab bund
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 · 19 days 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 · 18 days 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 · 1 months ago
    This is amazing. Thank-you!
  • To post as a guest, your comment is unpublished.
    wilfried · 3 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 !!