Multi worksheet export one to CSV and autosave with no prompt

Question

haven't done much with macros but some. I have searched and found some similar of what I need to do but not exact and wonder if anyone can help.
For work I have a excel workbook with multiple sheets, and one sheet I create a copy and save as CSV because a program we work with will only accept txt or csv format so have to save every time we want to import data. I am basically looking to have that one data sheet to auto save on a csv file in the folder that will save without the prompt so the program can just auto import the data from it.

Thanks for any help

Possible duplicate of [Saving excel worksheet to CSV files with filename+worksheet name using VB](http://stackoverflow.com/questions/10551353/saving-excel-worksheet-to-csv-files-with-filenameworksheet-name-using-vb) If you don't like prompts you can incorporate `Application.DisplayAlerts = False` somewhere into that solution. — Ralph, Apr 21 '17 at 09:37

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Simplest solution would be to use of Worksheet.Copy:

If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet object that contains the copied Worksheet object. The newly created workbook holds the Application.ActiveWorkbook Property (Excel) property and contains a single worksheet.

The single worksheet retains the Worksheet.Name Property (Excel) and Worksheet.CodeName Property (Excel) properties of the source worksheet.

If the copied worksheet held a worksheet code sheet in a VBA project, that is also carried into the new workbook.

Try:

Option Explicit

Sub Main()
    ' Call your Sub
    CopySheet2CSV ThisWorkbook.Sheets("Tabelle1")
End Sub

Sub CopySheet2CSV(ws As Worksheet)
    ws.Copy
    ActiveWorkbook.SaveAs Filename:="C:\Users\b036081\NoBackupData\" & ws.Name & ".csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close Savechanges:=False
End Sub

Capitalize on the fact that .Copy conveniently sets ActiveWorkbook to the newly created workbook that contains your single sheet.

Save it as csv, close it afterwards, thereby returning to your source worksheet.

Note: Don't explicitly turn all prompts off. Instead omit the two standard prompts you get when doing this manually by setting CreateBackup:=False when saving and Savechanges:=False when closing the csv-workbook.

That way you are still prompted in cases where the file exists or there is an error.

Did you replace `ThisWorkbook.Sheets("Tabelle1")` with your actual table name? — Martin Dreher, Apr 25 '17 at 07:08

Multi worksheet export one to CSV and autosave with no prompt

1 Answers1