VBA macro to save excel worksheet as a CSV file

[K1bb5]

fpath = "\\location\"
fname = "folder_" & Format(dte, "ddmmyyyy")

Set srcWkb = ActiveWorkbook

Worksheets("Thatworksheet").Copy

Set destWkb = ActiveWorkbook

With destWkb.Sheets("Thatworksheet").UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False


sfullpath = fpath & fname & ".csv"

ActiveWorkbook.SaveAs Filename:=sfullpath, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close savechanges:=False


Unfortunatley this saves the file in the desired location but with .csv.err at the end so not the required .csv only format.

e.g. fname_15032023_20230316_144855.csv.err

I have tried different file formats to see if that could be the issue xlcsvWindows xlCSVMSDOS but thet too get a mysterious .err extension added on. The file location isn't restricted as can create a new file there.
I have also tried in sfullpath = fpath & fname only so as not to duplicate the .csv assignment in case that was issue.
Using O365 and 2016.

 

[Monty Norman]

fpath = "\\location\"
fname = "folder_" & Format(dte, "ddmmyyyy")

Set srcWkb = ActiveWorkbook

Worksheets("Thatworksheet").Copy

Set destWkb = ActiveWorkbook

With destWkb.Sheets("Thatworksheet").UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False


sfullpath = fpath & fname & ".csv"

ActiveWorkbook.SaveAs Filename:=sfullpath, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close savechanges:=False


Unfortunatley this saves the file in the desired location but with .csv.err at the end so not the required .csv only format.

e.g. fname_15032023_20230316_144855.csv.err

I have tried different file formats to see if that could be the issue xlcsvWindows xlCSVMSDOS but thet too get a mysterious .err extension added on. The file location isn't restricted as can create a new file there.
I have also tried in sfullpath = fpath & fname only so as not to duplicate the .csv assignment in case that was issue.
Using O365 and 2016.

Hello @K1bb5

Where is fpath and fname are coming from?

try this:
Sub SaveAsCSV()
ActiveWorkbook.SaveAs Filename:="W:\ChangeThisToMatchYourPath\Book1.csv", FileFormat:=xlCSVMSDOS, CreateBackup:=False
End Sub

Make sure you make any adjustmnets that you need for example the path "W:\ChangeThisToMatchYourPath\" the file name "Book1.csv" and the FileFormat "xlCSVMSDOS" to match your needs!

Monty