Saving a specific worksheet in csv by using Dialog box Save As, so that the user can choose the path

[Gwhaou]

Hi,

Actually I'm using this code, to save a specific worksheet in CSV, on a predefined folder path, with this code the user can add the name for the file and it will be saved on the predefined path : here is on the desktop

Public Sub Save_CSV()


Application.ScreenUpdating = False

Sheets("DATA_SHEET").Select
Dim N_fold As Variant
Dim Stock_N As String


On Error GoTo Canceled

N_fold = InputBox("Your file will be saved on the desktop.", "Save in CSV", "add a name to the file")

If StrPtr(N_fold) = 0 Then Exit Sub

Stock_N = N_fichier

With ActiveSheet

.Copy
ActiveWorkbook.SaveAs "C:\Users\Johnny\Desktop\" & Stock_N & "_" & .Name & ".csv", xlCSV, Local:=True
ActiveWorkbook.Close False
.Activate


End With


Canceled:
Application.ScreenUpdating = True

End Sub

I want to use dialog box (Save as) predefined on CSV format, sorry i'm new to vba i don't know how to use dialog box to save one particular sheet on csv when the user use that macro.
I need some help

 

[Domenic]

To prompt the user to specify or select a path and/or filename, try...

    Dim saveAsFilename As Variant
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:="filename.csv", _
        FileFilter:="CSV Files (*.csv), *.csv", _
        Title:="Save As")
  
    If saveAsFilename = False Then Exit Sub

Change the name of the file used for InitialFileName accordingly. Then the workbook can be saved using saveAsFilename for the path and filename.

Hope this helps!

 

[Gwhaou]

Hi yesterday i found a solution,

Public Sub Save_CSV()

Dim FileName As String
FileName = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.csv), *.csv")

    If FileName = "False" Then
        MsgBox "No filename specified!", vbExclamation
    Else
    Sheets("DATA_SHEET").Select
    ActiveSheet.Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    End If
  
End Sub

It works, the user can save the sheet called "DATA_SHEET" with a dialog box (Save As) in CSV.
But i have another problem. When the sheet is copied and saved the format is not correct :
All the data are in on cell.

Do you know how to fix that ?

 

[Domenic]

Have look here...

https://harvestmedia.zendesk.com/hc/en-us/articles/360023978031-Opening-Excel-files-with-the-correct-CSV-list-separator

 

[Gwhaou]

Have look here...

https://harvestmedia.zendesk.com/hc/en-us/articles/360023978031-Opening-Excel-files-with-the-correct-CSV-list-separator

I tried that but it's still not working, the exported file copy all the data on the first cell

 

[Kerryx]

Did you use the import option in excel

 

[Gwhaou]

Actually, I use a Template workbook to transfer needed data with macro from one base sheet to the sheet called "DATA_SHEET" after that I want to save that Sheet seperately on csv format because. U see the Template workbook will delete all the data and initialize when it close to allow another user to use the Template workbook.
So with the code "Save_CSV" the user can save that actual sheet in csv in the folder that he choose.

The code works but as i said the format has change from that (on the DATA_SHEET):

to that (the saved one in csv ) :

I don't know why Excel do that

 

[Gwhaou]

If it doesn't work, it's ok I gonna use the first code in which the folder path is already defined, with that code the csv file is saved as it is.

 

[Kerryx]

The only way i can replicate your issue is by having the List Separator set to ; in the region setting.

 

[Gwhaou]

The only way i can replicate your issue is by having the List Separator set to ; in the region setting.

I tried both ";" and ",", And launched the macro. The result remain the same. by the way thank you for your help