Export worksheet as CSV with Save As dialog

Hi,

I have the following issue. I want to export a specific worksheet in CSV format but I want the macro to open a Save As dialog.
Actually I have found a macro that exports the sheet in PDF format and it works great. Can someone adjust the following macro in order to export also in CSV format? I want to copy the whole worksheet and paste only values in the new CSV, not formulas.
The idea is to give the user the option to export in either CSV or PDF format, using 2 different macros.
Thanks!

ps: I don't know

Sub Export_PDF()

    Sheets("(3) Google Calendar Setup").Select
    pdfName = ActiveSheet.Range("T1")
    ChDir "C:\" 'This is where you set a default file path.
    fileSaveName = Application.GetSaveAsFilename("Hansons Marathon Method - Training Calendar", _
    fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    End If
    MsgBox "File Saved to" & " " & fileSaveName
End Sub

Try this. I didn't change much except to give you the option of which format to save it as and added the option for CSV.

Sub File_OptionExportPDF_CSV()

    Sheets("(3) Google Calendar Setup").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    PDFName = ActiveSheet.Range("T1")
    CSVName = ActiveSheet.Range("T1")
    ChDir "C:\" 'This is where you set a default file path.
    
    MSG1 = MsgBox("Yes = PDF and NO = CSV", vbYesNo, "Choose file format?")

    If MSG1 = vbYes Then
      fileSaveName = Application.GetSaveAsFilename("Hansons Marathon Method - Training Calendar", _
    fileFilter:="PDF Files (*.PDF), *.PDF")
    If fileSaveName <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlPDF, Filename:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    End If
    Else
      fileSaveName = Application.GetSaveAsFilename("Hansons Marathon Method - Training Calendar", _
    fileFilter:="CSV Files (*.CSV), *.CSV")
    End If

    MsgBox "File Saved to" & " " & fileSaveName
End Sub

Hi Jessebranum777, thank you for your reply!

1. If the export file is CSV, the code seems to run but I cannot save the file. I tried different locations but the file is not saved anyway. The code works great if it is a PDF

2. I really like your idea with "Yes" or "No" but is it possible to make a new macro since the pdf and the csv are exported from different sheets? I use sheet "(3) Google Calendar Setup" to get the data for pdf and "(4) Google Calendar input" for CSV. I guess it would be too complicated to adjust the macro the export pdf from sheet (3) or export csv from sheet (4).

The actual file is this. It is a Marathon training program. The sheet that I want to extract in CSV format is the last (4) and it is an import sheet for Google Calendar.

https://drive.google.com/file/d/0B5A...it?usp=sharing

I am not sure why the code for applicationsaveas didn't work for CSV so I added a slight modification for that section. I also changed it up so that there is no option for yes or no anymore but instead it will save in the appropriate formate based on which sheet name you are on as you mentioned they are exported from different sheets. No it was not complicated at all. Try this.

Sub File_OptionExportPDF_CSV()

    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    If ActiveSheet.Name = "(4) Google Calendar input" Then
    CSVName = ActiveSheet.Range("T1")
    ChDir "C:\" 'This is where you set a default file path.
      filesavename = Application.GetSaveAsFilename("Hansons Marathon Method - Training Calendar", _
    fileFilter:="PDF Files (*.PDF), *.PDF")
    If filesavename <> False Then
    ActiveSheet.ExportAsFixedFormat Type:=xlPDF, Filename:= _
        filesavename _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    End If
    End If
    
    If ActiveSheet.Name = "(3) Google Calendar Setup" Then
    PDFName = ActiveSheet.Range("T1")
    ChDir "C:\" 'This is where you set a default file path.
      filesavename = Application.GetSaveAsFilename("Hansons Marathon Method - Training Calendar", _
    fileFilter:="CSV Files (*.CSV), *.CSV")
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        "" & filesavename & "" _
        , FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    End If
    
    MsgBox "File Saved to" & " " & filesavename
    
End Sub

No problem. Don't forget to add rep * next to my name please. I am glad it works for you.