Setting the "SAVE AS" path 1

[ETID]

In Excel, I need the VBA syntax to dynamicaly set the path that users will be directed to...

So that when the user clicks File>Save As... the path to the desired folder will be already set in the dialog box.

and maybe as a bonus,...set the filename as well?

Thanks for any help.

 

[RobBroekhuis]

I don't think you can do the filename bit. The folder can usually be set with the ChDir and ChDrive commands, but this doesn't work for network folders without a drive mapped to them. For those, I couldn't find a straightforward method, so I found a workaround documented in the following sub:

Public Sub ChangeActiveFolder(foldername As String)
'uses trickery to set active folder.  To set exact folder, foldername
'should end in a backslash
   Application.SendKeys ("{ESC}")
   Application.GetSaveAsFilename foldername
End Sub

It calls on the GetSaveAsFilename method, but automatically cancels out (but only after resetting the active folder).
Rob

 

[rmikesmith]

ETID,

Here is some code that should do what you want. Place the following procedure in a standard code module:

Sub CustomSaveAs()
Dim FName As String
Dim SuggestedPath As String
Dim FFilter As String
Dim FIndex As Integer

  On Error GoTo CustomSaveAs_Error
  
' Set SuggestedPath to the location you want user to save file and optionally add suggested filename.  For this example I made it a local var but could also be global.
    SuggestedPath = "C:\SuggestedFilename.xls"
' Set FFilter to desired extension(s), if needed; see Help on GetSaveAsFilename
    FFilter = "Microsoft Excel Workbook (*.xls), *.xls"
    FIndex = 1  'See Help on GetSaveAsFilename
    FName = Application.GetSaveAsFilename(SuggestedPath, FFilter, FIndex, "Save As")
    If FName <> &quot;False&quot; Then
' If FName = &quot;False&quot; then user clicked Cancel
      Application.EnableEvents = False
      ActiveWorkbook.SaveAs FName
    End If
  
CustomSaveAs_Exit:
  Application.EnableEvents = True
  Exit Sub
CustomSaveAs_Error:
  MsgBox &quot;Error &quot; & Err.Number & &quot;, &quot; & Err.Description
  Resume CustomSaveAs_Exit
  
End Sub

Next, set up the BeforeSave event handler to look like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  If SaveAsUI Then
    Cancel = True
    CustomSaveAs
  End If
End Sub

When the user selects SaveAs, the CustomSaveAs procedure will display the standard SaveAs dialog with Path and Filename your code assigns. I have this set up in a workbook. Let me know if you'd like a copy.

HTH
Mike

 

[RobBroekhuis]

An excellent solution by Mike, as long as it's just that particular workbook for which you want to direct the user to a certain folder. If you want to direct ALL save as commands for whichever workbooks the user happens to be using to the certain folder, you would be better off setting the default folder. So it depends on what exactly you are trying to achieve.
Rob

 

[rmikesmith]

Rob,

You are quite right and I actually developed two slightly different versions to handle the situations you refer to. To direct all workbook SaveAs events to the custom procedure, I hooked the

CustomSaveAs

procedure into the File menu, Toolbar and Alt-S keystroke commands. In that implementation, the

CustomSaveAs

procedure was modified to handle Save as well as SaveAs. This could be set up as either a workbook or an add-in.

If only the suggested path is needed, your solution is certainly the most straightforward. The main workbook could even save the existing default path and restore this when closed.

Regards,
Mike

 

[ETID]

Thanks much ...