7

I am using below code to get the created date of a workbook.

Dim mFile As String

mFile = "C:\User\User.Name\Test\Test.xlsx"
Debug.Print CreateObject("Scripting.FileSystemObject").GetFile(mFile).DateCreated

However to my surprise, this returns the date when the file is created in the directory.
If you copy the file to another folder, above will return that time and date it was copied (created).

To actually get the original created date, I tried using BuiltinDocumentProperties method.
Something like below:

Dim wb As Workbook
Set wb = Workbooks.Open(mfile) '/* same string as above */

Debug.Print wb.BuiltinDocumentProperties("Creation Date")

Above does return the original date the file was actually created.

Now, I have hundreds of file sitting in a directory that I need to get the original creation date.
I can certainly use above and look over the files, but opening and closing all of it from a shared drive takes some time. So I was wondering, if I can get the BuiltinDocumentProperties without opening the file(s) like using the first code above which is a lot faster and easier to manage.

If you somebody can point me to a possible solution, that would be great.

L42
  • 19,427
  • 11
  • 44
  • 68
  • I'm not sure what you are asking but are you looking at creating a log file? This might be helpful.. https://stackoverflow.com/questions/13853376/how-do-i-log-vba-debug-print-output-to-a-text-file – Rob B. Jan 25 '18 at 05:37
  • Can you simply write out from command line? cd to dir of interest and DIR /s /a:-D /OD /T:C > fileinfo.txt More info here: https://www.windows-commandline.com/get-file-creation-date-time/ – QHarr Jan 25 '18 at 07:29
  • @QHarr I think that will output the *create date* when the file was spawned on the directory, not the original created date of the file (first time it was saved). – L42 Jan 25 '18 at 09:10
  • Would the first saved time be after the creation date and actually be the first modified date? And I'm guess version control is not in place? – QHarr Jan 25 '18 at 09:42

1 Answers1

3

Try something like this. The key is the special DSO object.

Imports Scripting
Private Sub ReadProperties()
    Dim pathName As String = "C:\yourpathnamehere"
    Dim Fso As FileSystemObject = New Scripting.FileSystemObject
    Dim fldr As Folder = Fso.GetFolder(pathName)
    Dim objFile As Object = CreateObject("DSOFile.OleDocumentProperties")

    Dim ResValue As String = Nothing
    For Each f In fldr.Files
        Try
            objFile.Open(f)
            ResValue = objFile.SummaryProperties.DateCreated
            ' Do stuff here
            objFile.Close
        Catch ex As Exception
            'TextBox1.Text = ex.Message
        End Try

        Application.DoEvents()
    Next
End Sub
ForEachLoop
  • 2,508
  • 3
  • 18
  • 28
  • Thanks for your answer, I cannot implement `.Net` and I don't think `DSO` is part of the default `VBA` library. But I will look at it and see what I can get. While your at it, by executing `Open` method above, are you actually opening the file? Because that is what I want to avoid. – L42 Jan 27 '18 at 09:04
  • Good question about the file being opened. I wrote this for the exact same reason that I didn't want to open each file either. The conventional approach would have been using VBA and that did open each file and is time consuming. The DSO approach is very fast. I hope someone who knows more about this confirms this, but I don't think it opens the file itself. It looks to open a resource file instead. The DSO object just contains the properties, not access to to the contents. I've used this code for a project and works like I wanted it to. – ForEachLoop Jan 27 '18 at 15:43