Copy worksheet and save to desktop as a csv file

[Daddylouc]

I have this code to copy an existing worksheet to my desktop as an xlsx file but I need to save it as a csv file. I tried just changing the named extension but that didn't work. How would I modify this code to save the file as a csv?

Sub Expo()


Application.ScreenUpdating = False


'Get path for desktop of user PC
Path = Environ("USERPROFILE") & "\Desktop"
Sheet1.Cells.Copy


'Create new workbook and past copied data in new workbook & save to desktop
Workbooks.Add (xlWBATWorksheet)
ActiveWorkbook.ActiveSheet.Paste
ActiveWorkbook.ActiveSheet.Name = "report"
ActiveWorkbook.SaveAs Filename:=Path & "" & "report " & Format(CStr(Now()), "dd-mmm (hh.mm.ss AM/PM)") & ".xlsx"
ActiveWorkbook.Close SaveChanges:=True


Application.ScreenUpdating = True


MsgBox "Exported to Desktop"


End Sub

 

[Trevor G]

I think you need to add in the file format as well. See basic example below of recorded macro to save workbook as csv.

Sub Macro1()
ChDir "C:\Desktop"
ActiveWorkbook.SaveAs Filename:="C:\Desktop\rubbish.csv", _
FileFormat:=xlCSV, CreateBackup:=False
End Sub

 

[Joe4]

It is more than just changing the file extension (that is just a name). You also have to change the file type. Otherwise, it will still save as an Excel file.
So you need to add the FileFormat argument to your SaveAs function, i.e.

[COLOR=#333333]ActiveWorkbook.SaveAs Filename:=Path & "\" & "report " & Format(CStr(Now()), "dd-mmm (hh.mm.ss AM/PM)") & [/COLOR][COLOR=#ff0000]".csv", FileFormat:=xlCSV[/COLOR]

 

[Daddylouc]

Thanks. I will give these suggestions a try!