Another Question about Save As Dialog box

[Pete Rooney]

Good morning,

I want to be able to display the Save As Dialog box - but - before I do it,
I want to change the default Save As folder to whatever value is contained in
a cell on a worksheet so when the dialog is displayed, it's pointing to the
specified folder.

Ideally, I also want the filename box to contain a value contained in
another variable, based on other cells in the worksheet, which I don't have a
problem with, so that the Save As dialog box shows the variablein the File
Name field, but which the user has the opportunity to change before clicking
"Save".

So far, here's what I have, but the first problem is, when the dialog box is
displayed, the folder showing is the one where the workbook was last saved,
NOT the folder based on the value in the cell.

The value in the folder cell is valid, as I don't get an error message at
the CHDIR command - it just isn't reflected in the SaveAs dialog box.

Also, I don't have any idea yet as to how to put the user-amendable value in
the File Name field yet.


Here's what I have so far:


Sub AATest()

Dim DropDownsSheet As Worksheet
Dim DefaultFolderCell As Range
Dim DefaultFolderValue As String

Set DropDownsSheet = ActiveWorkbook.Worksheets("Dropdowns")
Set DefaultFolderCell = DropDownsSheet.Range("DefaultFolder")
DefaultFolderValue = DefaultFolderCell.Value

MsgBox (DefaultFolderValue)
ChDir DefaultFolderValue
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Thanks in advance for your help.

Regards

Pete

 

[Mike H]

Pete,

Try this

Sub AATest()
savedir = Sheets("Sheet1").Range("A1").Value
fname = Sheets("Sheet1").Range("A2").Value
Application.Dialogs(xlDialogOpen).Show ("c:\" & savedir & "\" & fname)
End Sub

Mike

 

[Pete Rooney]

Mike,

Off to a meeting now, but will try it when I get back.
Thanks
Pete

 

[Pete Rooney]

Mike,

Yep - this took me to where I needed to be.

Thank you!

Pete