Save As Function

[christianbiker]

I would like to have a button on a userform that when pressed will use the save as function to a specific location however will require the user to specify the actual file name.

Can someone please provide some assistance?

Thanks

 

[TFCJamieFay]

I think that in order for this to work properly the user will have to specify the file path unless it will always be saved in the same folder but with different names. I have had a go. in this example I have used my desktop to save the file on but you can change this to where ever you need.

Try:

Sub test()

Dim fName As String
Dim fPath As String

fName = UCase$(Application.InputBox(Prompt:="Enter the name you would like to call the file" _
, Title:="Save As Filename"))

ChDir "C:\Documents and Settings\jamie.fay\Desktop"
fPath = "C:\Documents and Settings\jamie.fay\Desktop\"
ActiveWorkbook.SaveAs Filename:=fPath & fName

End Sub

 

[iggydarsa]

do you mean something like this?

Private Sub CommandButton1_Click()
Dim fileName As String: fileName = ""
While fileName = ""
    fileName = InputBox("Enter a file name")
Wend
ThisWorkbook.SaveAs ("C:\Documents and Settings\jsmith\Desktop\" & fileName)
Unload Me
End Sub

 

[christianbiker]

ok they both work, but there are some glitches.

iggy, your's works however i cannot use the cancel button in the dialog box. it forces me to enter a name and save it. can this be resolved?

jamie, your's also works however if i do press the cancel button it closes the dialog box and proceeds to save a copy of the document any way and names it "FALSE". it also saves all filenames in capital letter which i don't want. can these be resolved?

thanks!!!

 

[Norie]

Take a look at Application.GetSaveAsFilename.

But remember that all it does it gets the name, you have to save the file.

 

[iggydarsa]

iggy, your's works however i cannot use the cancel button in the dialog box. it forces me to enter a name and save it. can this be resolved?

try this maybe

Private Sub CommandButton1_Click()
Dim fileName As String: fileName = ""
fileName = InputBox("Enter a file name")

If fileName = "" Then
    MsgBox "File name is not entered"
Else
    ThisWorkbook.SaveAs ("C:\Documents and Settings\jsmith\Desktop\" & fileName)
End If
Unload Me
End Sub

 

[christianbiker]

hey iggy, it still isn't working. the code says there is an error with the "Unload Me" part but doesn't really explain. i am using this with a button that is located in the spreadsheet itself, not a userform...does this matter? i would prefer to keep the button the way it is.

hi norie...thanks for the response. i originally started with that function however it takes me to my default location (My Documents) which can be easily done using the F12 function. i am really hoping to have the save as function go directly to a specific location and just have to input the filename.

thanks

 

[iggydarsa]

hey iggy, it still isn't working. the code says there is an error with the "Unload Me" part but doesn't really explain. i am using this with a button that is located in the spreadsheet itself, not a userform...does this matter? i would prefer to keep the button the way it is.

Yes, it does matter. Try removing that line and try again pls

 

[christianbiker]

AWESOME!!!

Perfect, thanks so much iggy.

 

[Norie]

hi norie...thanks for the response. i originally started with that function however it takes me to my default location

So change the directoy using ChDir.