Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Green Bay, Wisconsin, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Workbook Created (Excel 2003 SP3)

    I want to automatically insert the date a workbook was first created (from a template) in a worksheet. I don't want the date to change when the workbook is subsequently reopened. If I could insert the date "Created" that you see when clicking File - Properties - General (tab), that would work fine. How do I do it?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Workbook Created (Excel 2003 SP3)

    You could create the following custom function in a module in the Visual Basic Editor:

    Function CreateDate() As Date
    CreateDate = ActiveWorkbook.BuiltinDocumentProperties("Creation date").Value
    End Function

    Enter the following formula in a worksheet cell:

    =CreateDate()

    and format the cell as a date (or date+time). If you store the function in your Personal.xls workbook, use

    =Personal.xls!CreateDate()

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Green Bay, Wisconsin, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Workbook Created (Excel 2003 SP3)

    Thanks for your input, Hans.

    The CreateDate() function that you suggested works, but unfortunately, the date inserted is not the "date created" that is shown on the File - Properties menu. When I create a new file from a template, the File - Properties menu shows the date the new file was created, but the CreateDate() function shows the date when the original template was created. Is there any way to access the File - Properties date?

    Or is there another way to automatically insert a date when a new file is created (from a template), but prevent it from being changed when the file is reopened on a subsequent date? I can do it with a workbook_open() sub by setting a flag to test if the date has already been set, but for that to work, it is necessary to remember to always reset the flag when I open the original template to modify it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Workbook Created (Excel 2003 SP3)

    As far as I can tell, the creation date/time of the workbook itself isn't exposed in Excel VBA. Try this version instead:

    Function CreateDate()
    CreateDate = CreateObject("Scripting.FileSystemObject").GetFile (ActiveWorkbook.FullName).DateCreated
    End Function

    This will only work with workbooks that have been saved to disk, because this code looks at the saved .xls file.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •