Single sheet in .xlsm file into CSV

Question

I created an Excel sheet which has a macro to copy the data from sheet1 to sheet2.

I want to save sheet2 in a CSV format.

I tried this code

Sub saveAsCSV()
Application.DisplayAlerts = False

ThisWorkbook.Sheets("Sheet2").SaveAs ThisWorkbook.Path & "/" & "userimport" & ".csv", FileFormat:=6

Application.DisplayAlerts = True
End Sub

This code saves the entire workbook in a CSV format. I want to delete the sheet1 and save only the sheet2 as csv.

You want to delete Sheet1 in .xlsm file and you don't need it anymore? Then delete it before you save. If you want to keep it in the .xlsm file then copy Sheet2 to a new workbook and save it. — Egan Wolf
Refer this. This is what it takes to cycle through all the sheets and make each csv. However, you can also target one sheet. — Dy.Lee

Dy.Lee Dy.Lee · Accepted Answer · 2021-01-16T06:54:29

Sub ExportSheetsToCSV()

    Dim Ws As Worksheet
    Dim xcsvFile As String
    Dim rngDB As Range
    Dim r As Long, c As Integer

    'For Each Ws In Worksheets
    Set Ws = Sheets(2)
        xcsvFile = CurDir & "\" & Ws.Name & ".csv"
        With Ws
            r = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            c = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            Set rngDB = .Range("a1", .Cells(r, c))
        End With
        TransToCSV xcsvFile, rngDB
    'Next
    MsgBox ("Files Saved Successfully")
End Sub


Sub TransToCSV(myfile As String, rng As Range)

    Dim vDB, vR() As String, vTxt()
    Dim i As Long, n As Long, j As Integer
    Dim objStream
    Dim strTxt As String

    Set objStream = CreateObject("ADODB.Stream")
    vDB = rng
    For i = 1 To UBound(vDB, 1)
        n = n + 1
        ReDim vR(1 To UBound(vDB, 2))
        For j = 1 To UBound(vDB, 2)
            vR(j) = vDB(i, j)
        Next j
        ReDim Preserve vTxt(1 To n)
        vTxt(n) = Join(vR, ",")
    Next i
    strTxt = Join(vTxt, vbCrLf)
    With objStream
        '.Charset = "utf-8"
        .Open
        .WriteText strTxt
        .SaveToFile myfile, 2
        .Close
    End With
    Set objStream = Nothing

End Sub

Single sheet in .xlsm file into CSV

1 Answers