Access VBA to recognize Excel Creation Date

[tessinsd]

How can I get my Access 2007 VBA to recognize this Excel 2007 value[ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date").Value] ? based upon the time frame between date created (as seen in the Stats tab of Excel) and Last Saved, I need to import the file.

I'm new to VBA but have been using Access and Exel for many years. Any help is appreciated!

Thanks
Tessinsd

 

[Norie]

How is it not being recognised?

 

[tessinsd]

Even this code [ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date").Value] returns the Create Date found on the General tab of Excel when ran from code in Excel.

I need the Create Date found on the Statistics tab (the date the original document was created). In my Access VBA code, I'm using file scripting to read the Excel info but can't find that specific date. The create date from file scripting is the date last saved. Confusing because there is also a date of Last Saved in scripting

 

[Norie]

You'll need to use FSO For that.

 

[tessinsd]

Yes, I'm already using FSO in my Access code. It does not seem to find that specific date. I'm only looking for the date the original document was created

 

[Norie]

Can you post the code?

I assume it uses the Datecreated property of the file of interest.

 

[tessinsd]

Here's the code I'm using:

Private Function AllFiles(ByVal FullPath As String) _
As String()
'***************************************************
'PURPOSE: Returns all files in a folder using
'the FileSystemObject
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
'RETURN VALUE: An array containing a list of
'all file names in FullPath, or a 1-element
'array with an empty string if FullPath
'does not exist or it has no files
'************************************************
<o> </o>
Dim oFs ' object FileSystem Scripting
Dim sAns() As String ' array
Dim oFolder 'As folder
Dim oFile 'As file
Dim lElement 'As Long
Dim dt_Create As Date
Dim dt_Modify As Date
Dim rstHistRec As DAO.Recordset
<o> </o>
<o> </o>
On Error GoTo oops

'using the next 3 lines because FSO doesn't return the correct Create Date
Set rstHistRec = CurrentDb.OpenRecordset("qry_SM_LastHistoryUpdate", dbOpenDynaset)
dt_Create = rstHistRec(0)
rstHistRec.Close
<o> </o>
Set oFs = CreateObject("Scripting.FileSystemObject")
<o> </o>
<o> </o>
ReDim sAns(0) As String
If Not oFs.FolderExists(FullPath) = "" Then
Set oFolder = oFs.GetFolder(FullPath)
<o> </o>

For Each oFile In oFolder.Files
lElement = IIf(sAns(0) = "", 0, lElement + 1)
ReDim Preserve sAns(lElement) As String
<o> </o>
sAns(lElement) = oFile.Name


dt_Modify = Format(oFile.dateLastModified, "MM/DD/YY HH:MM") ‘ works
‘dt_Create = format(oFile.dateCreated, “MM/DD/YY HH:MM”) ‘doesn’t return correct value so having to use value in rstHistRec(0)
<o> </o>
If (dt_Create + 0.02) < dt_Modify Then '>30 minutes after creation
Debug.Print sAns(lElement) ‘ make note of those who meet criteria
Call ImportFiles(sAns(lElement), FullPath) 'import this file
End If
<o> </o>
Next
End If

 

[CharlesChuckieCharles]

Educate me whats the Stats tab ( what version of excel are you using ) where can i find it?

some help
This, in Excel, prints all available Items

Sub Metest()
On Error Resume Next
Dim item As Variant
For Each item In ActiveWorkbook.BuiltinDocumentProperties
 
 Debug.Print " Name : " & item.Name
 
 If IsNull(item.Value) Then
 Debug.Print "  Value IS NULL"
 Else
 Debug.Print " Value : " & item.Value
 End If
Next item

End Sub

 

[Norie]

Are you sure the date returned is wrong? Did you check?

What date do you expect and what date do you get?

 

[tessinsd]

Yes, I'm sure it is returning the incorrect date. I have checked it several times. If I open a file, make a change and save, the date changes to the date of the last save. This is reflected in the Excel document properties. The date that is returned is the date that appears on the General Tab when I look a the Excel properties. I need the date that the file was 1st created, which is the date that appears on the Statictics tab of the Excel document properties.