How to save excel file in .csv format?-CodePudding

The code below creates and saves all the excel sheets from "Test_Main" into separate new workbooks with file extension .xlsx and I want it to save the workbook in .csv format. Could someone please alter my current code to do the required job ? Thanks in advance :)

Sub Workbook()

Dim a As Integer
Dim ws As Worksheet
Dim wb As Workbook

a = ThisWorkbook.Worksheets.Count  'counts all the sheets

For i = 1 To a 'loops for all sheets
If ThisWorkbook.Worksheets(i).Name <> "Test_Main" Then 'rule out the main sheet

Set wb = Workbooks.Add
ThisWorkbook.Worksheets(i).Copy before:=wb.Worksheets(1) 'new workbook has 1 worksheet by deafult
wb.SaveAs "H:\IT\Melissa\Essengeld\TEST\" & ThisWorkbook.Worksheets(i).Name

wb.Close savechages = True

End If
Next i

ThisWorkbook.Activate
ThisWorkbook.Sheets(1).Activate
ThisWorkbook.Sheets(1).Cells(1, 1).Select

MsgBox ("Task Completed")
End Sub

CodePudding user response：

Microsoft answer question here -> https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

CodePudding user response：

I took code from to export a sheet from here

Sub exportSheet(sh As Worksheet, csvFilename As String)

    Dim wbNew As Workbook
    Set wbNew = Workbooks.Add
    
    Dim wsNew As Worksheet

    With wbNew
        sh.Copy wbNew.Sheets(1)
        Set wsNew = wbNew.Sheets(1)
        .SaveAs Filename:=csvFilename, _
            FileFormat:=xlCSVMSDOS, CreateBackup:=False
        .Close False
    End With

End Sub

and used it in your code like that

Sub exportToCSV()

    Dim a As Integer
    Dim ws As Worksheet
    Dim wb As Workbook

    a = ThisWorkbook.Worksheets.Count  'counts all the sheets

    For i = 1 To a 'loops for all sheets
        If ThisWorkbook.Worksheets(i).Name <> "Test_Main" Then 'rule out the main sheet

'            Set wb = Workbooks.Add
'            ThisWorkbook.Worksheets(i).Copy before:=wb.Worksheets(1) 'new workbook has 1 worksheet by deafult
'            wb.SaveAs "H:\IT\Melissa\Essengeld\TEST\" & ThisWorkbook.Worksheets(i).Name
'
'            wb.Close savechages = True
             exportSheet ThisWorkbook.Worksheets(i), "H:\IT\Melissa\Essengeld\TEST\" & ThisWorkbook.Worksheets(i).Name
        End If
    Next i

    ThisWorkbook.Activate
    ThisWorkbook.Sheets(1).Activate
    ThisWorkbook.Sheets(1).Cells(1, 1).Select

    MsgBox ("Task Completed")
End Sub

CodePudding user response：

I found the solution:

wb.SaveAs "H:\IT\Melissa\Essengeld\TEST" & ThisWorkbook.Worksheets(i).Name , FileFormat:=xlCSV