Macro: open save as dialogue, prefill filename BUT DO NOT SAVE YET

[oliviar]

Hi Excelperts,
I want to open the 'save as' box, and have it fill in the filename based on the value in 'g1' but I DON'T want it to save (it can't save to the location where its opened from so it has to be saved elsewhere.)

Can this be done? I have been able to get it to save as the value of g1 ( but no save as box), and I've been able to get it to open the save as box (but not fill in the value of g1) but not this ideal combo between the two.

Thanks

 

[jbeaucaire]

Dim SvName as String

SvName = Sheets("Sheet1").Range("G1")

Application.Dialogs(xlDialogSaveAs).Show SvName

 

[tusharm]

Maybe, something along the lines of

Sub testSaveAs()
    Dim Rslt
    Rslt = GetSaveAsFilename(ActiveSheet.Range("g1").Value)
    If TypeName(Rslt) = "Boolean" Then
    ElseIf Rslt = ActiveSheet.Range("g1").Value Then
        MsgBox "Oops! You must change the file name"
    Else
        ActiveWorkbook.SaveAs Rslt
        End If
    End Sub

Hi Excelperts,
I want to open the 'save as' box, and have it fill in the filename based on the value in 'g1' but I DON'T want it to save (it can't save to the location where its opened from so it has to be saved elsewhere.)

Can this be done? I have been able to get it to save as the value of g1 ( but no save as box), and I've been able to get it to open the save as box (but not fill in the value of g1) but not this ideal combo between the two.

Thanks

 

[oliviar]

Solved it!

Sorry guys,
Neither of those two worked. But this did!
:D I'm getting better :D

Sub saveasa1()
'save as with the filename prefilled by value of a1
        Application.Dialogs(xlDialogSaveAs).Show Sheet1.Range("a1")
End Sub

 

[jbeaucaire]

Re: Solved it!

I'm glad you got it, but can't for the life of me see how your method is different from what I showed? You just did it in one line instead of using a variable for the initial display text.

Good job, though. It always feel better to suss it out yourself, huh?

 

[oliviar]

Yes, I don't know why it worked either. I thought yours had merit, and I just changed how it referred to the filename... made it more direct. Your code brought up the save as dialogue box, but the filename was blank. Anyway, I thought 'what if I cut out the middleman' and it worked. Still not sure WHY, but I'll take it!

 

[idh959]

Hi All,

I found this thread to help give me the "save as" dialogue box with the defined name and it works fine. But can someone, tell how to also get it to define the file type as something else specific. For example the orginal file is XLS, I want to save as CSV.

Cheers,
Ian

 

[xenou]

To save as CSV you only need to execute the saveas command with the proper file format:

ActiveWorkbook.SaveAs SomeFile.csv, xlCSV

Using the GetSaveAsFileName is just a way to get the filename for the save. Here's some untested code cobbled together from the examples above.

Sub testSaveAs()
    
    Dim FSO As Object
    Dim s(1) As String
    Dim Rslt
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    s(0) = ActiveWorkbook.FullName
    If FSO.FileExists(s(0)) Then
        s(1) = FSO.GetBaseName(s(0))
        s(1) = s(1) & ".csv"
    Else
        s(1) = ActiveWorkbook.FullName & ".csv" '//XL file is new/unsaved
    End If

    Rslt = Application.GetSaveAsFilename(InitialFileName:=s(1), fileFilter:="CSV (Comma delimited) (*.csv), *.csv")
    If TypeName(Rslt) <> "Boolean" Then
        ActiveWorkbook.SaveAs Rslt, xlCSV
    End If
    
End Sub

 

[Bearsparko]

Re: Solved it!

Hi,

I've just spent 2 days on and off trying to get
Application.GetSaveAsFilename

to work with limited success.

Stumbled across your 1 line of code and it works.

THANK YOU.

Before i faint with euphoria are there any little issues with your code? It seems to work without any glitches so far.

Sorry guys,
Neither of those two worked. But this did!
:D I'm getting better :D

Sub saveasa1()
'save as with the filename prefilled by value of a1
        Application.Dialogs(xlDialogSaveAs).Show Sheet1.Range("a1")
End Sub

 

[sahmed8251]

Dim SvName as String

SvName = Sheets("Sheet1").Range("G1")

Application.Dialogs(xlDialogSaveAs).Show SvName

Hi. I wonder if you can help me. Or anyone else for that matter. This code I have tried does work. However it does not like full stops "." in the filename. Is there a work around on this?