VBA code for Excel File Save As

[Guest]

Hi,

I'm building a code where at some point I need to save the file to a new location with a specific file name, which is also specified by the code. I would like to be prompted for this but it only saves if I specify the path.

This is the bit I have already written and can't go further:

Dim NewName As String

NewName = mRateName & " " & Validade & ".xls"

If MsgBox("Save file as " & NewName & "?", vbYesNo, Save) = vbNo Then

Exit Sub

ActiveWorkbook.SaveAs FileName:=NewName

Can anyone help me?

Thanks

 

[Ron de Bruin]

You can use GetSaveAsFilename

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files (*.xls), *.xls")
If fname <> False Then
Wb.SaveAs fname
'Wb.Close False
Set Wb = Nothing
Else
Set Wb = Nothing
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi,

I'm building a code where at some point I need to save the file to a new location with a specific file name, which is also

specified by the code. I would like to be prompted for this but it only saves if I specify the path.

 

[Norman Jones]

Hi khlaudhya,

If mRateName and Validade are strings, you need to enclose them with double
quotes.

Similarly, you need to enclose the title argument (Save) of the msgbox in
quotes. You also need to provide an End If to close your If clause.

Incorporating thesr changes your code works for me and becomes:
Sub Test
Dim NewName As String

NewName = "mratename" & " " & "Validade" & ".xls"

If MsgBox("Save file as " & NewName & "?", vbYesNo, "Save") = vbNo Then

Exit Sub
End If

ActiveWorkbook.SaveAs Filename:=NewName
End Sub

The new file will be saved your default file location. If you wish to save
to another folder, simply prefix NewName with the full folder path.


---
Regards,
Norman

Hi,

I'm building a code where at some point I need to save the file to a new

location with a specific file name, which is also specified by the code. I
would like to be prompted for this but it only saves if I specify the path.

 

[Norman Jones]

Hi Khlaudhya,

Reading Ron's reply, I realise that I failed to appreciate that you want the
user to provide the path whilst the code designates the file name.


---
Regards,
Norman

Hi,

I'm building a code where at some point I need to save the file to a new

location with a specific file name, which is also specified by the code. I
would like to be prompted for this but it only saves if I specify the path.

 

[Guest]

Hi Ron,

thank you very much, this was really helpful.

Regards,

Khlaudhya

 

[Guest]

Hi Norman,

thanks for trying to help but Ron really did answer what I was asking.

Regards,

Khlaudhya

 

[LRay67]

Ron your code works great. Is there a way to incorporate into the code that
allows me to pick up a textbox with the data entered in there to be part of
the file name upon saving the workbook?