marco for save as

[tim963]

When I use the Marco recorder for save as I get the code below, is there any way to just have the save as dialog box come up so you can put in a name in it? Using excel ‘97

ActiveWorkbook.SaveAs FileName:="C:My DocumentsBook1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

 

[Burger01]

Application.GetSaveAsFilename

Should do the trick.

 

[Ivan F Moala]

On 2002-08-25 19:34, tim963 wrote:
When I use the Marco recorder for save as I get the code below, is there any way to just have the save as dialog box come up so you can put in a name in it? Using excel ‘97

ActiveWorkbook.SaveAs FileName:="C:My DocumentsBook1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Hellow tim963
Look up help for ;
Built-In Dialog Box Argument Lists
AND
GetSaveAsFilename Method

ie. Goto VBA Editor
Press F1
Type in the key words to search for

This should get you started.
Post if you have any further Q

 

[Burger01]

Good idea Ivan.

"One could catch fish for a man to feed him and his family for a week... one could also teach a man to fish, and feed him for a whole lifetime.". Someone wrote that sort of thing somewhere... unless I made it up!

Burger
This message was edited by Burger01 on 2002-08-25 22:09

 

[Ivan F Moala]

On 2002-08-25 22:09, Burger01 wrote:
Good idea Ivan.

"One could catch fish for a man to feed him and his family for a week... one could also teach a man to fish, and feed him for a whole lifetime.". Someone wrote that sort of thing somewhere... unless I made it up!

Burger
This message was edited by Burger01 on 2002-08-25 22:09

Burger, yes it was a wise man who said that .

 

[tim963]

Got this so far but, How come when I run this sub it is not automatically saved as an excel workbook, and will not make a backup?

Sub Save_()
fname = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs FileName:=fname, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=True
End Sub

 

[dsnbld]

Give this a try.<pre>Sub SaveAs()

Do
getfilename = Application.GetSaveAsFilename _
(FileFilter:="Microsoft Excel WorkBook(*.xls),*.xls,Microsoft Excel Template(*.xlt),*xlt")

If getfilename = False Then
'MsgBox "Save As was cancelled, workbook was not saved.", vbOKOnly + vbExclamation, "Save As"
Exit Sub
End If

getfilename_exist = Dir(getfilename)

If getfilename_exist<> "" Then
Select Case MsgBox("The file [" & getfilename_exist & "] already exist in this location, do you want to replace the existing file?", vbYesNoCancel + vbExclamation, "Save As")
Case vbYes
Application.DisplayAlerts = False
Case vbNo
getfilename = ""
Case vbCancel
'MsgBox "Save As was cancelled, workbook was not saved.", vbOKOnly + vbExclamation, "Save As"
Exit Sub
End Select
End If

Loop Until getfilename<> ""

Application.EnableEvents = False 'comment this line to not by-pass Workbook_BeforeSave event
ThisWorkbook.SaveAs Filename:=getfilename, CreateBackup:=True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub</pre>


_________________
Win 98SE - Office 2K
This message was edited by dsnbld on 2002-08-26 07:30

 

[tim963]

Thanks that works great!