SaveAs Cell.value=filename

[Guest]

Hi, I am using the following statement:

Application.Dialogs(xlDialogSaveAs).Show FullFilePath, vbNormalFocus

How can I include in this statement a Filename which is in a Cell.value in
order that the user can select the appropriate directory for him/her but the
filename is fixed.
thanks
Helmut

 

[Bob Phillips]

That gets the whole filename, you need FileDialog as I showed you in the
previous thread.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Hi Helmut,

Please try the following:
In cell A1 (as example) you place the following formula:
=If(B1="Lucy";"C:\Lucy\";"")&ifIf(B1="Helmut";"C:\Helmut\";"").... as
much varable you want
You write on cell A2 the following formula:
=A1&"FILENAME.xls"

In your VBA code, you write:

sub filename()
Dim FILENAME as string
FILENAME=range("A2").value
ActiveWorkbook.SaveAs Filename:=FILENAME
end sub

You will have your file saved with the name you pre-selected on the path of
the user.
If it doesn't work, please react.

Regards,

 

[Guest]

Sorry Helmut, there is a "IF" repeated on the 2nd variable of cell A1.

 

[Guest]

Hi Monica,
the following did it:


Dim SvPath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = False Then Exit Sub
SvPath = .SelectedItems(1)
End With
ActiveWorkbook.SaveAs SvPath & "\" & Range("Fname")