Day before creation date in cell or as sheet name
 

Hi all
Thanks for reading this!
I'm trying to get the day before the creation date of a file into a cell -
or to set it as its sheet name. One small addition is that I need it as
text. So if the creation date is 06/02/06, I need to cell to be 050206 (as
text), if the creation date is 01/03/06 then the cell would be 280206. I've
got a macro that formats column A as Text and then fills it with the sheet
name, but I'd like to do it all automatically.
Thanks in advance.

Day before creation date in cell or as sheet name
 

You can use BuiltInDocumentProperties to get the creation date.
E.g.,

ThisWorkbook.BuiltinDocumentProperties("Creation Date")

How do you get 280206 from 1/3/2006?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




<Andy wrote in message
...
Hi all
Thanks for reading this!
I'm trying to get the day before the creation date of a file
into a cell - or to set it as its sheet name. One small
addition is that I need it as text. So if the creation date is
06/02/06, I need to cell to be 050206 (as text), if the
creation date is 01/03/06 then the cell would be 280206. I've
got a macro that formats column A as Text and then fills it
with the sheet name, but I'd like to do it all automatically.
Thanks in advance.

Day before creation date in cell or as sheet name
 

Chip Pearson wrote:

How do you get 280206 from 1/3/2006?

The 28th Feb is the day before the 1st March.


--
Gordon Rainsford

London UK

Day before creation date in cell or as sheet name
 

Try this

Sub test()
Dim str As String
str = Format(ThisWorkbook.BuiltinDocumentProperties _
("Creation Date") - 1, "dd-mm-yy")
ActiveSheet.Name = str
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


<Andy wrote in message ...
Hi all
Thanks for reading this!
I'm trying to get the day before the creation date of a file into a cell - or to set it as its sheet name. One small addition is
that I need it as text. So if the creation date is 06/02/06, I need to cell to be 050206 (as text), if the creation date is
01/03/06 then the cell would be 280206. I've got a macro that formats column A as Text and then fills it with the sheet name, but
I'd like to do it all automatically.
Thanks in advance.

Day before creation date in cell or as sheet name
 

Sorry - I'm in England! 01/03/2006 is the first of March (over here!) and
28/02/06 is the day before it!

Cheers.
Andy.

"Chip Pearson" wrote in message
...
You can use BuiltInDocumentProperties to get the creation date. E.g.,

ThisWorkbook.BuiltinDocumentProperties("Creation Date")

How do you get 280206 from 1/3/2006?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




<Andy wrote in message ...
Hi all
Thanks for reading this!
I'm trying to get the day before the creation date of a file into a
cell - or to set it as its sheet name. One small addition is that I need
it as text. So if the creation date is 06/02/06, I need to cell to be
050206 (as text), if the creation date is 01/03/06 then the cell would be
280206. I've got a macro that formats column A as Text and then fills it
with the sheet name, but I'd like to do it all automatically.
Thanks in advance.

Day before creation date in cell or as sheet name
 

Try code like

Debug.Print
Format(ThisWorkbook.BuiltinDocumentProperties("Cre ation Date") -
1, "ddmmyy")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


<Andy wrote in message
...
Sorry - I'm in England! 01/03/2006 is the first of March (over
here!) and 28/02/06 is the day before it!

Cheers.
Andy.

"Chip Pearson" wrote in message
...
You can use BuiltInDocumentProperties to get the creation
date. E.g.,

ThisWorkbook.BuiltinDocumentProperties("Creation Date")

How do you get 280206 from 1/3/2006?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




<Andy wrote in message
...
Hi all
Thanks for reading this!
I'm trying to get the day before the creation date of a file
into a cell - or to set it as its sheet name. One small
addition is that I need it as text. So if the creation date
is 06/02/06, I need to cell to be 050206 (as text), if the
creation date is 01/03/06 then the cell would be 280206. I've
got a macro that formats column A as Text and then fills it
with the sheet name, but I'd like to do it all automatically.
Thanks in advance.

Day before creation date in cell or as sheet name
 

I missed the part about the day before. My mistake.


"Gordon Rainsford" wrote in
message
news:1hby0gn.1uo2xkjgkqezqN%grbridgeREMOVETHIS@bti nternet.com...
Chip Pearson wrote:

How do you get 280206 from 1/3/2006?

The 28th Feb is the day before the 1st March.


--
Gordon Rainsford

London UK

Day before creation date in cell or as sheet name
 

Chip

Thanks for your reply. I have a problem, though! The file that I am working
on is an imported .txt and the macro crashes with a Run-time error 5 -
Invalid procedure call or argument.
The line I'm trying to use is this:
Range("A2").Value =
ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
and I'm wondering if it's because the sheet is not technically a workbook.
The macro imports a .txt file, deletes junk from it and formats some of the
columns and I need to get the creation date of the txt file.
Thanks.
Andy.


"Chip Pearson" wrote in message
...
Try code like

Debug.Print Format(ThisWorkbook.BuiltinDocumentProperties("Cre ation
Date") - 1, "ddmmyy")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


<Andy wrote in message ...
Sorry - I'm in England! 01/03/2006 is the first of March (over here!) and
28/02/06 is the day before it!

Cheers.
Andy.

"Chip Pearson" wrote in message
...
You can use BuiltInDocumentProperties to get the creation date. E.g.,

ThisWorkbook.BuiltinDocumentProperties("Creation Date")

How do you get 280206 from 1/3/2006?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




<Andy wrote in message ...
Hi all
Thanks for reading this!
I'm trying to get the day before the creation date of a file into a
cell - or to set it as its sheet name. One small addition is that I
need it as text. So if the creation date is 06/02/06, I need to cell to
be 050206 (as text), if the creation date is 01/03/06 then the cell
would be 280206. I've got a macro that formats column A as Text and
then fills it with the sheet name, but I'd like to do it all
automatically.
Thanks in advance.

Day before creation date in cell or as sheet name
 

Range("A2").Value = _
ActiveWorkbook.BuiltinDocumentProperties("Creation Date")

Range("A2").Value = _
ActiveWorkbook.BuiltinDocumentProperties("Creation Date")

"Creation Date" is two separate words.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



<AndyB wrote in message
...
Chip

Thanks for your reply. I have a problem, though! The file that
I am working on is an imported .txt and the macro crashes with
a Run-time error 5 - Invalid procedure call or argument.
The line I'm trying to use is this:
Range("A2").Value =
ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
and I'm wondering if it's because the sheet is not technically
a workbook.
The macro imports a .txt file, deletes junk from it and formats
some of the columns and I need to get the creation date of the
txt file.
Thanks.
Andy.


"Chip Pearson" wrote in message
...
Try code like

Debug.Print
Format(ThisWorkbook.BuiltinDocumentProperties("Cre ation
Date") - 1, "ddmmyy")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


<Andy wrote in message
...
Sorry - I'm in England! 01/03/2006 is the first of March
(over here!) and 28/02/06 is the day before it!

Cheers.
Andy.

"Chip Pearson" wrote in message
...
You can use BuiltInDocumentProperties to get the creation
date. E.g.,

ThisWorkbook.BuiltinDocumentProperties("Creation Date")

How do you get 280206 from 1/3/2006?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




<Andy wrote in message
...
Hi all
Thanks for reading this!
I'm trying to get the day before the creation date of a
file into a cell - or to set it as its sheet name. One
small addition is that I need it as text. So if the
creation date is 06/02/06, I need to cell to be 050206 (as
text), if the creation date is 01/03/06 then the cell would
be 280206. I've got a macro that formats column A as Text
and then fills it with the sheet name, but I'd like to do
it all automatically.
Thanks in advance.

Day before creation date in cell or as sheet name
 

Thanks Ron
Helped me heaps in a word template

"Ron de Bruin" wrote:

Try this

Sub test()
Dim str As String
str = Format(ThisWorkbook.BuiltinDocumentProperties _
("Creation Date") - 1, "dd-mm-yy")
ActiveSheet.Name = str
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


<Andy wrote in message ...
Hi all
Thanks for reading this!
I'm trying to get the day before the creation date of a file into a cell - or to set it as its sheet name. One small addition is
that I need it as text. So if the creation date is 06/02/06, I need to cell to be 050206 (as text), if the creation date is
01/03/06 then the cell would be 280206. I've got a macro that formats column A as Text and then fills it with the sheet name, but
I'd like to do it all automatically.
Thanks in advance.