Behaviour of Save as and Displayalerts

[Hari]

Hi,

I have a file by the name ABC format. I open this file and perform a couple
of operations through macros. At the end of the operation I want to save the
file by appending today's date in the format dd-mm-yy.

This saving operation is performed at the end of the code. I have pasted the
last lines of the code where the saving operation is being performed.

It is possible that the new name of the file by which I m saving already
exists. In my case I would simply want it to be overwritten and replaced
with the new copy of the file.

The problem is that if the file exists then a message is displayed saying
that do u want to replace the file, etc.
I understand that one might supress this message by adding
application.displayalerts = false before the "Active workbook.SaveAs"
statement and then again introduce a statement application.displayalerts =
true just after the "Active workbook.SaveAs" statement.

My query is whether by using displayalerts the previous file is overwritten
or not. I tried it by creating a file of the same name( ABC 10-Jun-04.xls)
with junk data and when I ran this macro the file I did have a new and
correct file.

The concern I have is when we have a file open and use the save as option
from file menu and save the file without changing the file name in the same
location then excel prompts whether we want to replace the existing file. In
that the option yes and no appears and by default "No" is active. Hence, the
question is if , No is active in this case and in the similar line when we
do application.displayalerts = false in the macro case doesnt excel take or
assume our default response to be "No" for replacing the file. How does
excel read our response to be yes in case of application.displayalerts =
false ?


ActiveWorkbook.SaveAs filename:= _
"C:\Documents and Settings\hprasadh\Desktop\Pil\Project comparison\ABC
format " & " " & CStr(Format(Now, "dd-mmm-yy")), FileFormat:=xlNormal

Regards,
Hari
India

 

[David McRitchie]

Hi Hari,
See the top of my Backup page, and you want to put the
date in the form yyyy-mm-dd or yyyy_mmdd something
that you can sort the directory on.
http://www.mvps.org/dmcritchie/excel/backup.htm

If the file might already exist and your intent is to overwrite it
the best thing to do is to delete the file and ignore the error
from deleting the file before creating the new backup file.
Then exception coding is hardly a concern. Your main concern
would probably be in creating the new file that the directory
you want to put it in already exists, once you have your directory
as indicated in the macro it should continue to work on and on.

 

[Hari]

Hi David,

Thanx a lot. The info u have on backup's is exhaustive.

Specially thanx to you for that great tip of writng file names in the format
yyyy-mm-dd or yyyy_mmdd which will be the right one for sorting. Its a
ver very neat method. Thanx again.I have already incorporated it in my code
and will follow this convention

I was just wondering , in excel under format cells, date, locale settings,
International (ISO) the settings they have is "yyyy-mm-dd" or "yyyymmdd" and
so on. Is that convention present from the sorting perspective or there are
other equally good reasons for ISO to adopt such a Format for date.

Regards,
Har
India

Hi Hari,
See the top of my Backup page, and you want to put the
date in the form yyyy-mm-dd or yyyy_mmdd something
that you can sort the directory on.
http://www.mvps.org/dmcritchie/excel/backup.htm

If the file might already exist and your intent is to overwrite it
the best thing to do is to delete the file and ignore the error
from deleting the file before creating the new backup file.
Then exception coding is hardly a concern. Your main concern
would probably be in creating the new file that the directory
you want to put it in already exists, once you have your directory
as indicated in the macro it should continue to work on and on.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I have a file by the name ABC format. I open this file and perform a couple
of operations through macros. At the end of the operation I want to save the
file by appending today's date in the format dd-mm-yy.

 

[David McRitchie]

Hi Hari,
Dates within Excel are sorted on the serial number days from
a certain date (usually Dec 31, 1899) so it doesn't matter
how you format them. As far as I'm concerned the world
blew it by not changing over to ISO date format with 2000
for all documents.

I wish I could show all dates yyyy-mm-dd by default
(Regional dates) and enter them as mm/dd/yy but that
wouldn't make sense except to me. When I change my
format to yyyy-mm-dd I create the similar types of problems
that other country's have with our US date format. The
ISO format and order is ONLY going to make sense with
four digit years which is part of the specification along with
the use of hyphens and not slashes.

You can always show the format in whatever order you want,
but Excel takes it to mean that if the order matches your
regional format then you don't have a format but it is the
regional format and would change if given to someone else.

So if I make a point of showing the year with four digits the next
person will only see four digit years if that is what they use in their
own regional formats.