Solved: Capture and Display Excel Creation Date property

[DomFino]

I am having a hard time figuring this out. I hope someone can help.
I have an Access 2003 database that is used to simply run reports. The
data for the reports comes from a linked Excel 2003 spreadsheet.

I have a Report Form in Access that allows users to select the report they
wish to run. What I need is to have the date and time the Excel
spreadsheet was created show on the Access form so the user knows the
last time the data was refreshed.

This is code I have so far and it does not work. The Creation Date
property in Excel has the date and time I need to display on the Access
form but darn if I can figure out how to capture it and display it

This code is behind the Access form.

[vba]Option Compare Database
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open("V:\CPSI-AccessDB\WebPAS\webpas_download.xls")

Wscript.Echo
Debug.Print objWorkbook.BuiltinProperties(11).Value

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub
[/vba]
</IMG>

[lucas]

Couple of links with leads to accessing excel documnent properties:
http://vbaexpress.com/kb/getarticle.php?kb_id=547
http://vbaexpress.com/kb/getarticle.php?kb_id=750

this one shows how to add a custom document property so you can locate it later:
http://vbaexpress.com/kb/getarticle.php?kb_id=694

[lucas]

Sorry DomFino, I don't know much about access. You should probably post this in the access forum.

[DomFino]

Thanks for the information Lucas. I read through the links you sent but they are way over my head. I will repost in the Access forum.
Thanks again for your time.
Dom

[lucas]

Moved at DomFino's request

[stanl]

You could try either WMI or DSO. Are you looking for the creation date or the lastupdate because if the same file is linked, the former should be constant.
.02
Stan

[DomFino]

Hi Stan,
I have managed to get the following code to work. However, I think I need
something to release the variable because after I run it by pressing the
command button on my form I get an error Unable to set the open
property of the workbooks class. If I go to the spreadsheet and try to
open it, it states it is read-only, etc. Do you or anyone else know the
additional code required to clean up so that excel is released and the variable is
released?

[vba]Option Compare Database
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim szCreationDate As String
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open("V:\CPSI-AccessDB\WebPAS\webpas_download.xls")
szCreationDate = objWorkbook.BuiltinDocumentProperties("Creation Date")

Me.txtCreationDate = szCreationDate
Exit_Command10_Click:
Exit Sub
Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub
[/vba]

[DomFino]

All,
The errors are gone. It was something in the excel spreadsheet. Therefore, the code listed above is the working code and this post is solved. Thanks for everyones help.