使用VB将Excel工作表保存到CSV文件，并使用文件名+工作表名称 [英] Saving excel worksheet to CSV files with filename+worksheet name using VB

问题描述

我是非常新的VB编码，我试图保存多个excel文件工作表到csv，我不知道这样做多个工作表，但我发现一个方法来做单个文件。我发现在这个网站上的代码，这是非常有用的，我试图做，只有问题是文件与工作表名称保存，但我试图保存他们与原始文件和工作表名称如 filename_worksheet name ，我试图自己做，但不断收到错误，你能告诉我我做错了什么吗？

I am very new with VB coding, I am trying to save multiple excel file worksheets to csv, I don't know to do this for multiple sheets, but I found a way to do for single file. I have found code on this site which are very useful for what I am trying to do, only problem is the files are saved with the worksheet name but I am trying to save them with the original file and worksheet name such as filename_worksheet name, I tried to do that myself but keep getting error, could you please advise what I am doing wrong?

我使用的是如下：

   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 

推荐答案

I认为这是你想要的...

I think this is what you want...

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

这篇关于使用VB将Excel工作表保存到CSV文件，并使用文件名+工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！