- To post as a guest, your comment is unpublished.· 2 months agoSo, 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.
Como converter em lote vários arquivos do Excel em arquivos CSV no Excel?
No Excel, podemos converter a pasta de trabalho em arquivo CSV com a função Salvar como, mas você poderia saber como converter em lote vários arquivos do Excel em arquivos CSV separados? Neste artigo, apresento um código VBA para converter em lote todos os arquivos do Excel em uma pasta para arquivos CSV no Excel.
No Excel, não há nenhuma função incorporada que resolva esse trabalho rapidamente, exceto o VBA.
1. Ative o Excel e pressione Alt + F11 chaves abertas Microsoft Visual Basic para Aplicações janela.
2. Clique inserção > Módulo para criar um novo módulo.
3. Copie o código abaixo e cole-o na nova janela do módulo.
VBA: conversão em lote de arquivos do Excel para 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. Pressione F5 , selecione a pasta que contém os arquivos Excel que deseja converter em arquivos CSV na primeira caixa de diálogo pop-up.
5. Clique OKe, na segunda caixa de diálogo pop-up, selecione a pasta para colocar os arquivos CSV.
6. Clique OK, agora os arquivos do Excel na pasta foram convertidos em arquivos CSV e salvos em outra pasta.
Como sabemos, só podemos converter a pasta de trabalho inteira em um arquivo CSV no Excel com sua função Salvar como. Mas, às vezes, você deseja converter a única folha em arquivo CSV, neste caso, o Dividir a pasta de trabalho utilidade de Kutools for Excel pode ajudá-lo.
|Kutools for Excel, com mais de 300 funções úteis, tornam seus trabalhos mais fáceis.|
Depois de instalar Kutools para Excel, faça o seguinte:（Baixe agora o Kutools para Excel gratuitamente!)
2. No Dividir a pasta de trabalho caixa de diálogo, marque o nome da folha que deseja dividir (todas as folhas são marcadas por padrão), marque Salvar como digite, escolha CSV (Macintosh) (* .CSV) na lista suspensa.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agoHello, 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.· 3 months agoThis is amazing. Thank-you!
- To post as a guest, your comment is unpublished.· 4 months agotop thanks :)
- To post as a guest, your comment is unpublished.· 1 years agoI 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.· 1 years agoThank 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.· 2 years agoAnother 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:
Selection.NumberFormat = "@"
- To post as a guest, your comment is unpublished.· 2 years agoWorks 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.· 1 years agoHave you found a way around this issue?
- To post as a guest, your comment is unpublished.· 1 years agoCarol,
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.· 1 years agoThank you so much! This has saved me so much time!!
- To post as a guest, your comment is unpublished.· 2 years agoTy it really works dear !!