VBA: Prefill SaveAs Dialog Box With Defined Name, Give Alert If Filename Exists In Directory

[kelly mort]

Hello [Merry Christmas]
I have this code and I need some adjustments:
1. Fill the name field with predefined name
- "My File" + current year
Which is to say that if I run the code right now, the name field should display "My File 2021"
2. When I click the Save button, I want to scan the directory where I am saving the file for the availability of same name as in the name field. And if I find a match, I want to flag a message alert.

Sub SaveFileCode
ChDrive ThisWorkbook.Path 
ChDir ThisWorkbook.Path 

FileSaveAs = Application.GetSaveAsFilename(FileFilter:="Exel Files (*.xlsm),*.xlsm",Title:="Add Name")

If FileSaveAs = False Then 
        MsgBox "Operation terminated"
Else
       Application.EnableEvents=False 
       ThisWorkbook.SaveAs FileName:=FileSaveAs, FileFormat:=52, Password:="", writerespassword:="", ReadOnlyRecommended:=False 
       Application.EnableEvents=True
End Sub

Can someone please help me with it?

Thanks in advance.

 

[Larry Haydn]

Simple way to check is to use the Dir() function.

Sub SaveFileCode
ChDrive ThisWorkbook.Path 
ChDir ThisWorkbook.Path 
Dim MyFile as String 

   MyFile = "xxxxx" ' Here you have to provide the Path and Filename

FileSaveAs = Application.GetSaveAsFilename(FileFilter:="Exel Files (*.xlsm),*.xlsm",Title:="Add Name")

If FileSaveAs = False Then 
        MsgBox "Operation terminated"
Else
       '=============================
       '   Check whether file exists
       '   You will need a variable to contain the:
       '      path and filename 
       '=============================
       If Dir(MyFile) = "" then
            '=============================
            '   File does not exist, so proceed to save
            '=============================
            Application.EnableEvents=False 
            ThisWorkbook.SaveAs FileName:=FileSaveAs, FileFormat:=52, Password:="", writerespassword:="", ReadOnlyRecommended:=False 
            Application.EnableEvents=True
      Else
           '======================
           '   File exists; use another name
           '======================
           Application.StatusBar = "File already exists! Please use another name!"
           Exit Sub
      End If

End Sub

 

[kelly mort]

Okay great!

So instead of using a new variable "MyFile",

I used the Dir () function as :

If Dir (FileSaveAs) = "" Then
.................
.................
End If

So that part of my request (option #2) is resolved.

Now I am left with the option #1:

How to get the dialog box to open with a predefined name instead of the original name of the workbook.

Once again, thank you for your code @Larry Haydn

 

[kelly mort]

Cross posted at:

Prefill SaveAs Dialog Box With Defined Name, Give Alert If Filename Exists In Directory [SOLVED]

Hello Cross posted at: https://www.mrexcel.com/board/threads/vba-prefill-saveas-dialog-box-with-defined-name-give-alert-if-filename-exists-in-directory.1191206/ Reason: Only one of my requests has been answered so far. I have this code and I need some adjustment. I want to fill the...

www.excelforum.com

Reason:
I still need help with the request #1 as described in post #1 above.

 

[johnnyL]

I still need help with the request #1 as described in post #1 above.

Change:

FileSaveAs = Application.GetSaveAsFilename(FileFilter:="Exel Files (*.xlsm),*.xlsm",Title:="Add Name")

to:

DefaultFileName = "My File " & Year(Date)
FileSaveAs = Application.GetSaveAsFilename(InitialFileName:=DefaultFileName, FileFilter:="Exel Files (*.xlsm),*.xlsm", Title:="Add Name")

 

[kelly mort]

Thank you @johnnyL

It has done the job perfectly.

Have a great time.

 

[johnnyL]

Glad to help @kelly mort.