VBA Before Save to open Save As dialogue box in current folder

[orangebloss]

Hi there

I have a work book that needs to be saved by default in the folder it was opened but users should be able to move it as required using Save As.

At the moment I'm getting complaints that the Save As windows opens on a local folder on the users computer rather than the network folder they opened it in.

I've spent hours googling and searching and can find some solutions but none which quite hit the mark.

In short on Save the code should check 'Save As' was used and if not it brings up the save as dialogue box in the folder that the file currently sits in. The user can then decide whether it needs to be in a different folder.

Coding I have so far brings up the Save As box :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim txtFileName As String
 
 If SaveAsUI = False Then

    Cancel = True

    MsgBox "You cannot save this workbook.  Use Save As"
   
   
   End If

 
'2. Call up your own dialog box.  Cancel out if user Cancels in the dialog box.
'ThisWorkbook.Path & "\" &
     
       txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            MsgBox "Action Cancelled", vbOKOnly
            Cancel = True
            Exit Sub
        End If
  
'3. Save the file.
       Application.EnableEvents = False
       
     
        ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
       
        Application.EnableEvents = True
   




End Sub

Any suggestions how I can incorporate the file path (assume that the network drive mapping is different for each user)?

 

[baitmaster]

you'll have to change the application.defaultfilepath setting

- Store the default value so you can change it back when finished
- Change it to the "UNC standard" path, i.e. ignoring mapped drive shortcuts
- Before the code ends change it back

Dim strDefault As String: strDefault = Application.DefaultFilePath
Application.DefaultFilePath = "[URL="file://\\new\path\here"]\\new\path\here[/URL]"

[ code here]

Application.DefaultFilePath = strDefault

 

[Eric Golf]

Hi orangebloss,

I believe the first parameter in GetSaveAsFilename is the suggested Filepath & Name. If you set it to the workbooks filepath the dialog should open with that i.e. Add red text in this line:-

txtFileName = Application.GetSaveAsFilename([COLOR="#FF0000"]ThisWorkbook.path[/COLOR], "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")

Hope this helps,

Eric.

 

[orangebloss]

you'll have to change the application.defaultfilepath setting

Dim strDefault As String: strDefault = Application.DefaultFilePath
Application.DefaultFilePath = "[URL="file://\\new\path\here"]\\new\path\here[/URL]"

[ code here]

Application.DefaultFilePath = strDefault

How do I use that code to set the UNC if the drive letter is different for everyone? I understand the concept of pointing it elsewhere but not the how!

 

[orangebloss]

Hi Eric, if I put the red text there, I seem to lose the filename from the save as box?

 

[Eric Golf]

Hi,

Not sure about getting the UNC, unless you hard code the filepath in. Try Thisworkbook.fullname to get the filename as well.

Eric.

 

[orangebloss]

Hi,

Not sure about getting the UNC, unless you hard code the filepath in. Try Thisworkbook.fullname to get the filename as well.

Eric.

Thank Eric I'll give it a go and see if it works!

 

[baitmaster]

actually I don't think UNC is relevant as we're only saving the file on individual machines, not working with network locations. If the user has a mapped drive then it won't affect the file or other users in this case. It would be relevant if inserting hyperlinks or testing the network location, but in this case we're fine, sorry for the distraction

use

thisworkbook.Path & application.PathSeparator

to get the current folder location including the "/" at the end