Get the Date Created in the Property Summary of a Excel File

This should work for any file, not just Excel ones
 

Sub GetDateCreated()
    Dim oFS As Object
    Dim strFilename As String

    'Put your filename here
    strFilename = "c:\temp\excelfile.xls"

    Set oFS = CreateObject("Scripting.FileSystemObject")
    MsgBox strFilename & " was created on " & oFS.GetFile(strFilename).DateCreated
    Set oFS = Nothing
End Sub

Select allOpen in new window

The code only gives me the date created under "GENERAL" Tab, not the one under Summary Tab. In fact the file was created by scheduler at 6:02 and sent to me by email. I save the file to my hard disk at 09:17. Your code give me 09:17 not 6:02.

Hi Howard,
Sorry, didn't realise exactly which one you meant.... this code works in vbscript, but I don't have Excel in front of me to test with. Let me know how you go, as it may need a slight bit of tweaking to get it into VBA
 

Sub GetDateCreated()
	'Define file name and path to file
	strPath = "C:\temp"
	strFilename = "excelfile.xls"

	Set objShell = CreateObject("Shell.Application")
	Set objFolder = objShell.Namespace(strPath)
	Set objFolderItem = objFolder.ParseName(strFileName)

	strMyDate = objFolder.GetDetailsOf(objFolderItem, 31)

	MsgBox strFilename & " was created on " & strMyDate
End Sub

Select allOpen in new window

Sorry error:

Object variable or With Block Variable not set

I've just cut and paste this into a new Excel macro and it works fine
Obviously you need to customise lines 2 and 3 to reflect the file you want to check.

When you get the error, hit debug - which line does it have a problem with?

I run the code in Access not Excel

The problem is from the following line
Set objFolderItem = objFolder.ParseName(strFileName


The codes are:

Private Sub Command0_Click()
       
    strPath = "L:\Capex Small Tools\Source\Yardi Daily Source\Job"
    strFileName = "Job_Details_Report_.all_20110426.xls"


    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(strPath)
    Set objFolderItem = objFolder.ParseName(strFileName)

    strMyDate = objFolder.GetDetailsOf(objFolderItem, 31)

    MsgBox strFileName & " was created on " & strMyDate

End Sub

Sorry, didn't realise parseName wasn't usable in VBA, try this instead and let me know how you get on
 

Sub GetDateCreated()
	'Define file name and path to file
	strPath = "L:\Capex Small Tools\Source\Yardi Daily Source\Job"
	strFilename = "Job_Details_Report_.all_20110426.xls"

	Set objShell = CreateObject("Shell.Application")
	Set objFolder = objShell.Namespace(strPath)

	For Each arrFileName in objFolder.Items
		If objFolder.GetDetailsOf(arrFileName, 0) = strFilename Then
			strMyDate = objFolder.GetDetailsOf(arrFileName, 31)
		End If
	Next

	MsgBox strFilename & " was created on " & strMyDate
End Sub

Select allOpen in new window

Error "Object variable or With Block Variable not set" at the following line

For Each arrFileName In objFolder.Items

My Code:
    strPath = "L:\Capex Small Tools\Source\Yardi Daily Source\Job"
    strFileName = "Job_Details_Report_.all_20110428.xls"

    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(strPath)

    For Each arrFileName In objFolder.Items
        If objFolder.GetDetailsOf(arrFileName, 0) = strFileName Then
            strMyDate = objFolder.GetDetailsOf(arrFileName, 31)
        End If
    Next

    MsgBox strFileName & " was created on " & strMyDate

Hi,

This error would generally occur if the file did not exist, the path was incorrect, or the account you are running the code as does not have permissions to that location.
Please could you check these criteria

The file is correct and the file exist.

The path is correct and the file exists.

In fact when i test the content of the following line in debug mode, the error "Object variable or With Block Variable not set" occur

? objShell.Namespace(strPath)

Is this running as a macro in Access? or how are you running it?
Are you able to post the file for me to check (or is it confidential).
Only reason I ask is that I cannot replicate your error unless I type in the path name incorrectly. I will have another look in the morning though

I put your code in the click event of a button in a form in Access. I click the button and I get the error message.