VBA Save as dialog box opening

[bliven731]

Is there a way to use VBA to open the save as dialog box, set a default name from cell information, and pick the save as file type?

Basically I want it to select sheet 3, Do file save as, default the name as (if I put quote marks I want the cell contents), "D3" "B3"-"C3" So it would be 123456 1-2019 (123456 is D3 contents, 1 is B3 contents, 2019 is C3 contents, and also make the save as file type "CSV (Comma delimited)". That is where the macro would end so that the end user could select where they actually want to save the file. It obviously has to select sheet 3 first because CSV only saves a single sheet.

The file name part is not required if that's too hard to do, the important part is selecting the file type, but the name thing would be nice if it's easy.

 

[G12]

Hi
It's a copy paste from something I wrote ages ago, but you might be able to covert this to your needs?
HTH

Sub SaveAs()
' Export Details
    Dim Filenames As Variant
    Dim Filefilter As String
    Dim Msg As String
       
    Filefilter = "2010 Excel Workbook (*.xlsx),*.xlsx"
           Filenames = Application.GetSaveAsFilename(, Filefilter)
    
    If Filenames = False Then GoTo SveCnl Else
        
        ActiveWorkbook.SaveAs Filenames, xlOpenXMLWorkbook
        

    On Error GoTo SveCnl
    
        Msg = "The " & Name & " Macro is Complete!" & vbCrLf & vbCrLf
        Msg = Msg & "This file has been saved as: " & vbCrLf & vbCrLf
        Msg = Msg & Filenames
        MsgBox Msg, vbInformation

        ActiveWorkbook.Saved = True
        
    On Error Resume Next
    Exit Sub
    
SveCnl:
        MsgBox "The Save has been Cancelled!", vbInformation
        ActiveWorkbook.Saved = False
End Sub

 

[bliven731]

So from what your have posted I think I mostly got it figured out but how do I actually tell it to save it as a CSV (comma delimited)? This is what I have come up with.

Sub Save()
Dim IntialName As String
Dim fileSaveName As Variant
InitialName = Range("D3") & " " & Range("B3") & "-" & Range("C3")
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
    fileFilter:="Excel Files (*.csv), *.csv")

If fileSaveName <> False Then
    MsgBox "Save as " & fileSaveName
End If
End Sub

 

[G12]

Hi. I think it's just as easy as changing

"2010 Excel Workbook (*.xlsx),*.xlsx"
to
"CSV (*.csv),*.csv"

Let me know if that doesn't work and I'll check properly

 

[bliven731]

Okay I switched to using your code and the CSV change seemed to work (well I had to type the full name but otherwise it was good). How can I set the initial name though? This code worked with a different implementation but doesn't seem to work when inserted into your code.

Dim InitialName as String
InitialName = Range("D3") & " " & Range("B3") & "-" & Range("C3")

Here is the current attempted use. The save as box is generating with the correct file format so that is good, but it's not putting any name in the field.

Sub Savetest()
' Export Details
    Dim Filenames As Variant
    Dim Filefilter As String
    Dim Msg As String
    Dim InitialName As String

    Filefilter = "CSV (Comma delimited)(*.csv),*.csv"
           Filenames = Application.GetSaveAsFilename(, Filefilter)
    InitialName = Range("D3") & " " & Range("B3") & "-" & Range("C3")
   
    If Filenames = False Then GoTo SveCnl Else
       
        ActiveWorkbook.SaveAs Filenames, xlOpenXMLWorkbook
       

    On Error GoTo SveCnl
   
        Msg = "The " & Name & " Macro is Complete!" & vbCrLf & vbCrLf
        Msg = Msg & "This file has been saved as: " & vbCrLf & vbCrLf
        Msg = Msg & Filenames
        MsgBox Msg, vbInformation

        ActiveWorkbook.Saved = True
       
    On Error Resume Next
    Exit Sub
   
SveCnl:
        MsgBox "The Save has been Cancelled!", vbInformation
        ActiveWorkbook.Saved = False
End Sub

 

[G12]

Try

InitialName = Range("D3").value & " " & Range("B3").value & "-" & Range("C3").value

 

[bliven731]

Nevermind I got it figured out!!!

 

[G12]

No. It's because I'm an idiot.

I'd deleted a bunch of stuff from my code to make it easier to understand, I deleted too much. I think this will correct

        Filenames = Application.GetSaveAsFilename(InitialName, Filefilter)

 

[bliven731]

No. It's because I'm an idiot.

I'd deleted a bunch of stuff from my code to make it easier to understand, I deleted too much. I think this will correct

        Filenames = Application.GetSaveAsFilename(InitialName, Filefilter)

Yep, that's exactly what I ended up doing, thank you!

 

[bliven731]

Well the code in general works, but I think the filefilter I have for CSV just isn't right or something. It pulls the name as requested and it saves the file as a ".csv" file, except it gives this error message. When I hit yes, it saves it, but when I open the file it gives 2nd error. It's also not saving the file as an actual csv, because if I right click and open the file in notepad, it is bringing up gibberish instead of the expected text.

Here is a posting of the code again if anyone has any idea what is wrong with the file format.

Sub Savetest()
' Export Details
    Dim Filenames As Variant
    Dim Filefilter As String
    Dim Msg As String
    Dim InitialName As String
    InitialName = Range("D3") & " " & Range("B3") & "-" & Range("C3")
    Filefilter = "CSV (Comma delimited)(*.csv),*.csv"
           Filenames = Application.GetSaveAsFilename(InitialName, Filefilter)
    
   
    If Filenames = False Then GoTo SveCnl Else
       
        ActiveWorkbook.SaveAs Filenames, xlOpenXMLWorkbook
       

    On Error GoTo SveCnl
   
        Msg = "The " & Name & " Macro is Complete!" & vbCrLf & vbCrLf
        Msg = Msg & "This file has been saved as: " & vbCrLf & vbCrLf
        Msg = Msg & Filenames
        MsgBox Msg, vbInformation

        ActiveWorkbook.Saved = True
       
    On Error Resume Next
    Exit Sub
   
SveCnl:
        MsgBox "The Save has been Cancelled!", vbInformation
        ActiveWorkbook.Saved = False
End Sub