PDA

View Full Version : Solved: Save As



jammer6_9
05-08-2007, 12:45 AM
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?

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

moa
05-08-2007, 01:15 AM
Not sure about the best approach but look up the GetSaveAsFilename method in help.

lynnnow
05-08-2007, 01:16 AM
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
05-08-2007, 01:55 AM
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
05-08-2007, 02:02 AM
Hi Jammer

This works, try this.


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

HTH

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

jammer6_9
05-08-2007, 02:09 AM
Perfect lynnnow thanks.


Hi Jammer

This works, try this.


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

HTH

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

lynnnow
05-08-2007, 02:14 AM
Hey Jammer,

Glad to be of help.

Lynnnow

jammer6_9
05-08-2007, 02:24 AM
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 :dunno

If filesaver1 <> False Then
MsgBox "Save as " & filesaver1
End If

jammer6_9
05-08-2007, 02:31 AM
I guess i found solution...

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

lynnnow
05-08-2007, 02:35 AM
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.

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

HTH

Lynnnow

jammer6_9
05-08-2007, 02:35 AM
Now I have error in Save as with this code :banghead:


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

lynnnow
05-08-2007, 02:41 AM
wotz the error u're getting coz it worked fine with me.

jammer6_9
05-08-2007, 02:51 AM
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
05-08-2007, 03:20 AM
You're right lynnnow... The code below works fine... Shukrya...:friends:

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

Charlize
05-08-2007, 03:26 AM
Better try this. Will be saved as xls. Yours just a file without extension if you don't type it in.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 SubCharlize

jammer6_9
05-08-2007, 03:41 AM
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.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 SubCharlize

lynnnow
05-08-2007, 03:46 AM
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
05-08-2007, 03:59 AM
Better do this instead of gotoSub 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 SubCharlize

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
05-08-2007, 04:02 AM
Won't a goto equate to a do while loop? just asking, pls assist

moa
05-08-2007, 04:19 AM
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
05-08-2007, 04:23 AM
k, get it. thanks moa

Charlize
05-08-2007, 04:27 AM
Won't a goto equate to a do while loop? just asking, pls assistI only use a goto when I use errorchecking. I place the errochecking at the end of a module and in the beginning I specify that on an error jump to the part that resolves the error to quit the module properly.

The result will probably be the same but why not using the loop-commands that exists. Same with For Next, you can do it with a simple counter, If Then Else and a goto-label

That's the same discussion as why using case select when you can use if then else ?

I don't know it why, but I prefer it this way because it is clear that it is a loop and I don't have to search for the place of the goto-label to decide if it is a loop .

Charlize :2p:

lynnnow
05-08-2007, 04:32 AM
Thanks charlize