Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

What is the difference between the general create date and the statistics create date?

I use a template to create an Excel workbook that has been in use a long time.
I want to use brettd code to insert the creation date, however the files seem to have two creation dates.
1. Under the General tab of the workbook there is the date that the file was created from the template.
2. Under Statistics tab of the workbook there is the date that the template was created

ActiveWorkbook.BuiltinDocumentProperties("Creation Date") .....seems to return the "Statistics" create date
I would like to return "General"  create date

Is that possible??
0
spar-kle
Asked:
spar-kle
  • 5
  • 4
1 Solution
 
ragnarok89Commented:
From within any module in the Visual Basic Editor, choose from the main menu Tools, References and make sure that "Microsoft Scripting Runtime" is checked. Then create this function:

Function getFileDate(strFileName As String) As Date
   Dim fso As Scripting.FileSystemObject
   Dim file As Scripting.file
   Set fso = New FileSystemObject
   Set file = fso.GetFile(strFileName)
   getFileDate = file.DateCreated
End Function

Then, within your code, you can use for instance:

getFileDate("C:\test.txt")

to get the Date Created for that file.

Al
0
 
spar-kleAuthor Commented:
Thanks for your answer Al
However, I'm not quite sure how I can use this in conjunction with brettdj code
...please advise
Thanks
Des
0
 
Dave BrettCommented:
Des,

That is interesting

In Excel 2010 the creation date of a file from a template is the original date, and The CreationDate does return this on my test

Are you running Excel 2003?

The code below returns the template creation date (2006) with ActiveWorkbook.BuiltinDocumentProperties("Creation Date"), then the actual file creation date (today)using the filesystemobject on my testing of a new file created from a template

Cheers

Dave
MsgBox ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
    Set objfso = CreateObject("scripting.filesystemobject")
    Set objfil = objfso.GetFile(ActiveWorkbook.FullName)
    MsgBox objfil.DateCreated

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dave BrettCommented:
> However, I'm not quite sure how I can use this in conjunction with brettdj code
I don't think it gives you what you need - it will give you the file creation date not the template date
0
 
spar-kleAuthor Commented:
Thanks Dave
This file is in Excel 2007 format.
The MsgBox code returns template creation date, then actual date
Thanks
Des
0
 
spar-kleAuthor Commented:
sorry I should have said thta it is in 2003 format.
I was looking at it in 2007 at home.
It was in 2003 format at work..
0
 
Dave BrettCommented:
Doesn't this then do what you want already?

It you wanted actual date rather than template creation then pls try this

Cheers

Dave


Sub Change()
'uses http://www.experts-exchange.com/A_2839.html
    Dim objFso
    Dim objFil
    Dim Wb As Workbook
    Dim strFName As String
    Dim strFolderName As String
    strFolderName = "c:\temp"
    Set objFso = CreateObject("scripting.filesystemobject")
    

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    strFName = Dir(strFolderName & "\*.xls*")
    Do While Len(strFName) > 0
        Set Wb = Workbooks.Open(strFolderName & "\" & strFName)
        Set objFil = objFso.GetFile(ActiveWorkbook.FullName)
        With Wb.Sheets(1).[a1]
            .NumberFormat = "dd-mmm-yyyy"
            .Value = objFil.DateCreated
        End With
        Wb.Save
        Wb.Close
        strFName = Dir
    Loop

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .StatusBar = vbNullString
    End With

End Sub

Open in new window

0
 
spar-kleAuthor Commented:
Can you please advise how I could use the filesystemobject in the original code please?
0
 
spar-kleAuthor Commented:
Thanks for your help,
That's perfect
Des
0
 
Dave BrettCommented:
See post above that overalpped yours :)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now