Skip i'r prif gynnwys

Sut i swp-drosi ffeiliau CSV lluosog i ffeiliau XLS (X) yn Excel?

Mae trosi ffeil CSV yn ffeil XlS neu XLSX yn hawdd iawn i chi trwy gymhwyso'r nodwedd Save As. Fodd bynnag, mae trosi ffeiliau CSV lluosog i ffeiliau XLS neu XLSX o ffolder yn cymryd llawer o amser trwy arbed fesul un â llaw. Yma, rwy'n cyflwyno cod macro i swpio'n gyflym drosi'r holl ffeiliau CSV i ffeiliau XLS (x) o ffolder.

Swp trosi ffeiliau CSV i ffeiliau XlS (X) gyda chod macro

Swp trosi ffeiliau CSV i ffeiliau XlS (X) gyda chod macro

I drosi ffeiliau CSV lluosog o un ffolder i ffeiliau XLS (X), gallwch wneud fel y nodir isod:

1. Galluogi llyfr gwaith newydd, gwasgwch Alt + F11 allweddi i agor Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, a chlicio Mewnosod > Modiwlau. Gweler y screenshot:
swp doc trosi cvs xls 1

Nodyn: Sicrhewch fod yr holl ffeiliau CSV rydych chi am eu trosi ar gau.

2. Yna pastiwch o dan y cod macro i'r Modiwlau sgript, a gwasg F5 allwedd i redeg y cod.

VBA: Trosi CSV i 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. Yn y dialog popping out, dewiswch y ffolder penodedig sy'n cynnwys y ffeiliau CSV rydych chi am eu trosi. Gweler y screenshot:
swp doc trosi cvs xls 2

4. Cliciwch OK, mae'r holl ffeiliau CSV yn y ffolder a ddewiswyd wedi'u trosi'n ffeiliau XLS ynddo.
swp doc trosi cvs xls 3

Tip: Os ydych chi am drosi ffeiliau CSV i ffeiliau XLSX, rydych chi'n defnyddio isod cod VBA.

VBA: Trosi ffeiliau CSV i 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

trosi neu allforio ystod o ddalen yn gyflym i wahanu XLS / Word / PDF neu ffeiliau fformat eraill mewn unwaith
Fel rheol, nid yw Excel yn eich cefnogi gydag opsiwn i allforio neu arbed ystod yn gyflym fel ffeil CSV neu Excel. Os ydych chi am arbed ystod o ddata fel CSV neu lyfr gwaith yn Excel, efallai y bydd angen i chi ddefnyddio Macro VBA ar gyfer gwneud hyn neu i gopïo'r ystod i glipfwrdd a'i gludo mewn llyfr gwaith newydd ac yna arbed y llyfr gwaith fel CSV neu Llyfr Gwaith. Kutools for Excel ychwanegu at Excel gyda Ystod Allforio i'w Ffeilio cyfleustodau ar gyfer defnyddwyr Excel sydd am brosesu'r gweithrediadau canlynol yn gyflym ：  Cliciwch am dreial llawn sylw 30 diwrnod am ddim!
doc allforio ystod celloedd i'r ffeil
 
Kutools for Excel: gyda mwy na 300 o ychwanegiadau Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod.

Erthyglau Perthynas:

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools for Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed o'r blaen. Kutools for Excel Yn cynnig dros 300 o nodweddion uwch i hybu cynhyrchiant ac arbed amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905

Office Tab Yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (40)
No ratings yet. Be the first to rate!
Cyril
Cyril
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.
Reply
Report
0
0
Sunny
Sunny     Cyril
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.
Reply
Report
0
0
Bindu Narula
Bindu Narula
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?
Reply
0
0
Luis Ron
Luis Ron
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.
Reply
0
0
mehmet
mehmet
This comment was minimized by the moderator on the site
yes it's not work.
Reply
0
0
Dan
Dan
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.
Reply
0
0
Robin
Robin
This comment was minimized by the moderator on the site
This is working for me, but the file extension is not changing. Any tips?
Reply
0
0
Pascal
Pascal     Robin
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.
Reply
0
0
stevehurm
stevehurm     Robin
This comment was minimized by the moderator on the site
Same for me -- all files remain .csv.
Reply
Report
0
0
Sunny
Sunny     stevehurm
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.
Reply
Report
0
0
Dixon
Dixon
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?
Reply
0
0
Jason
Jason
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
Reply
0
0
Sunny
Sunny     Jason
This comment was minimized by the moderator on the site
Thanks for your reminder, I have updated it, thanks again.
Reply
Report
0
0
aaqil Rahman
aaqil Rahman
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
Reply
0
0
viper
viper
This comment was minimized by the moderator on the site
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.
Reply
0
0
Sunny
Sunny     viper
This comment was minimized by the moderator on the site
Hi, viper, after conversion, you can format them back, select the dates and right click to choose Format Cells from context menu, in the Format Celld dialog, under Number tab, click Custom in the Category list, then type mm-dd-yyyy into the textbox in right section. Or if you have Kutools for Excel, applying the Apply Date Formatting to change the date format as you need.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations