Excel宏多重表格到CSV [英] Excel Macro Multiple Sheets to CSV

问题描述

我有一个我在Excel中运行的宏，将49张单张分成单独的CSV文件。

但是，它正在第7行被抓住

 应用程序.ActiveWorkbook.SaveAs文件名：= xcsvFile，_ 
 FileFormat：= xlCSV，CreateBackup：= False 
  

这里是周围的代码：

  Sub ExportSheetsToCSV（）
 
 Dim xWs As Worksheet 
对于每个xWs在Application.ActiveWorkbook.Worksheets 
 
 xWs.Copy 
 
 Dim xcsvFile As String 
 xcsvFile = CurDir& \& xWs.Name& .csv
 
 Application.ActiveWorkbook.SaveAs文件名：= xcsvFile，_ 
 FileFormat：= xlCSV，CreateBackup：= False 
 
 Application.ActiveWorkbook.Saved = True 
 Application.ActiveWorkbook.Close 
 
下一个
 
 End Sub 
  

解决方案

考虑这个。

 子测试（）
 
 Dim ws As Worksheet 
 Dim GetSheetName As String 
 
对于每个ws在ThisWorkbook.Worksheets 
如果ws.Name<> Sheet1然后'假设有一张表不要保存为CSV 
 
 ws.Select 
 GetSheetName = ActiveSheet.Name 
设置shtToExport = ActiveSheet'导出为CSV 
设置wbkExport = Application.Workbooks.Add 
 shtToExport.Copy之前：= wbkExport.Worksheets（wbkExport.Worksheets.Count）
 Application.DisplayAlerts = False'可能覆盖没有要求
 wbkExport.SaveAs文件名：=C：\your_path_here\Desktop\& GetSheetName& .csv，FileFormat：= xlCSV 
 Application.DisplayAlerts = True 
 wbkExport.Close SaveChanges：= False 
 
 End If 
 Next ws 
 
 End Sub 
  

I have a macro that I am running in Excel to separate 49 sheets into individual CSV files.

However, it is getting caught up on line 7

Application.ActiveWorkbook.SaveAs Filename: = xcsvFile, _
    FileFormat: = xlCSV, CreateBackup: = False

Here's the surrounding code:

Sub ExportSheetsToCSV()

    Dim xWs As Worksheet
    For Each xWs In Application.ActiveWorkbook.Worksheets

        xWs.Copy

        Dim xcsvFile As String
        xcsvFile = CurDir & "\" & xWs.Name & ".csv"

        Application.ActiveWorkbook.SaveAs Filename: = xcsvFile, _
            FileFormat: = xlCSV, CreateBackup: = False

        Application.ActiveWorkbook.Saved = True
        Application.ActiveWorkbook.Close

    Next

End Sub

解决方案

Consider this.

Sub test()

    Dim ws As Worksheet
    Dim GetSheetName As String

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then ' Assuming there is one sheet that you DON'T want to save as a CSV

        ws.Select
        GetSheetName = ActiveSheet.Name
            Set shtToExport = ActiveSheet     ' Sheet to export as CSV
                Set wbkExport = Application.Workbooks.Add
                shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
                Application.DisplayAlerts = False       ' Possibly overwrite without asking
                wbkExport.SaveAs Filename:="C:\your_path_here\Desktop\" & GetSheetName & ".csv", FileFormat:=xlCSV
                Application.DisplayAlerts = True
                wbkExport.Close SaveChanges:=False

        End If
    Next ws

End Sub

这篇关于Excel宏多重表格到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！