vba - 使用 VB 将 Excel 工作表保存为文件名+工作表名称的 CSV 文件

Question

这个问题已经有答案了:

Export each sheet to a separate csv file (2 个回答)

已关闭 9 年前。

我对 VB 编码非常陌生，我正在尝试将多个 Excel 文件工作表保存到 csv，我不知道如何对多个工作表执行此操作，但我找到了一种对单个文件执行此操作的方法。我在这个网站上找到了对我想做的事情非常有用的代码，唯一的问题是文件是用工作表名称保存的，但我试图用原始文件和工作表名称保存它们，例如 filename_worksheet name ，我尝试自己这样做，但不断出错，您能告诉我我做错了什么吗？

我使用的代码如下:

   Public Sub SaveWorksheetsAsCsv()

   Dim WS As Excel.Worksheet
   Dim SaveToDirectory As String

   Dim CurrentWorkbook As String
   Dim CurrentFormat As Long

   CurrentWorkbook = ThisWorkbook.FullName
   CurrentFormat = ThisWorkbook.FileFormat
   ' Store current details for the workbook
   SaveToDirectory = "H:\test\"
   For Each WS In ThisWorkbook.Worksheets
   WS.SaveAs SaveToDirectory & WS.Name, xlCSV
   Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub 

Answer 1

我想这就是你想要的......

Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = "H:\test\"

For Each WS In Application.ActiveWorkbook.Worksheets
    WS.SaveAs SaveToDirectory & WS.Name, xlCSV
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub