macro to save file?

Posted by mike on February 14, 2002 5:09 AM

How do I program a macro that asks the user to input the save name for a workbook and then saves the book?

regards mike

Posted by Rick E. on February 14, 2002 6:02 AM

What you asked is simple BUT requires a lot of additional information and coding. First it is simple to ask for the name of the file, as in:

str1 = "Enter the name of the Excel wookbook to save."
NewName = InputBox(str1, "Enter Name")

Next you have to check that what was entered is a valid name (no special characters), and did they add the .xls to the name or not?

From there you must know where to save the file and change the directory to that location, i.e.

ChDir "C:\TEMP"

Next check to see that the file does not already exist or figure out what to do if it does.

Finally build the correct file name and use the "SaveAs" function to write out the file,

str2 = "C:\TEMP\" & NewName & ".xls"
ActiveWorkbook.SaveAs Filename:=str2

Good luck on your project, Rick E.