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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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,089,992
Messages
5,411,712
Members
403,392
Latest member
Faster 72

This Week's Hot Topics

Top