Macro: open save as dialogue, prefill filename and path BUT DO NOT SAVE YET

zach9208

Board Regular
Joined
Dec 15, 2015
Messages
117
I am looking for a macro that will populate the save as dialogue box to a specified file path and with a custom save name based on the cell value of (d4) of sheet2 . I want the user to be able to review one last time and manually click the save button.

In other words, I want the populate the dialogue box first, with the file path I selected pre-populate, along with my selected file name also prepopulated.I have tried everything! Please help.

Code:
Sub Macro1()
Application.Dialogs(xlDialogSaveAs).Show "C:\Users\c755748\Desktop\" & Sheet2.Range("D4") & ".xlsm"
End Sub
 
For post #2, I would do it like this.
Code:
Sub Main()
  Dim dt As String, fn As Variant
  dt = CreateObject("WScript.Shell").SpecialFolders("Desktop") * "\"
  fn = Application.GetSaveAsFilename(dt & Sheet2.Range("D4").Value & ".xlsm", _
     fileFilter:="Excel Marcro-Enabled Workbook (*.xlsm),*.xlsm")
  MsgBox fn
End Sub
 
Last edited:
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I should have mentioned that I was only using the desktop as a test... I really need it to drop to a specific folder in a shared drive at work. I tried the code
Code:
Application.Dialogs(xlDialogSaveAs).Show CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Sheet2.Range("D4").Value, 52
and it worked for the desktop but not sure if I can even tweak this for a specific folder with subfolders.

I'm pretty sure it's not a 2013 bug. I run 2013 myself...

You can check a couple of things:

The dialog will show the path specified, but if the path is wrong/doesn't exist, the default path will be shown instead.
So are you absolutely sure the path ("C:\Users\c755748\Desktop") is correct/exists?

If this is the path to YOUR desktop and the user using this, is on a different machine, I can say with 99% certainty, thet the path won't be the same.
You need to "catch" the users desktop path.
In this case you could use Kenneth Hobson's solution, or:

As I was going to suggest:
Code:
Application.Dialogs(xlDialogSaveAs).Show Environ("USERPROFILE") & "\Desktop\" & Sheet2.Range("D4").Value, 52
But Kenneths codepiece seems much more reliable:
Code:
Application.Dialogs(xlDialogSaveAs).Show CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Sheet2.Range("D4").Value, 52
Thank you for that Kenneth Hobson, didn't know this one...

You could also try to replace double backslashes to be absolutely sure (I'm not sure if there's any downfall to this. UNC paths have doubleslashes but I would never expect the Desktop to reside on a UNC path. Is that just me being ignorant?):
Code:
Application.Dialogs(xlDialogSaveAs).Show [COLOR=#ff0000]Replace([/COLOR]CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Sheet2.Range("D4").Value[COLOR=#ff0000], "\\", "\")[/COLOR], 52

Other things to try:

What is the exact value in Sheet2("D4")? If for example the value in D4 is something like this: "\FileName", the path the dialog is trying to navigate to would be this:
C:\Users\c755748\Desktop\\FileName
Which is incorrect/doesn't exist (note the two \\), and therefore the dialog will go to the default path (you could try the Replace function to correct this or correct it manually in D4).

You can try this in the Immediate window and doublecheck that the path is correct:
Code:
MsgBox "C:\Users\c755748\Desktop\" & Sheet2.Range("D4").Value

Have you tried tygrrboi's suggestion with the Immediate window? What's the result?
 
Upvote 0
Also, yes I have confirmed that C:\Users\c755748\Desktop\ is a valid file path using windows explorer. I would post the real file path but my company is strict about security.
 
Upvote 0
I really need it to drop to a specific folder in a shared drive at work.

I think this is your real problem.

Are you saying that the workbook is placed in a shared drive/server and the user trying to save will be presented with a savepath locally on his computer?

Or:

Are you saying that the workbook is located locally on the users computer and the savepath should be on the shared drive/server?
 
Upvote 0
I am thinking this is maybe a security patch that the company has put into place. I can tell that the default path is set to go to the right place, it appears to just override the file path! It kindof makes sense that they would prevent a macro from dictating the file location in a macro... anyone else think this is a possibility?
 
Upvote 0
what is really odd is that the following code works perfectly at automatically saving the file to the drive... yet all of the suggestions will not have the save as box populate the file path to the right spot... so weird.

Code:
  Sub SaveWithVariableFromCell()
       Dim SaveName As String
       SaveName = ActiveSheet.Range("D4").Text
       ActiveWorkbook.SaveAs Filename:="C:\Users\c755748\Desktop\" & _
           SaveName & ".xlsm"
   End Sub
 
Last edited:
Upvote 0
It is on a shared drive on a server..


So it is located on the server and the savepath should also be on the server?

If the above is the case, you could try to "capture" the workbook's path and "navigate" from there, for example to a subfolder (in the Macro1() further down):

Code:
wbPath = wbPath & "SubFolder\"

Code:
Sub Macro1()
Dim wbPath As String
wbPath = ThisWorkbook.Path
If Not Right(wbPath, 1) = "\" Then wbPath = wbPath & "\"
Application.Dialogs(xlDialogSaveAs).Show wbPath & Sheet2.Range("D4") & ".xlsm"
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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