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

csv, excel, vba

Solution

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.

Problem

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

Original source