VBA to Save a file to a different format but keep original

[groutch]

Hi,

This should not be hard, but either I am stoopid or Excel is.

In VBA, I want to save a copy of my current worksheet as a CSV file
and not change anything about the current workbook. It seems to me
that "SaveAs filename:=whatever.csv fileformat:=xlCSV" saves the file
as XLS, and converts the current file to CSV !

I have tried various work-arounds such as using SaveCopyAs or
reloading the file from the original, but end up with all sorts of
messes such as a CSV file containing .XLS data and my current file not
being able to be written as Excel think it is a CSV file containing
multiple sheets.

TIA for any advice on how to do this, short of writing out the CSV
data in my own code :-(

Richard

 

[Tom Ogilvy]

Activesheet.Copy ' creates a new workbook for copy of current sheet.
ActiveWorkbook.SaveAs "C:\Myfile.csv",Fileformat:=xlCSV
ActiveWorkbook.Close Savechanges:=False

 

[Don Guillett]

I just recorded this and it worked just fine. .xls file not changed.

ActiveWorkbook.SaveAs Filename:="C:\yourfolder\aa1.csv",
FileFormat:=xlCSV, _
CreateBackup:=False

 

[groutch]

That did it - thanks Tom - Although it's a bit ugly to have a new
sheet pop up just to do this ?

Don Guillett's proposed solution leaves the workbook renamed as
aa1.csv, just as my attempt did, so is not a solution.

Thanks,

Richard

 

[Tom Ogilvy]

You can always to

Application.ScreenUpdating = False
' sample code provided to save the file
application.ScreenUpdating = True

and you will not see the sheet pop up.