Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas baisc ilchomhaid Excel a thiontú go comhaid CSV in Excel?

In Excel, is féidir linn an leabhar oibre a thiontú go comhad CSV leis an bhfeidhm Save as, ach an bhféadfá a fhios a bheith agat conas baisc ilchomhaid Excel a thiontú go comhaid CSV ar leithligh? San Airteagal seo, tugaim isteach cód VBA chun gach comhad Excel i bhfillteán a thiontú go comhaid CSV in Excel.

Baisc comhaid Excel d’fhillteán a thiontú go comhaid CSV le VBA

Tiontaigh bileoga de leabhar oibre i gcomhaid CSV ar leithligh le Kutools for Excelsmaoineamh maith3


Baisc comhaid Excel d’fhillteán a thiontú go comhaid CSV le VBA

In Excel, níl aon fheidhm ionsuite ann chun an post seo a réiteach go tapa seachas VBA.

1. Cumasaigh Excel, agus brúigh Alt + F11 eochracha oscailte Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. cliceáil Ionsáigh > Modúil chun Modúl nua a chruthú.

3. Cóipeáil thíos an cód agus greamaigh iad chuig fuinneog nua an Mhodúil.

VBA: Baisc comhaid Excel a thiontú go 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. Brúigh F5 eochair, roghnaigh san fhillteán tá na comhaid Excel a theastaíonn uait a thiontú go comhaid CSV sa chéad dialóg popping.
baisc doc go csv 1

5. cliceáil OK, ansin sa dara dialóg popping, roghnaigh an fillteán chun na comhaid CSV a chur.
baisc doc go csv 2

6. cliceáil OK, anois tá na comhaid Excel san fhillteán tiontaithe go comhaid CSV agus sábháladh iad i bhfillteán eile.
baisc doc go csv 3


Tiontaigh bileoga de leabhar oibre i gcomhaid CSV ar leithligh le Kutools for Excel

Mar is eol dúinn, ní féidir linn an leabhar oibre iomlán a thiontú go comhad CSV amháin in Excel lena fheidhm Save As. Ach i roinnt uaireanta, ba mhaith leat an bhileog aonair a thiontú go comhad CSV, sa chás seo, an Leabhar Oibre Scoilt fóntais de Kutools le haghaidh Excel Is féidir cabhrú leat.

Kutools le haghaidh Excel, le níos mó ná 300 feidhmeanna úsáideacha, déanann sé do phoist níos éasca. 

Tar éis a shuiteáil Kutools for Excel, déan mar atá thíos le do thoil:Download Kutools Íoslódáil saor in aisce do Excel Anois!)

1. Cumasaigh an leabhar oibre a theastaíonn uait a bhileoga a thiontú mar chomhaid CSV ar leithligh, cliceáil Kutools Plus > Leabhar Oibre > Leabhar Oibre Scoilt.
baisc doc go csv 4

2. Sa Leabhar Oibre Scoilt dialóg, seiceáil ainm na bileoige a theastaíonn uait a roinnt (déantar gach bileog a sheiceáil de réir réamhshocraithe), seiceáil Ach amháin mar cineál, roghnaigh CSV (Macintosh) (* .CSV) ón liosta anuas.
baisc doc go csv 5

3. cliceáil Scoilt a pop amach a Brabhsáil Chun Fillteán dialóg, roghnaigh nó cruthaigh fillteán chun na comhaid CSV a chur.
baisc doc go csv 6

4. cliceáil OK, anois tá an leabhar oibre roinnte mar chomhaid CSV ar leithligh.
baisc doc go csv 7


Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

  • Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
  • Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
  • Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
  • Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
  • Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
  • Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
  • Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
  • Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.
cluaisín kte 201905

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
bun officetab
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 · 11 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 · 10 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 · 2 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 !!