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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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,486
Messages
5,414,821
Members
403,546
Latest member
robfraley80

This Week's Hot Topics

Top