Export more sheets as separate .csv files

Question

I'm trying to export two sheet in two different csv file using the code presented below:

import_bt.SaveAs Filename:="D:\Temp\import_bt.csv", FileFormat:=xlCSV, CreateBackup:=False, Local:=False
import_brd.SaveAs Filename:="D:\Temp\import_brd.csv", FileFormat:=xlCSV, CreateBackup:=False, Local:=True

The code works fine but I've an issue, it saves also my entire file as the last .csv file named import_brd.

Does this answer your question? [VBA Saving single sheet as CSV (not whole workbook)](https://stackoverflow.com/questions/34155718/vba-saving-single-sheet-as-csv-not-whole-workbook) — FunThomas, Jun 30 '20 at 07:36

FaneDuru · Accepted Answer · 2020-06-30T09:04:23.223

In order to be able to copy sheets, without changing the active document name, you cannot proceed as you tried. It will always save the sheet, but the workbook will be Saved As your last allocated (csv) name.

Try this code, please:

Sub testSaveSheetAsCSV()
   Dim import_bt As Worksheet, import_brd As Worksheet, wb As Workbook

    'I used dummy sheets name only for testing reason. Please, use your real ones:
    Set import_bt = Sheets("Test") 'use here your necessary sheet
    Set import_brd = Sheets("Teste") 'use here your necessary sheet
     Set wb = Workbooks.aDD
     import_bt.Copy Before:=wb.Worksheets(1)
     wb.SaveAs fileName:=ThisWorkbook.path & "\import_bt.csv", FileFormat:=xlCSV, CreateBackup:=False, Local:=False
     import_brd.Copy Before:=wb.Worksheets(1) 'it will save its first sheet
     wb.SaveAs fileName:=ThisWorkbook.path & "\import_brd.csv", FileFormat:=xlCSV, CreateBackup:=False, Local:=True
     wb.Close False
End Sub

It adds a new workbook, copy the sheet necessary to be transformed in CSV, before the existing first one of the newly created workbook and save it (AS CSV) after that. It repeats the steps for the second sheet and closes the temporary workbook.

If you need to avoid the warning regarding overwriting of the existing file(s) (if the case), the code can be adapted to avoid it. Only in case you want playing with the code, or updating the existing csv files with the last necessary ones.

Export more sheets as separate .csv files

1 Answers1