Preskoči na glavno vsebino

Kako v Excelu paketno pretvoriti več datotek CSV v datoteke XLS (X)?

Pretvorbo datoteke CSV v datoteko XlS ali XLSX je zelo enostavno z uporabo funkcije Shrani kot. Pretvorba več datotek CSV v datoteke XLS ali XLSX iz mape pa zamuja z ročnim shranjevanjem po eno. Tukaj predstavljam makro kodo za hitro paketno pretvorbo vseh datotek CSV v datoteke XLS (x) iz mape.

Paketno pretvorite datoteke CSV v datoteke XlS (X) z makro kodo


Paketno pretvorite datoteke CSV v datoteke XlS (X) z makro kodo

Če želite pretvoriti več datotek CSV iz ene mape v datoteke XLS (X), lahko storite naslednje:

1. Omogočite nov delovni zvezek, pritisnite Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno in kliknite Vstavi > Moduli. Oglejte si posnetek zaslona:
doc paket pretvori cvs xls 1

Opombe: Prepričajte se, da so vse datoteke CSV, ki jih želite pretvoriti, zaprte.

2. Nato prilepite spodnjo kodo makra na Moduli in pritisnite F5 tipko za zagon kode.

VBA: Pretvori CSV v 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. V pojavnem pogovornem oknu izberite določeno mapo z datotekami CSV, ki jih želite pretvoriti. Oglejte si posnetek zaslona:
doc paket pretvori cvs xls 2

4. klik OK, so vse datoteke CSV v izbrani mapi v njej pretvorjene v datoteke XLS.
doc paket pretvori cvs xls 3

Nasvet: Če želite datoteke CSV pretvoriti v datoteke XLSX, uporabite spodnjo kodo VBA.

VBA: Pretvorite datoteke CSV v 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", ".xlsx", vbTextCompare), xlWorkbookDefault
        ActiveWorkbook.Close
        Windows(xWsheet).Activate
        xCSVFile = Dir
    Loop
    Application.StatusBar = False
    Application.DisplayAlerts = True
End Sub

hitro pretvorite ali izvozite obseg lista, da enkrat ločite datoteke XLS / Word / PDF ali druge oblike zapisa

Običajno Excel ne podpira možnosti hitrega izvoza ali shranjevanja obsega kot datoteke CSV ali Excel. Če želite shraniti obseg podatkov kot CSV ali delovni zvezek v Excelu, boste morda morali za to uporabiti makro VBA ali kopirati obseg v odložišče in ga prilepiti v nov delovni zvezek, nato pa delovni zvezek shraniti kot CSV ali Delovni zvezek. Kutools za Excel povečuje Excel z Izvozi obseg v datoteko pripomoček za uporabnike Excela, ki želijo hitro obdelati naslednje postopke:  Kliknite za 30-dnevno brezplačno preskusno različico vseh funkcij!
doc izvozi obseg celic v datoteko
 
Kutools za Excel: z več kot 300 priročnimi dodatki za Excel lahko brezplačno preizkusite brez omejitev v 30 dneh.

Relativni članki:

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (41)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Realmente me funcionó, muchas gracias!
Rated 5 out of 5
This comment was minimized by the moderator on the site
Thanks for this macro code! It works and converts my csv's to xlsx and saves me time. The only problem I have is that the column name in cell A1 is gone.
I have to add this back to all the xlsx files because this is needed for further scripts I run on the xlsx files.
This comment was minimized by the moderator on the site
Hi, Cyril, I have test the code again, the column name in A1 is still saved in the xlsx files.
This comment was minimized by the moderator on the site
Not working. Copied same code.. Pop up comes to select csv but even though there are csv files in the folder, non appears in the browse list. any reason why?
This comment was minimized by the moderator on the site
Buenos dias compañeros

Adiciona a lo que hace el script, quisiera poder convertir inmediatamente el texto en columnas, por la opción que no es delimitado por ningún tipo de caracter, adicional los tamaños de las columnas son diferentes. Vale la pena aclarar que si tomo cada archivo por separado y le aplico la opción de texto en columnas, bajo esa opción los puedo convertir sin problemas, abriéndolos uno por uno, pero si grabo esa macro y le aplico la misma macro a todos, inclusive usando la opción de "USAR REFERENCIAS RELATIVAS", no hace el proceso bien, porque el graba las posiciones de la delimitación del archivo que uso de ejemplo, pero necesito que lo aplique nuevo a cada archivo, es decir, como si abriera cada archivo nuevo y le hiciera manualmente la opción de delimitación y no aplique los valores de ubicación de las columnas identificados en el archivo ejemplo.

Ya inserte esa opcion en este archivo, sin embargo pasa lo que indico en la lineas arriba, solo requiero saber como puedo hacer para aplicar la conversion a cada archivo, aplicando la delimitacion del campo como su fuera un archivo nuevo.


Workbooks.Open Filename:=xSPath & xCSVFile
ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xlsx", vbTextCompare), xlWorkbookDefault

ESTA PARTE ES LA QUE AGREGUE APLICANDO EL FORMATO DEL TEXTO A COLUMNAS

StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(18 _
, 1), Array(38, 1)), TrailingMinusNumbers:=True
ActiveCell.Cells.Select
ActiveCell.Cells.EntireColumn.AutoFit
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveWorkbook.Save

HASTA AQUI AGREGUE YO

ActiveWorkbook.Close
Windows(xWsheet).Activate
xCSVFile = Dir

Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub

No se si me hago explicar bien, pero es mi problema.
This comment was minimized by the moderator on the site
yes it's not work.
This comment was minimized by the moderator on the site
How would I incorporate code into the XLSX batch file routine to semicolon separate the data into separate columns? The code shared converts the file from a .csv to a .xlsx, but it doesn't separate the data into separate columns.
This comment was minimized by the moderator on the site
This is working for me, but the file extension is not changing. Any tips?
This comment was minimized by the moderator on the site
I had the same problem. If your source file has an extention in capital letters (.CSV) it doesnt work. Just replace .csv to .CSV in the code in line 22 and then it should work. Well, at least for me it did.
This comment was minimized by the moderator on the site
Same for me -- all files remain .csv.
This comment was minimized by the moderator on the site
All csv files you choose have been save as new excel files, the original files (csv) do not change, there are new excel files with same contents existing.
This comment was minimized by the moderator on the site
I get an error "Object variable or with block variable not set" for this line:
xFd.Title = "/Users/[my.name]/Documents/[myFolder]" >> this is the path of a particular folder on my computer where I'm doing the conversion (the folder has a bunch of csv files inside)

Am I doing something wrong here?
This comment was minimized by the moderator on the site
You have an error on line 22 of the csv to xlsx
With Error - ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlWorkbookDefault
Corrected - ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xslx", vbTextCompare), xlWorkbookDefault
This comment was minimized by the moderator on the site
Thanks for your reminder, I have updated it, thanks again.
This comment was minimized by the moderator on the site
great article. How can I, save all the converted files in a new location? Can you please update the code and send it to me. Like allowing the user to choose his destination folder.


Thanks
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations