Export to csv but Maintain xlsm as Active Workbook

dgr

Board Regular
Joined
Apr 24, 2005
Messages
176
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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?

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

SaveCopyAs doesn't accept the file format argument, just the path.
 
Upvote 0
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.
 
Upvote 0
Copy the worksheet to a new workbook and save that workbook as csv:

Code:
ActiveSheet.Copy
ActiveWorkbook.SaveAs etc

I'll leave you to complete the etc.
 
Upvote 0
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 :p
 
Upvote 0
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 :)
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top