Macro save dir & filename

Question

Hi,

I have the following bit of code which suggests a name for the user through the save as dialog to save a file.  I would like to modify it to add the directory.  The directory is already defined in another part of the macro.

Can someone suggest where can i add & directory to have it displayed in the save as with the file name?

Thanks for your help!

Response = Application.Dialogs(xlDialogSaveAs).Show("Donnez moi un nom")

    If Response = False Then

        MsgBox "Filename"

        Exit Sub

    End If

Answer 1

Hi,

try this

Sub SaveAs_02()

'Nov 18, 2014

On Error GoTo errh

Dim cel

cel = Range("E1").Value   '<< file name e.g. file1, file001, book1,

ChDrive "e"

ChDir "e:\tmp"

Dim fName

fName = Application.GetSaveAsFilename(cel, filefilter:="Excel Files <.xlsm> (*.xlsm), *.xlsm")

If fName <> False Then

If MsgBox("save as " & vbCr & fName, vbOKCancel) = vbCancel Then Exit Sub

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=52 'xlsm

Application.DisplayAlerts = True

Else

MsgBox "cancel"

End If

Exit Sub

errh:

MsgBox "wrong, try again"

End Sub

Answer 2

Hi,

Rather than having to write the path into the code i want it to = range e1.  The path is variable depending on the user hence why i defined the variable directory.  Directory is used in other parts of the macro.

I would like to incorporate the variable directory into the save as.

Answer 3

Hi,

try this approach

Sub ChDir_SaveAs_01()

'Nov 18, 2014

Dim fName

ChDrive "E"

ChDir "E:\tmp folder"

Application.DisplayAlerts = False

fName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xlsm), *.xlsm")

If fName <> False Then

MsgBox "save as " & vbCr & fName

ActiveWorkbook.SaveAs Filename:=fName

Else

MsgBox "cancel"

End If

Application.DisplayAlerts = True

End Sub

Answer 4

Hi,

I tried this.  The code runs but when the save as dialog box opens it is not on the right path (directory) in this case.  It stays on the same path as the original file.

Any ideas on how i can force it to take the path in E1?

Thanks   

    directory = Range("E1")

    ChDir directory

    Response = Application.Dialogs(xlDialogSaveAs).Show("text")

    If Response = False Then

        MsgBox "text"

        Range("A1").Select

        Exit Sub

    End If

Answer 5

Here is one way:

Sub Itsmesens()

    ChDrive "F"

    ChDir "F:\Transfer"

    Response = Application.Dialogs(xlDialogSaveAs).Show("Donnez moi un nom")

    If Response = False Then

        MsgBox "Filename"

        Exit Sub

    End If

End Sub