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

orangebloss

Board Regular
Joined
Jun 5, 2013
Messages
51
Office Version
  1. 365
Platform
  1. Windows
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 :

Code:
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)?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
Code:
Dim strDefault As String: strDefault = Application.DefaultFilePath
Application.DefaultFilePath = "[URL="file://\\new\path\here"]\\new\path\here[/URL]"

[ code here]

Application.DefaultFilePath = strDefault
 
Upvote 0
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:-

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

Hope this helps,

Eric.
 
Last edited:
Upvote 0
you'll have to change the application.defaultfilepath setting


Code:
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!
 
Upvote 0
Hi Eric, if I put the red text there, I seem to lose the filename from the save as box?
 
Upvote 0
Hi,

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

Eric.
 
Upvote 0
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
Code:
thisworkbook.Path & application.PathSeparator
to get the current folder location including the "/" at the end
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top