1.启用Excel，然后按 Alt + F11键 钥匙打开 Microsoft Visual Basic应用程序 窗口。
2。 点击 插页 > 模块 创建一个新的模块。
3.复制以下代码并将其粘贴到新的“模块”窗口中。
VBA：将Excel文件批量转换为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。 按 F5 键，在第一个弹出对话框中选择包含要转换为CSV文件的Excel文件的文件夹。
5。 点击 OK，然后在第二个弹出对话框中，选择放置CSV文件的文件夹。
6。 点击 OK，现在文件夹中的Excel文件已转换为CSV文件并保存在另一个文件夹中。
使用Kutools for Excel将工作簿表转换为单独的CSV文件
众所周知，我们只能使用Excel的另存为功能将整个工作簿转换为一个CSV文件。 但是有时候，您希望将单个工作表转换为CSV文件，在这种情况下， 拆分工作簿 实用程序 Kutools for Excel 可以帮你。
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!
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?
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:
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).
