Saving a file while recording a macro

R

Richard Champlin

Once I have opened up a spreadsheet that I received in an e-mail, and
utilizing a macro to format it the way I want it, is it possible to then save
the worksheet in a particular location and with a standard name as the final
steps in the macro?
 
G

Gary''s Student

Just include lines like:

ChDir "C:\temp"
ActiveWorkbook.SaveAs Filename:="C:\temp\Book1.xls"
 
R

Richard Champlin

I take it I could use any drive & folder designation (rather than "C:\temp")?

Is it possible to specify a filename that reflects the filename in the
attachment worksheet I received?

Thanks!
 
J

Jim Thomlinson

For better or for worse my preference is to display the save as dialog. That
way you are in a well recognized postion to save your workbook in your
choosen directory with a proposed name that fits your general guidelines. It
also allows you to madify that at run time if you choose...

Application.Dialogs(xlDialogSaveAs).Show "C:\" & ActiveWorkbook.Name &
Format(Now, "yyyymmdd")

The above line of code will bring up the SaveAs dailog pointing at the Root
of C (you can change that) with the proposed name being the existing file
name with the Date appended to it.
 
G

Gary''s Student

Yes - any drive - any folder

ChDir "C:\temp"
s = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:="C:\temp\" & s

This uses the same name as the file you get.
 
R

Richard Champlin

I tried this, and wound up with an error message. I tried your first
suggestion, and it worked like a charm. But after I changed the code to
incorporate this one, and then tried to change it back, it gave me an error
message saying "path not found"...I tried to debug, and it highlighted the
first line of this code:

ChDir " O:\Open Purchase Orders\Cardinal POs\2007"
ActiveWorkbook.SaveAs Filename:=" O:\Open Purchase Orders\Cardinal
POs\2007\Latest Cardinal.xls"
End Sub

Where have I screwed up?
 
G

Gary''s Student

I don't see an obvious error, but I can't duplicate you drive/folder structure
 
Top