遍历文件夹下.xlsx文件，将所有sheet文件拆分为新的.csv

最新推荐文章于 2024-09-10 10:56:36 发布

weixin_42117570

最新推荐文章于 2024-09-10 10:56:36 发布

阅读量112

点赞数

文章标签：开发语言

本文链接：https://blog.csdn.net/weixin_42117570/article/details/129852633

版权

这个VBA宏SubSplitSheetsToCSV用于遍历指定文件夹中的所有.xlsx文件，对每个文件中的每个工作表进行操作，将每个工作表单独保存为同名的CSV文件。用户首先通过文件对话框选择包含.xlsx文件的文件夹，然后宏会自动处理该文件夹中的所有文件，无需逐一手动操作。

摘要由CSDN通过智能技术生成

**Sub SplitSheetsToCSV()
    Dim wb As Workbook
    Dim savePath As String
    Dim sheet As Worksheet
    
    'Open folder dialog to select folder containing .xlsx files
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing .xlsx files"
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub
        folderPath = .SelectedItems(1)
    End With
    
    'Loop through all .xlsx files in selected folder
    file = Dir(folderPath & "\*.xlsx")
    Do While file <> ""
        'Open .xlsx file
        Set wb = Workbooks.Open(folderPath & "\" & file)
        
        'Loop through all sheets in .xlsx file
        For Each sheet In wb.Sheets
            'Save sheet as .csv file with same name as sheet
            savePath = folderPath & "\" & Replace(file, ".xlsx", "") & "_" & sheet.Name & ".csv"
            sheet.SaveAs Filename:=savePath, FileFormat:=xlCSV, CreateBackup:=False
        Next sheet
        
        'Close .xlsx file
        wb.Close SaveChanges:=False
        
        'Get next .xlsx file in folder
        file = Dir
    Loop
End Sub
**