Setting Auto populated File Name for Save Window

[Muni]

Hello all,

I am trying to find a way to set the default name of a file that appears in the Save Dialogue box based on a series of cells but still allow the user to specify the directory that the file is saved in. They will be opening a template.

Thank you for any help that you may be able to provide.

 

[Ruddles]

Hi and welcome to the board!

As you start typing sFileName = Application.GetSaveAsFilename(, you should get a 'tool tip' prompt indicating what parameters you can pass to the function. Does that not happen?

If you showed us your code, that would help us give you a specific solution, but in the meantime you should be looking at passing a parameter called InitialFileName to the function, like this:-

sNewFileName = Application.GetSaveAsFilename([B][COLOR=blue]InitialFileName:=sSuggestedFileName[/COLOR][/B]..., etc.

 

[Muni]

Thank you for you help that was the exact line of code that I needed but now I am have some other problems. As requested my code is below. The code is working all of the up to where it has to save. It is searching for an existing file and everything correctly but when it saves excel crashes.

A little more context around what I am trying to do.
I want to preempt the save function by updating the file name to the latest version based on the data in some of the cells in differing worksheets and then seeing if that file exists and if it does then just save the file, this portion of the function is working properly. But, if the file does not exist then it opens the dialogue box so that the user can select the file directory and then it saves the file with the predetermined name and it is after the GetSaveAsFilename gets the name and I have it save the file that excel crashes. Also if anyone knows how to validate the file name so that it does not have any special charters that line of code would be nice to know.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Dimensioning Variables
Dim Lease As String, WellNo As String, WellLe As String, Version As String
Dim fName As String, fName2 As String, Path As String
Dim YYYYMMDD As String, Y As String, M As String, D As String
Dim msg As String

'Pull in Information for file Save into Path
msg = "Please No Special Characters in File Name"
Path = ThisWorkbook.Path

'Gather well information to name file
Lease = Land.Cells(3, 3).Value
WellLe = Land.Cells(9, 3).Value
WellNo = Land.Cells(10, 3).Value
Version = Front_End_Loading.Cells(2, 6).Value

'Adjust date information so that month and day always show up as two digits
YYYYMMDD = Front_End_Loading.Cells(2, 55).Value
Y = Year(YYYYMMDD)
If Month(YYYYMMDD) > 9 Then M = Month(YYYYMMDD) Else M = "0" & Month(YYYYMMDD)
If Day(YYYYMMDD) > 9 Then D = Day(YYYYMMDD) Else D = "0" & Day(YYYYMMDD)

'Compile file name
fName = Lease & " " & WellLe & " No." & WellNo & " Permitting Form v" & Version & " " & _
Y & M & D & ".xls"

'Search file status
If Path <> "" Then '..........................................Does the file have a path, Blank if not saved
With Application.FileSearch '..............................Search to see if file name exists in its current state
.NewSearch '..........................................with the modify data the same
.LookIn = ThisWorkbook.Path '..........................Search Path of current workbook
.SearchSubFolders = False '..........................Don't look in sub folders
.Filename = fName '..................................Search for current file name
.MatchTextExactly = False '..........................Does it exists
.FileType = msoFileTypeAllFiles '......................Look for all Microsoft Office files
If .Execute() > 0 Then '..............................If Same File found then Save work book
If Not ThisWorkbook.Saved Then ThisWorkbook.Save '.Save file if not already saved
Else '..............................................If no previous save exist then proceeded to save
With Application '..............................Hide all alerts
.DisplayAlerts = False
.EnableEvents = False
End With
'List file save Name and verify location
fName2 = Application.GetSaveAsFilename( _
InitialFileName:=fName, _
FileFilter:="Excel Files (*.xls), *.xls", _
Title:=msg)

'Save file
If fName2 = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName2

With Application '................................Show alerts
.DisplayAlerts = True
.EnableEvents = True
End With
End If
End With
Else
With Application '.......................................Hide all alerts
.DisplayAlerts = False
.EnableEvents = False
End With
'List file save Name and verify location
fName2 = Application.GetSaveAsFilename( _
InitialFileName:=fName, _
FileFilter:="Excel Files (*.xls), *.xls", _
Title:=msg)
'Save file
ThisWorkbook.SaveAs Filename:=fName2

With Application '........................................Show alerts
.DisplayAlerts = True
.EnableEvents = True
End With
End If
End Sub

 

[Ruddles]

To the best of my knowledge GetSaveAsFilename validates the filename and won't exit the dialog until the filename is valid. You may want to do a bit of Googling to verify this.

Can I suggest that if you're going to post code in the future, you place it between CODE tags - the # icon in the advanced editor toolbar. This preserves indenting and engages a fixed-width font, both of which makes your code easier to read and this in turn encourages people to read it and respond.