I made a macro to perform a "save as" and give it a file name as you see
below. I now need it to create the file name based on the date ...

ActiveWorkbook.SaveAs "c:\mypath\currentdump.xls"

I need it to save the file as the followint format.. (replace the mmddyyyy
with the date...)

ActiveWorkbook.SaveAs "c:\mypath\mmddyyyy.xls"

What do I need to do to accomplish this?

Thanks

ActiveWorkbook.SaveAs "c:\mypath\" & Format(Date,"mmddyyyy") & ".xls"


--

John Green - Excel MVP
Sydney
Australia


"Tim_S" wrote in message ...
I made a macro to perform a "save as" and give it a file name as you see
below. I now need it to create the file name based on the date ...

ActiveWorkbook.SaveAs "c:\mypath\currentdump.xls"

I need it to save the file as the followint format.. (replace the mmddyyyy
with the date...)

ActiveWorkbook.SaveAs "c:\mypath\mmddyyyy.xls"

What do I need to do to accomplish this?

Thanks

I would suggest you use

ActiveWorkbook.SaveAs "c:\mypath\" & Format(Date,"yyyymmdd") & ".xls"

so that if you sort the files created they are sorted in data order with
all the files for this year sorted before any files for next year - and all
files for Jan this year are sorted before the ones for Feb. Your way
with mmddyy will sort all files from Jan in any year before any files
for any other month.

Chrissy/



"John Green" wrote in message ...
ActiveWorkbook.SaveAs "c:\mypath\" & Format(Date,"mmddyyyy") & ".xls"


--

John Green - Excel MVP
Sydney
Australia


"Tim_S" wrote in message ...
I made a macro to perform a "save as" and give it a file name as you see
below. I now need it to create the file name based on the date ...

ActiveWorkbook.SaveAs "c:\mypath\currentdump.xls"

I need it to save the file as the followint format.. (replace the mmddyyyy
with the date...)

ActiveWorkbook.SaveAs "c:\mypath\mmddyyyy.xls"

What do I need to do to accomplish this?

Thanks

Ok this works great.

Now another big question is how can I tell if an Excel document is open and
locked before I do a save as

For instance;
I have 1.xls open and want to save it as 2.xls but joe another user has
2.xls open. I get an error in the macro when the macro tries to save it.

Thanks




"Tim_S" wrote in message
...
I made a macro to perform a "save as" and give it a file name as you see
below. I now need it to create the file name based on the date ...

ActiveWorkbook.SaveAs "c:\mypath\currentdump.xls"

I need it to save the file as the followint format.. (replace the mmddyyyy
with the date...)

ActiveWorkbook.SaveAs "c:\mypath\mmddyyyy.xls"

What do I need to do to accomplish this?

Thanks

You should first consider the logic behind one used trying to
save a document to a name that another user has already used.
This implies that you need to come up with some naming
conventions which will give unique names or come up with
a procedure for deciding which document is the real 2.xls.

Having said that - the easiest way to do what you want to do
is with error trapping.

Try to do what you want and if it fails then check why it failed
and if it failed because it was already opened or existed the you
take appropriate action. Of course, depending on your answers
to the above, there may very well be a better solution for you.

Chrissy.



Tim_S wrote
Ok this works great.

Now another big question is how can I tell if an Excel document is open and
locked before I do a save as

For instance;
I have 1.xls open and want to save it as 2.xls but joe another user has
2.xls open. I get an error in the macro when the macro tries to save it.