Save As Function

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
365
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

TFCJamieFay

Active Member
Joined
Oct 3, 2007
Messages
480
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
 
Upvote 0

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,734
Office Version
  1. 2019
Platform
  1. Windows
do you mean something like this?

Code:
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
 
Upvote 0

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
365
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!!!
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Take a look at Application.GetSaveAsFilename.

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

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,734
Office Version
  1. 2019
Platform
  1. Windows
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
Code:
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
 
Upvote 0

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
365
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
 
Upvote 0

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,734
Office Version
  1. 2019
Platform
  1. Windows
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
 
Upvote 0

Forum statistics

Threads
1,191,687
Messages
5,988,077
Members
440,125
Latest member
vincentchu2369

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top