Day before creation date in cell or as sheet name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Try this

Sub test()
Dim str As String
str = Format(ThisWorkbook.BuiltinDocumentProperties _
("Creation Date") - 1, "dd-mm-yy")
ActiveSheet.Name = str
End Sub
 
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.
 
Try code like

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
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("CreationDate")
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.
 
Range("A2").Value = _
ActiveWorkbook.BuiltinDocumentProperties("CreationDate")

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
 
Thanks Ron
Helped me heaps in a word template

Ron de Bruin said:
Try this

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