将多个工作表导出到指定目录中的CSV文件 [英] Export multiple worksheets to CSV files in a specified directory

问题描述

我正在尝试执行以下操作:

I'm trying to do the following:

1. 将工作簿中的特定工作表(任何包含上载"的工作表名称)导出/复制到特定文件目录.
2. 我既不想更改这些工作表名称，也不想更改工作簿的名称.
3. 每个工作表的文件名都是一致的，因此每当我运行宏时都可以替换目录中的文件.可以出现一个对话框，询问是否确定要替换每个文件，这是可以的.
4. 我不希望打开新创建的CSV或任何其他文件.

---

Sub SheetsToCSV() 

    'Jerry Beaucaire (1/25/2010), updated (8/15/2015)  
    'Save each sheet to an individual CSV file  
    Dim ws As Worksheet, fPATH As String

    Application.ScreenUpdating = False      'speed up macro  
    Application.DisplayAlerts = False       'automatically overwrite old files  
    fPATH = "C:\2015\CSV\"                  'path to save into, remember the final \ in this string  

    For Each ws In Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:=fPATH & ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Close
    Next ws

    Application.ScreenUpdating = True  
End Sub

推荐答案

您只需要在所有工作表中添加一个简单的循环并测试名称即可.

You just need to add a simple loop through all worksheets and test the name.

尝试一下:-

Sub COPYSelectedSheetsToCSV()

  Dim ws As Worksheet

  'In case something goes wrong      
  On Error GoTo COPYSelectedSheetsToCSVZ

  'Loop through all worksheets
  For Each ws In ActiveWorkbook.Sheets

    'Does the name contain "Upload" 
    If InStr(1, ws.Name, "Upload") > 0 Then

      'Make the worksheet active
      ws.Select

      'Save it to CSV
      ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/" & ws.Name & ".csv", _
          FileFormat:=xlCSV, CreateBackup:=False

    End If

  Next

COPYSelectedSheetsToCSVX:

  'Clean up the memory usage
  Set ws = Nothing

  Exit Sub

COPYSelectedSheetsToCSVZ:
  MsgBox Err.Number & " - " & Err.Description
  Resume COPYSelectedSheetsToCSVX

End Sub

这篇关于将多个工作表导出到指定目录中的CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！