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

MassSpecGuru

Board Regular
Joined
Feb 23, 2010
Messages
55
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
You may wish to look at Application.GetSaveAsFilename
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
Maybe try this. Some or most of this may not be needed and you can modify as you want:

Rich (BB code):
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,110
Messages
5,412,486
Members
403,429
Latest member
jmdesk20

This Week's Hot Topics

Top