Solved

Find creation date of Excel Workbook

Posted on 2014-01-01
9
1,161 Views
Last Modified: 2014-01-16
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
Comment
Question by:rrhandle8
  • 5
  • 3
9 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39750412
If it's the active workbook, try this:

ActiveWorkbook.BuiltinDocumentProperties("Creation Date")

Flyster
0
 

Author Comment

by:rrhandle8
ID: 39750421
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
 

Author Comment

by:rrhandle8
ID: 39750448
CORRECTION -- The date on the Statistics tab is today's date.  The date on the general tab is back in August.
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 39750516
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rrhandle8
ID: 39750572
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
 

Author Comment

by:rrhandle8
ID: 39750612
I correct myself again.  Even using the FSO, sometimes the original creation day is captured, other times it is not.  Very confusing.
0
 
LVL 14

Accepted Solution

by:
Farzad Akbarnejad earned 500 total points
ID: 39750730
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
 

Author Closing Comment

by:rrhandle8
ID: 39784078
There was no answer to this problem.
0
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 39784679
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now