Help with VBA code for "File Save As" and path

[MassSpecGuru]

I have an Excel 2003 workbook that, once the user is finished with, prompts the user to save the updated workbook.

I used the Application.Dialogs(xlDialogSaveAs).Show command to prompt the user for the file name, however, I want the "default" save to folder to be the one in which the current workbook resides. Is there any way to do this?

Also, is there a way to prevent the user from over-writing the existing workbook?


Thanks for any suggestions.

 

[GTO]

You may wish to look at Application.GetSaveAsFilename

 

[schielrn]

Maybe try this. Some or most of this may not be needed and you can modify as you want:

Sub test()
Dim fDialog As Office.FileDialog
Dim nameOfFile As String
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
With fDialog
    '.AllowMultiSelect = False 'Not sure if you need these
    '.Filters.Clear 'Not sure if you need these
    '.Filters.Add "Excel File", "*.xls*" 'Not sure if you need these
    .Title = "Please select the file you would like to import"
  .InitialFileName = ActiveWorkbook.Path
    If .Show = False Then Exit Sub
End With
End Sub

As for preventing overwriting an existing workbook, you could maybe put in a test to check if the workbook exists in that directory.

Hope that helps.