Solved: Save As

[jammer6_9]

How can I set below code to let user enter the filename instead of giving direct "Training Audit.xls" and is it possible to let user save it in any location?

[vba]Sub macro5()
ChDir "C:\Documents and Settings\ofsjcr\Desktop\"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\ofsjcr\Desktop\Training Audit.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub[/vba]

[moa]

Not sure about the best approach but look up the GetSaveAsFilename method in help.

[lynnnow]

Hey Jammer,

Have u tried the Application.GetSaveAsFilename option. I've not tried it for now, but i've used it before for saving as different file name. The location of where to save it needs to be worked on.

Lynnnow

[jammer6_9]

Could you show me a bit of the code? thanks

Hey Jammer,

Have u tried the Application.GetSaveAsFilename option. I've not tried it for now, but i've used it before for saving as different file name. The location of where to save it needs to be worked on.

Lynnnow

[lynnnow]

Hi Jammer

This works, try this.

[vba]
FileSaver1 = Application.GetSaveAsFilename() ' You can set an initial file name in this parentheses
ActiveWorkbook.SaveAs FileName:=FileSaver1
[/vba]
HTH

I had variables passing down the name of the file so i've deleted that part.
Lynnnow

[jammer6_9]

Perfect lynnnow thanks.

Hi Jammer

This works, try this.

[vba]
FileSaver1 = Application.GetSaveAsFilename() ' You can set an initial file name in this parentheses
ActiveWorkbook.SaveAs FileName:=FileSaver1
[/vba]
HTH

I had variables passing down the name of the file so i've deleted that part.
Lynnnow

[lynnnow]

Hey Jammer,

Glad to be of help.

Lynnnow

[jammer6_9]

one more thing Save as works very fine now but when I click cancel Error 1004 appears "Method SaveAs of Object WorkBook Failed". I was trying to put if condition like

[VBA]If filesaver1 <> False Then
MsgBox "Save as " & filesaver1
End If[/VBA]

[jammer6_9]

I guess i found solution...

[VBA]filesaver1 = Application.GetSaveAsFilename()
If filesaver1 <> False Then
MsgBox "Save as " & filesaver1
ActiveWorkbook.SaveAs Filename:=filesaver1
End If[/VBA]

[lynnnow]

Hey Jammer,

I tried out the scenario you've indicated, but I don't get any errors. I clicked on cancel and the If exited without problems. I tried putting a msgbox, that worked fine too.

this is wot i've used.

[VBA] FileSaver1 = Application.GetSaveAsFilename()
If FileSaver1 <> False Then
MsgBox "File will be saved: " & ActiveWorkbook.FullName
ActiveWorkbook.SaveAs FileName:=FileSaver1
End If[/VBA]

HTH

Lynnnow

[jammer6_9]

Now I have error in Save as with this code
[vba]

filesaver1 = Application.GetSaveAsFilename()
If filesaver1 <> False Then
MsgBox "Save as " & filesaver1
ActiveWorkbook.SaveAs Filename:=filesaver1
End If

[/vba]

[lynnnow]

wotz the error u're getting coz it worked fine with me.

[jammer6_9]

When I click SAVE in "Save Dialog Box its okay but when i click CANCEL Run-time error '1004'
Method 'SaveAs' of object'_WorkBook' Failed

wotz the error u're getting coz it worked fine with me.

[jammer6_9]

You're right lynnnow... The code below works fine... Shukrya...

[VBA]Sub macro5()
Dim filesaver1 As Variant
filesaver1 = Application.GetSaveAsFilename()
If filesaver1 <> False Then
MsgBox "File will be saved: " & ActiveWorkbook.FullName
ActiveWorkbook.SaveAs Filename:=filesaver1
End If
End Sub
[/VBA]

[Charlize]

Better try this. Will be saved as xls. Yours just a file without extension if you don't type it in.[VBA]Sub file_to_save()
Dim sFile As Variant
sFile = Application.GetSaveAsFilename(InitialFileName:="", _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save workbook to..")
If sFile <> False Then
ThisWorkbook.SaveAs Filename:=sFile
Else
MsgBox ("Please give a name ..."), vbInformation
End If
End Sub[/VBA]Charlize

[jammer6_9]

This is a "Charlize User Friendly Version"... Tnx...

Better try this. Will be saved as xls. Yours just a file without extension if you don't type it in.[vba]Sub file_to_save()
Dim sFile As Variant
sFile = Application.GetSaveAsFilename(InitialFileName:="", _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save workbook to..")
If sFile <> False Then
ThisWorkbook.SaveAs Filename:=sFile
Else
MsgBox ("Please give a name ..."), vbInformation
End If
End Sub[/vba]Charlize

[lynnnow]

glad to have got it solved. can use it in my work also. thanks charlize. However, it can be looped till the filename is provided like so.

Dim sFile As Variant
TryAgain: sFile = Application.GetSaveAsFilename(InitialFileName:="", _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save workbook to..")
If sFile <> False Then
ThisWorkbook.SaveAs Filename:=sFile
Else
MsgBox ("Please give a name ..."), vbInformation
Application.GoTo TryAgain
End If
didn't try it out coz i've got another macro running and don't want to interrupt it.

[Charlize]

Better do this instead of goto[vba]Sub file_to_save()
Dim sFile As Variant
Do While sFile = False
sFile = Application.GetSaveAsFilename(InitialFileName:="", _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save workbook to..")
If sFile <> False Then
ActiveWorkbook.SaveAs Filename:=sFile
Else
MsgBox ("Please give a name ..."), vbInformation
End If
Loop
End Sub[/vba]Charlize

ps.: They told me that trying to avoid goto's helps to keep the code clean and readible. It helps to avoid 'spaghetti-coding' because you follow a logical order in the code and not a jumping scenario.

[lynnnow]

Won't a goto equate to a do while loop? just asking, pls assist

[moa]

ps.: They told me that trying to avoid goto's helps to keep the code clean and readible. It helps to avoid 'spaghetti-coding' because you follow a logical order in the code and not a jumping scenario.