[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Find creation date of Excel Workbook

I am opening Excel workbooks use VBA in Access.  I want to get the original creation date of the workbook, but what I am getting is the date is was moved to the current folder.


Dim WkBk As Excel.Workbook
Set WkBk = ExcelApp.Workbooks.Open(filename:=WbkPathName)
WkBk.BuiltinDocumentProperties("Creation Date")

Open in new window



I tried using this, but it causes an error:

WkBk.BuiltinDocumentProperties.items("Creation Date").Value

Open in new window



Access 2003
Excel 2010
0
rrhandle8
Asked:
rrhandle8
  • 5
  • 3
1 Solution
 
FlysterCommented:
If it's the active workbook, try this:

ActiveWorkbook.BuiltinDocumentProperties("Creation Date")

Flyster
0
 
rrhandle8Author Commented:
ActiveWorkbook.BuiltinDocumentProperties("Creation Date") also returns today's date.  Today is the day I saved it to my computer.
The original creation date is back in August, and can be seen on the Statistics tab of the property box.
0
 
rrhandle8Author Commented:
CORRECTION -- The date on the Statistics tab is today's date.  The date on the general tab is back in August.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Farzad AkbarnejadCommented:
0
 
rrhandle8Author Commented:
I already tried that.  Didn't work.
I have discovered the only way to get the original creation date is to use the FileSystemObject.

Option Compare Database
Option Explicit
Function getCreationDate(strCompleteFilePath As String) As Date
    Dim objFileSystem       As Object
    Dim objFile             As Object
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    If objFileSystem.FileExists(strCompleteFilePath) Then
        Set objFile = objFileSystem.GetFile(strCompleteFilePath)
        getCreationDate = objFile.DateCreated
    Else
        MsgBox "The specified file '" & strCompleteFilePath & "' does not exist.",    vbInformation, "Creation Date"
    End If
    Set objFile = Nothing
 End Function
 Function getOpnWbkCreationDate(wbkOpened As Workbook) As Date
     getOpnWbkCreationDate = wbkOpened.BuiltinDocumentProperties("Creation Date")
 End Function
 Sub ExecuteFunc()
     MsgBox getCreationDate(ThisWorkbook.FullName)
    MsgBox getOpnWbkCreationDate(ThisWorkbook)
End Sub

Open in new window

0
 
rrhandle8Author Commented:
I correct myself again.  Even using the FSO, sometimes the original creation day is captured, other times it is not.  Very confusing.
0
 
Farzad AkbarnejadCommented:
Hi,
I tested a bit a few hours ago. If you create file using Mouse Right Click then using new excel file command of opened context menu you will have a wrong creation date field!.

-FA
0
 
rrhandle8Author Commented:
There was no answer to this problem.
0
 
Farzad AkbarnejadCommented:
rrhandle8,
If you don't get your proper solution you can request for attention to this question and get points refund.

-FA
0

Featured Post

Industry Leaders: 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!

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