Excel ActiveWorkbook.BuiltinDocumentProperties("Creation Date") Error

Hello,

I have a report that I run manually which is also scheduled to run overnight. The overnight version never has the file creation date populated. I'm trying to build code to check if the creation date exists but keep getting run time error '-2147467259 (80004005)':
The method 'Value' for the object DocumentProperty failed.

Please help.

Private Sub TestCDate()

If IsNull(ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value) Then
MsgBox "No Date" 'I have code to prompt the user to enter the value if null
Else: MsgBox ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value ' This will be changed to run some other code

 End If

End Sub

Open in new window


Please help.

Thank you.
sq30Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Private Sub TestCDate()

On Error Resume Next
CrDate = ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value
On Error GoTo 0

If IsEmpty(CrDate) Then
MsgBox "No Date" 'I have code to prompt the user to enter the value if null
Else: MsgBox ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value ' This will be changed to run some other code

 End If

Open in new window

Regards
sq30Author Commented:
Rgonzo1971, the same error is still being returned.
[ fanpages ]IT Services ConsultantCommented:
Hi,

I presume that you have checked that ActiveWorkbook is the correct workbook at the time of failure.

The "Creation Date" property should be available even on workbooks that have not been saved.

How is the manual process different to the automated (presumably) overnight process?

How are you automating the execution of the code?

If the workbook has been saved at the point at which the code above is executed, the following statement will (also) return the Creation Date:

MsgBox CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).DateCreated

ThisWorkbook.FullName must be a valid (existing) file (previously saved).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Rgonzo1971Commented:
I've edited it to IsEmpty instead of IsNull

pls try
[ fanpages ]IT Services ConsultantCommented:
PS. The Creation (or Last Modified) Date of a file can also be gained using:

MsgBox FileDateTime(ThisWorkbook.FullName)
sq30Author Commented:
fanpages,

Yes it is the correct workbook at time of failure and it's an overnight process.

I've changed the code slightly which works perfectly for me as the active workbook (the workbook I need the creation time from) is closed after some data has been extracted from it into my main file.

MsgBox CreateObject("Scripting.FileSystemObject").GetFile(ActiveWorkbook.FullName).DateCreated

Open in new window


Thank you
Sq30
[ fanpages ]IT Services ConsultantCommented:
You're welcome.

I am still unclear why the existing code statement(s) failed, or how the manual process is executed differently to the overnight process (unless you mean it is just started before you leave for the evening), but if you are happy with the alternate suggestion, then that's fine with me! :)
sq30Author Commented:
The report is scheduled overnight and saved. On the server in explorer you can see the creation/last save time of the file but within the .xls file under the file properties the last modified or created say "never". This behaviour only happens on the overnight run?  When the report is run manual it's opened locally in excel which is probably when the .xls fields are populated? Thanks again :)
[ fanpages ]IT Services ConsultantCommented:
Thanks again... but I am still unclear how the scheduling occurs.

If the process by which the MS-Excel workbook is opened (the code is executed, & the workbook is saved/closed) differs between the 'overnight' execution & the manual execution, then this is most likely to be the cause of the issue.

Which "scheduler" are you using?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.