CODEWITHSUNDEEP
excelvbacsv
Tommy O'Dell
Tommy O'Dell

Reputation: 7109

How to export multiple worksheets to CSV (without saving over the current worksheet)

I'm trying to export a number of worksheets in my workbook to .csv via some code like this:

Sub Export_To_CSV(exportPath As String)    

    Dim filePath As String 

    For Each WS In ThisWorkbook.Worksheets

            filePath = exportPath & "(" & WS.Name & ").dat"
            WS.SaveAs Filename:=filePath, FileFormat:=xlCSV

    Next 
End Sub

The problem is that this saves over the current .xlsm file that I have open.

How can I get it to export the .csv without changing the name of the current file?

I thought SaveCopyAs would do the trick, but it only applies to a workbook and not a worksheet.

Upvotes: 5

Views: 11319

Answers (2)

WillemG
WillemG

Reputation: 31

You could also first save the workbook, then save off the worksheets and finally close the workbook without having to save (again):

' ... your code here
    ActiveWorkbook.Save
    Application.DisplayAlerts = False

Dim WS As Worksheet

For Each WS In Worksheets
        Filepath = "c:\temp\" & WS.Name & ".dat"
        WS.SaveAs Filename:=Filepath, FileFormat:=xlCSV, _
        CreateBackup:=False
Next
    ActiveWorkbook.Close
' ... your code here

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Here comes my idea which could help you... Add this part of code instead you current for...next section:

'...your code here
Dim tmpWS As Worksheet
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets

        filePath = exportPath & "(" & WS.Name & ").dat"

        WS.Copy
        Set tmpWS = ActiveSheet
        tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV
        tmpWS.Parent.Close False
Next
Application.DisplayAlerts = True
'...your code here

Logic of the code? First, it makes a copy of your sheet into temporary workbook, next it saves new sheet as CSV file and finally, it closes temporary workbook. Moreover, I added Application.DisplayAlerts instructions that your code overwrites .csv file without asking if file already exists.

Upvotes: 8

Related Questions