vba - Excel: macro to export worksheet as CSV file without leaving my current Excel sheet

Question

There are a lot of questions here to create a macro to save a worksheet as a CSV file. All the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like this:

Sub SaveAsCSV()
    ActiveWorkbook.SaveAs FileFormat:=clCSV, CreateBackup:=False
End Sub

This is a great answer, but I want to do an export instead of Save As. When the SaveAs is executed it causes me two annoyances:

• My current working file becomes a CSV file. I'd like to continue working in my original .xlsm file, but to export the contents of the current worksheet to a CSV file with the same name.
• A dialog appears asking me confirm that I'd like to rewrite the CSV file.

Is it possible to just export the current worksheet as a file, but to continue working in my original file?

question from:https://stackoverflow.com/questions/37037934/excel-macro-to-export-worksheet-as-csv-file-without-leaving-my-current-excel-sh

Answer 1

@NathanClement was a bit faster. Yet, here is the complete code (slightly more elaborate):

Option Explicit

Public Sub ExportWorksheetAndSaveAsCSV()

Dim wbkExport As Workbook
Dim shtToExport As Worksheet

Set shtToExport = ThisWorkbook.Worksheets("Sheet1")     'Sheet to export as CSV
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False                       'Possibly overwrite without asking
wbkExport.SaveAs Filename:="C:mpest.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

End Sub