Export to csv but Maintain xlsm as Active Workbook

[dgr]

Hi,
I'm using Excel 2013. I'm looking for a VB macro solution.

I have a .xlsm file. I want to export the active worksheet from this .xlsm file to csv. However, after the export, I want the .xlsm file to continue being my active workbook.

What's currently happening is when I export/saveas my .xlsm file to csv, the .xlsm file extension becomes a .csv I don't want the active .xlsm to become a csv

How do I achieve this via a Vb macro?

Thanks a lot.

 

[Darren Bartrup]

Have a look at .SaveCopyAs rather than SaveAs.

 

[dgr]

Have a look at .SaveCopyAs rather than SaveAs.

Thanks for your reply, Darren. I tried it but it said "compile error named argument not found"

Is there something wrong with my code below?

Sub save_as_csv()
'Application.DisplayAlerts = False
        
        ActiveWorkbook.SaveCopyAs Filename:= _
        "C:\path\to\my\test.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
        
End Sub

 

[Darren Bartrup]

Use

ActiveWorkbook.SaveCopyAs Filename:="C:\path\to\my\test.csv"

SaveCopyAs doesn't accept the file format argument, just the path.

 

[dgr]

Thanks Darren. Yes, this worked but it led to another problem. I wasn't able to open the csv file because Excel said that the file was corrupted. Would you happen to know of any other workarounds?

Thanks for your suggestion. I've learnt quite a bit from you already I appreciate your suggestions.

 

[Andrew Poulsom]

Copy the worksheet to a new workbook and save that workbook as csv:

ActiveSheet.Copy
ActiveWorkbook.SaveAs etc

I'll leave you to complete the etc.

 

[Darren Bartrup]

I wasn't able to open the csv file because Excel said that the file was corrupted.

I was wondering about that - I knew the SaveAs could cause problems if the correct file type wasn't specified, but wasn't sure about SaveCopyAs.

Andrew - I'm starting to think you may be a machine yourself

 

[dgr]

Thanks a lot for the idea Andrew. I appreciate it. Here's my code for someone else who might need it. I used the macro record button so it might not be perfect but it works

Sub Macro1()

    Cells.Select
    Range("J1").Activate
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ChDir "C:\path\to\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\path\to\Desktop\the_csv_file.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

 

[Andrew Poulsom]

I was wondering about that - I knew the SaveAs could cause problems if the correct file type wasn't specified, but wasn't sure about SaveCopyAs.

You can't use SaveCopyAs to save a workbook as a different file type.