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

 

[tygrrboi]

Have you tried this?

Application.DefaultFilePath = "C:\Users\c755748\Desktop\"
fileName = Application.GetSaveAsFilename(InitialFileName:=Sheet2.Range("D4").Value, _
     fileFilter:="Excel Marcro-Enabled Workbook (*.xlsm),*.xlsm")

 

[zach9208]

I tried this code and can not get it to show the file path. File name populated though.

 

[tygrrboi]

I can get Application.DefaultFilePath to open up the correct directory every time. If I assign a directory which does not exist, it just remains what it used to be initially. No error.

Are you sure you are assigning it the correct file path?

A thought just occurred to me... does your path continue in the cell's contents? For example in the code above your file ideally would like to be
C:\Users\c755748\Desktop\NameOfFile<contents of="" cell="">.xlsm

Does the cell continue to define the path? like D4 = "New Folder\Files\NameOfFile" so you want the full path to be
C:\Users\c755748\Desktop\New Folder\Files\

or will your path be only the Desktop?
</contents>

 

[zach9208]

No I am not looking to drop to a different subfolder. It seems to be taking me to my most recent file path not the one directed in the path. I have even tried changing the path and it makes no difference.

 

[BQardi]

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")[COLOR=#ff0000] & ".xlsm"[/COLOR]
End Sub

Just loose the & ".xlsm" and add , 52 instead:

Sub Macro1()
Application.Dialogs(xlDialogSaveAs).Show "C:\Users\c755748\Desktop\" & Sheet2.Range("D4")[COLOR=#ff0000], 52[/COLOR]
End Sub

 

[zach9208]

I tried both of these suggestions and still cannot get it to work. I am wondering if this is an excel 2013 bug?

Just loose the & ".xlsm" and add , 52 instead:

Sub Macro1()
Application.Dialogs(xlDialogSaveAs).Show "C:\Users\c755748\Desktop\" & Sheet2.Range("D4")[COLOR=#ff0000], 52[/COLOR]
End Sub

 

[tygrrboi]

Just out of curiosity. What do you get when you type this into your immediate window?

MsgBox "The current default file path is " & Application.DefaultFilePath

Then type

Application.DefaultFilePath = "C:\Users\c755748\Desktop\" : [COLOR=#000000][FONT=Consolas]MsgBox "The current default file path is " & Application.DefaultFilePath
[/FONT][/COLOR]

 

[Kenneth Hobson]

Sub Test_FileSaveAs()
  Dim fn As String
  fn = FileSaveAs(CreateObject("WScript.Shell").SpecialFolders("Desktop") _
    & "\" & Sheet2.Range("D4").Value & ".xlsm")
  'MsgBox fn
  If fn <> "" Then ActiveWorkbook.SaveAs fn, 52
End Sub

Function FileSaveAs(initialFilename As String, _
  Optional sDesc As String = "Excel (*.xlsm)", _
  Optional sFilter As String = "*.xlsm") As String
  With Application.FileDialog(msoFileDialogOpen)
    .ButtonName = "&SaveAs"
    .initialFilename = initialFilename
    .Filters.Clear
    .Filters.Add sDesc, sFilter, 1
    .Title = "File SaveAs"
    .AllowMultiSelect = False
    '.Execute 'Executes the action of the dialog.
    If .Show = -1 Then FileSaveAs = .SelectedItems(1)
  End With
End Function

 

[BQardi]

I tried both of these suggestions and still cannot get it to work. I am wondering if this is an excel 2013 bug?

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?