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.
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
i=Len(fil) - 5
fnme=Left(fil, i) 'Minus the information .xlsx or .xlsm (5 characters)
.InitialFileName="Copy_" & fil
If .Show=-1 Then .Execute
The path in question is "C:\Users\HYMC\theSmallman\Joe\"
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:
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.