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

[zach9208]

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.

Sub Macro1()
Application.Dialogs(xlDialogSaveAs).Show "C:\Users\c755748\Desktop\" & Sheet2.Range("D4") & ".xlsm"
End Sub

 

[Kenneth Hobson]

For post #2, I would do it like this.

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

 

[zach9208]

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

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:

Application.Dialogs(xlDialogSaveAs).Show Environ("USERPROFILE") & "\Desktop\" & Sheet2.Range("D4").Value, 52

But Kenneths codepiece seems much more reliable:

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

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:

MsgBox "C:\Users\c755748\Desktop\" & Sheet2.Range("D4").Value

Have you tried tygrrboi's suggestion with the Immediate window? What's the result?

 

[zach9208]

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.

 

[BQardi]

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?

 

[zach9208]

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?

 

[zach9208]

It is on a shared drive on a server..

 

[BQardi]

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?

 

[zach9208]

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.

  Sub SaveWithVariableFromCell()
       Dim SaveName As String
       SaveName = ActiveSheet.Range("D4").Text
       ActiveWorkbook.SaveAs Filename:="C:\Users\c755748\Desktop\" & _
           SaveName & ".xlsm"
   End Sub

 

[zach9208]

Correct.

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

 

[BQardi]

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

wbPath = wbPath & "SubFolder\"

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