使用VB [文件夹]将excel工作表保存到文件名称为CSV文件

发布时间：2020-12-17 00:10:34 所属栏目：大数据来源：网络整理

导读：参见英文答案 Export each sheet to a separate csv file2 我非常新的VB编码,我试图保存多个excel文件工作表到csv,我不知道这样做为多个表,但我找到一种方法来做单个文件.我发现这个网站上的代码对我正在尝试做的非常有用,只有问题是文件保存的工作表名称,但

参见英文答案 > Export each sheet to a separate csv file2
我非常新的VB编码,我试图保存多个excel文件工作表到csv,我不知道这样做为多个表,但我找到一种方法来做单个文件.我发现这个网站上的代码对我正在尝试做的非常有用,只有问题是文件保存的工作表名称,但我试图保存它们与原始文件和工作表名称,如filename_worksheet名称,我试图自己做,但不断收到错误,你能告诉我做错了什么吗？

我使用的代码如下：

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

我想这是你想要的

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
    Sheets(WS.Name).Copy
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv",FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Activate
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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!