Save As CSV

[BradleyS]

Hi

I have a workbook with a macro, but I don't want the workbook overwritten.
Therefore if I press the save button OR close the workbook I always want it to Save As a CSV file format

Ideally I want to show the Save As dialog screen and prefix it with the my default file name.

I have added the code below to BeforeSave and BeforeClose, but I can't get it to work as mentioned above

Dim newFile As String, fName As String

gName = "FGM_"
fName = Range("A2").Value
newFile = gName & " " & fName & " " & Format$(Date, "dd-mm-yyyy")

Dim fileSaveName As Variant

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=newFile, FileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Save As")

 

[svendiamond]

I'm pretty sure if you're using the GetSaveAsFileName command, you don't need a variable that is "equal" to its result. Instead, just write:

Application.GetSaveAsFilename InitialFileName:=newFile, FileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Save As"

 

[BradleyS]

In the end I got it to work using the below code. It creates a new CSV using my fixed naming convention and retains the original workbook. The only thing it doesn't give me is a choice as to where to save it, but as it saves it in the same folder as the original file, I'm happy.

Dim newFile As String, fName As String

gName = "FGM_"
fName = Range("A2").Value
newFile = gName & " " & fName & " " & Format$(Date, "dd-mm-yyyy")

ActiveWorkbook.SaveAs FileName:=newFile, FileFormat:=xlCSV, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

 

[Sektor]

Sub G()
    Dim newFile As String, fName As String, gName, fileSaveName
    gName = "FGM_"
    fName = Range("A2").Value
    newFile = gName & " " & fName & " " & Format$(Date, "dd-mm-yyyy")
    fileSaveName = Application.GetSaveAsFilename(newFile, "CSV (Comma delimited) (*.csv), *.csv")
    ThisWorkbook.SaveAs fileSaveName, xlCSV
End Sub

 

[BradleyS]

Perfect . Thank you

 

[Sektor]

You're welcome!

 

[mikerickson]

In the end I got it to work using the below code. It creates a new CSV using my fixed naming convention and retains the original workbook. The only thing it doesn't give me is a choice as to where to save it, but as it saves it in the same folder as the original file, I'm happy.

You could use this

    Dim newFile As String, fName As String
 
    gName = "FGM_"
    fName = Range("A2").Value
    newFile = gName & " " & fName & " " & Format$(Date, "dd-mm-yyyy")

    Application.Dialogs(xlDialogSaveAs).Show newFile, xlCSV