How to save a file

[hon123456]

Dear all,

I want to place a button in a worksheet, which will ask for
a filename, after the user input the
filename, I want to save the file to a particular directory, how can I
do that? I do not want to use the common dialog box. I want to use an
inputbox which ask for filename, after the user input filename in
inputbox, then I will immdeately save the file, what is the function to
set the saving directory and what is the function to save(write) a
excel file?

Thanks.

 

[Norman Jones]

Hi Hon123456,

Try something like:

'================>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim res As String
Const sPath As String = "C:\aFolder\" '<<=== CHANGE

Set WB = ActiveWorkbook '<<=== CHANGE

res = InputBox("Please enter new file name")

WB.SaveAs Filename:=sPath & res, _
FileFormat:=xlWorkbookNormal

End Sub
'<<================

Note that no error checking has been included.

 

[hon123456]

Thanks for your help

 

[Guest]

Norman,
I have some code that we hired out and when a file is automatically opened
it requires you to save it somewhere. Unfortunately that "somewhere" can be
anywhere and I prefer to make it a common networked drive and folder. That
would be "W:\ICC\OrderNet Proposals\", all members have the "W" mapped.
However, it is so political to get money for customizations and then it goes
into this I.T. vortex of not a high enough priority. Can someone look at
this code, I believe I copied it all correctly and provided the important
stuff, if you need more of the code let me know.
..
..Previous code stuff
..
Resave:
filesavename = Application.GetSaveAsFilename( _
strFileName, fileFilter:="Excel Files (*.xls), *.xls")
If filesavename <> False Then
ActiveWorkbook.SaveAs Filename:= _
filesavename, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
MsgBox "You must enter a name for the file"
GoTo Resave
End If

 

[Norman Jones]

Hi Rookie_User,

Try
'=============>>
Public Sub Tester()
Dim filesavename As Variant
Dim strFileName As String
Dim MyPath As String '<<=== NEW LINE
Dim OldPath As String '<<=== NEW LINE

OldPath = CurDir '<<=== NEW LINE
MyPath = "W:\ICC\OrderNet Proposals\" '<<=== NEW LINE

ChDrive MyPath '<<=== NEW LINE
ChDir MyPath '<<=== NEW LINE

Resave:
filesavename = Application.GetSaveAsFilename( _
strFileName, filefilter:="Excel Files (*.xls), *.xls")
If filesavename <> False Then
ActiveWorkbook.SaveAs Filename:= _
filesavename, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

ChDrive OldPath '<<=== NEW LINE
ChDir OldPath '<<=== NEW LINE

Else
MsgBox "You must enter a name for the file"
GoTo Resave
End If

End Sub
'<<=============