如何编写宏批量打开CSV文件、执行分列并另存为新文件？

It looks like you're almost there—you just need to insert the TextToColumns operation right after opening each CSV file before saving it as a new workbook. Here's the complete, updated code with that step integrated, plus explanations of the key parts:

Sub CSVtoXLS()
    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
    
    ' Select the target folder
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xSPath = xFd.SelectedItems(1) & "\"
    Else
        MsgBox "No folder selected. Exiting macro.", vbExclamation
        Exit Sub
    End If
    
    ' Loop through all CSV files in the folder
    xCSVFile = Dir(xSPath & "*.csv")
    Do While xCSVFile <> ""
        ' Open the CSV file
        Workbooks.Open Filename:=xSPath & xCSVFile
        
        ' --- NEW: Apply Text to Columns ---
        With ActiveSheet.UsedRange
            .TextToColumns _
                Destination:=.Cells(1), _
                DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ' Handles quoted values containing delimiters
                ConsecutiveDelimiter:=False, _
                Comma:=True ' Adjust this for your delimiter (e.g., Semicolon:=True, Tab:=True)
                ' Use Other:="|" if your delimiter is a custom character like a pipe
        End With
        
        ' Save as XLSX (modify for legacy .xls if needed)
        ActiveWorkbook.SaveAs _
            Filename:=xSPath & Replace(xCSVFile, ".csv", ".xlsx"), _
            FileFormat:=xlOpenXMLWorkbook
        
        ' Close the processed file
        ActiveWorkbook.Close
        
        ' Update status bar for progress feedback
        Application.StatusBar = "Processed: " & xCSVFile
        xCSVFile = Dir ' Move to next CSV file
    Loop
    
    ' Cleanup and finish
    Application.StatusBar = False
    Application.DisplayAlerts = True
    MsgBox "Batch processing complete!", vbInformation
End Sub

Key Adjustments You Might Need: #

• Delimiter: If your CSV uses a semicolon, tab, or custom character instead of a comma, tweak the TextToColumns parameters. For example, switch Comma:=True to Semicolon:=True or add Other:="|" for a pipe delimiter.
• File Format: To save as legacy .xls instead of .xlsx, change FileFormat:=xlOpenXMLWorkbook to FileFormat:=xlExcel8.
• Target Range: If you only want to split a specific column (e.g., column A), replace ActiveSheet.UsedRange with ActiveSheet.Range("A:A").

Why This Works: #

The TextToColumns method runs immediately after opening each CSV, while the file is active. This splits the single-column CSV data into proper columns based on your delimiter before saving the workbook. Your original folder selection, file looping, and save logic remain intact—we just inserted the critical splitting step in the right place.

内容的提问来源于stack exchange，提问作者Dan Howles