Open Dialog Defaulting to Specified Network Path

This article focuses on the Excel VBA for opening a file dialog which defaults to a specified network path (directory).  The purpose of the following procedure is to save the current file to a path of your choosing.  Usually opening a file dialog will default to the last file directory which was opened within Excel.  This Excel VBA procedure will send the information to a specified path.

Option Explicit

Sub SavePathSpecific() 'Open Dialog defaulting to specified network path
Dim path As String
Dim fil As String Dim fnme As String Dim i As Integer 'Trap the name of the file
path=ThisWorkbook.FullName
fil=Dir(path)
i=Len(fil) - 5
fnme=Left(fil, i) 'Minus the information .xlsx or .xlsm (5 characters)

With Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect=False
.InitialFileName="G:\Smallman\VBA\"
.InitialFileName="Copy_" & fil
If .Show=-1 Then .Execute
End With
End Sub

The path in question is "G:\Smallman\VBA\"

However, you can change this to whatever you choose.  The key is to remember to include the back slash (\) at the end of the path.  Here is an example from my home computer:

vba open directory

The dialog will open to the above directory every time.  The file will be saved as

Copy_YOUR FILE NAME

However you have to click the Save button which appears in order for the procedure to complete.