CODEWITHSUNDEEP
vbacsv
lakshmen
lakshmen

Reputation: 29084

Export Sheets as CSVs overwrites the original filename VBA

I am saving each sheet as csv and when i save each sheet as csv, it overwrites the the original template filename to the new csv name. It changes from ".xlsm" to ".csv"

How do i avoid that?

My code looks like this:

Sub ExportToCSVs()
    Dim ws As Worksheet
    Dim nm As String
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "Control" Then
            ws.Select
            nm = ws.Name
            filenameconvention = "\\Lakesh\ExportToCSVs\" & nm
            ActiveSheet.SaveAs Filename:="\\Lakesh\ExportToCSVs\" & nm & ".csv", _
                 FileFormat:=xlCSV, CreateBackup:=False
        End If
    Next ws
    Sheets("Control").Activate
    Application.ScreenUpdating = True
    MsgBox "Csvs Created!"
End Sub

Need some guidance on this.

Upvotes: 0

Views: 26

Answers (1)

Turtle
Turtle

Reputation: 111

It helps to copy the worksheet before saving it. ActiveSheet.Copy ActiveSheet.SaveAs Filename:="\\Lakesh\ExportToCSVs\" & nm & ".csv", _ FileFormat:=xlCSV, CreateBackup:=False

Upvotes: 1

Related Questions