Question

This question already has answers here:

Export each sheet to a separate csv file (2 answers)

Closed 9 years ago.

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:	est"
   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:	est"

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

Answer 2

我也存在同样的问题。工作表格一中的数据需要作为单独CSV文档予以保存。

这里,我以指挥纽顿为后盾。

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

这对我的工作:-

Answer 3

这是你试图做的吗?

Option Explicit

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

    SaveToDirectory = "H:	est"

    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

Answer 4

找到最佳途径是记录宏观和采取确切步骤,并了解传统做法的代号。然后,你可以去掉你想要做的普通话题(即档案名称和装饰)

Answer 5

上述法典完美无缺地运作;由此产生的档案无法得到延期。 • 2天前的Tentigh

我在法典中增加了以下内容,并节省了我的卷宗。感谢这种编号。它都是按预期运作的。

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

友情链接