Macro: export current worksheet as CSV file using Save As prompt

[dlz217]

I have a workbook that has 3 sheets. I have a macro that performs different operations and I'd like to add a step to take the active worksheet, prompt me to save that individual sheet as a CSV file (using the Save As prompt), but not affect the current workbook and continue on with further steps.

I'm able to prompt the Save As dialog, but that changes the file name of the active workbook, which I don't want.

    MsgBox "Save CSV file."
    Application.Dialogs(xlDialogSaveAs).Show

 

[Andrew Poulsom]

Try copying the worksheet to a new workbook with:

ActiveSheet.Copy

then saving ActiveWorkbook as csv (and closing it).

 

[dlz217]

Where am I going wrong here? +PAYE CSV Gen.xlsm is the active workbook.

    Workbooks.Add
    Set TempFile = ActiveWorkbook
    Windows("+PAYE CSV Gen.xlsm").Activate
    ActiveSheet.Copy Before:=Workbooks(TempFile).Sheets(1)
    MsgBox "Save CSV file."
    Application.Dialogs(xlDialogSaveAs).Show
    ActiveWorkbook.Close

 

[Andrew Poulsom]

TempFile is a Workbook Object not a string:

ActiveSheet.Copy Before:=TempFile.Sheets(1)

But you don't need to Add a Workbook. ActiveSheet.Copy (with no arguments) will do that for you.

 

[dlz217]

Got it. Is there a way to have the prompt default to CSV?

 

[Andrew Poulsom]

You can use FileDialog. Maybe this will help you:

http://www.mrexcel.com/forum/excel-questions/377525-save-dialog-box-visual-basic-applications.html

Note that FileDialog doesn't actually save the file. Your code needs to do that.