vba 使用VB将excel工作表保存为带有文件名+工作表名称的CSV文件

Saving excel worksheet to CSV files with filename+worksheet name using VB

提问by Achak

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?

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

The code I am using is as follows:

我使用的代码如下：

   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 

回答by Ben Strombeck

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 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

回答by Tudor

I had a similar problem. Data in a worksheet I needed to save as a separate CSV file.

我有一个类似的问题。我需要将工作表中的数据另存为单独的 CSV 文件。

Here's my code behind a command button

这是我在命令按钮后面的代码

---

---

Private Sub cmdSave()
    Dim sFileName As String
    Dim WB As Workbook

    Application.DisplayAlerts = False

    sFileName = "MyFileName.csv"
    'Copy the contents of required sheet ready to paste into the new CSV
    Sheets(1).Range("A1:T85").Copy 'Define your own range

    'Open a new XLS workbook, save it as the file name
    Set WB = Workbooks.Add
    With WB
        .Title = "MyTitle"
        .Subject = "MySubject"
        .Sheets(1).Select
        ActiveSheet.Paste
        .SaveAs "MyDirectory\" & sFileName, xlCSV
        .Close
    End With

    Application.DisplayAlerts = True
End Sub

---

---

This works for me :-)

这对我有用:-)

回答by Siddharth Rout

Is this what you are trying?

这是你正在尝试的吗？

Option Explicit

Public Sub SaveWorksheetsAsCsv()
    Dim WS As Worksheet
    Dim SaveToDirectory As String, newName As String

    SaveToDirectory = "H:\test\"

    For Each WS In ThisWorkbook.Worksheets
        newName = GetBookName(ThisWorkbook.Name) & "_" & WS.Name
        WS.Copy
        ActiveWorkbook.SaveAs SaveToDirectory & newName, xlCSV
        ActiveWorkbook.Close Savechanges:=False
    Next
End Sub

Function GetBookName(strwb As String) As String
    GetBookName = Left(strwb, (InStrRev(strwb, ".", -1, vbTextCompare) - 1))
End Function

回答by Bek Raupov

Best way to find out is to record the macro and perform the exact steps and see what VBA code it generates. you can then go and replace the bits you want to make generic (i.e. file names and stuff)

找出问题的最佳方法是记录宏并执行确切的步骤，然后查看它生成的 VBA 代码。然后你可以去替换你想要通用的位（即文件名和东西）

回答by RazaJ77

The code above works perfectly with one minor flaw; the resulting file is not saved with a .csv extension. – Tensigh 2 days ago

上面的代码完美运行，但有一个小缺陷；生成的文件不会以 .csv 扩展名保存。– Tensigh 2 天前

I added the following to code and it saved my file as a csv. Thanks for this bit of code.It all worked as expected.

我在代码中添加了以下内容，并将我的文件保存为 csv。感谢这段代码。一切都按预期工作。

ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV