Custom Save Button

[rjddatascien]

Folks,

Can anyone explain to me why the worksheet gets renamed as the file save name when I run this bit of code? Is there a way to prevent this from happening?


Dim fileSaveName

fileSaveName = Application.GetSaveAsFilename(InitialFileName:="PcleanRequest", FileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Save As")
If fileSaveName <> "False" Then ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlCSV


End Sub





Cheers.

[Aflatoon]

That's how Excel handles CSV files. The sheet name is always the file name (as there's nothing else it can use).

[rjddatascien]

That's unfortunate. Thanks!

[Logit]

.
Here are two methods of saving a Sheet as a CSV Workbook - workbook name hardcoded :



Option Explicit


Sub test()
Dim TempWB As Workbook

ActiveSheet.Copy
Set TempWB = ActiveWorkbook


Dim fd As FileDialog
Dim sPath As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

If fd.Show = -1 Then
sPath = fd.SelectedItems(1)
End If
'sPath now holds the path to the folder or nothing if the user clicked the cancel button

With TempWB
.SaveAs Filename:=sPath & "\CSV-Exported", FileFormat:=xlCSVWindows, CreateBackup:=False

'use below to try your asc format to see if it saves ok for you
'SaveAs Filename:=sPath & "\CSV-Exported.asc", CreateBackup:=False
.Close
End With


End Sub






Option Explicit




Sub test()
Dim TempWB As Workbook
Dim fd As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.Copy
Set TempWB = ActiveWorkbook


fd = "C:\Users\My\Desktop" 'Edit path as required. Assumes the folder Test already exists on the desktop
'fd now holds the path to the folder

With TempWB
'Exchange Sheet is the name of the saved CSV file. Edit name as required.
.SaveAs Filename:=fd & "\Exchange_Sheet", FileFormat:=xlCSVWindows, CreateBackup:=False
.Close
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub