[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

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

How to get the Date Created in the Property Summary of a Excel File in VBA?
0
howardching
Asked:
howardching
  • 8
  • 7
1 Solution
 
ScottyworldCommented:
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

Open in new window

0
 
howardchingAuthor Commented:
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.
0
 
ScottyworldCommented:
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

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
howardchingAuthor Commented:
Sorry error:

Object variable or With Block Variable not set
0
 
ScottyworldCommented:
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?
0
 
howardchingAuthor Commented:
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
0
 
ScottyworldCommented:
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

Open in new window

0
 
howardchingAuthor Commented:

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
0
 
ScottyworldCommented:
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
0
 
howardchingAuthor Commented:
The file is correct and the file exist.
0
 
howardchingAuthor Commented:
The path is correct and the file exists.
0
 
howardchingAuthor Commented:
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)
0
 
ScottyworldCommented:
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

0
 
howardchingAuthor Commented:
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.
0
 
ScottyworldCommented:
It still all works for me as long as the file and path exist. What version of Access are you using?
In Access 2003, I created a new form, created a new button, right-click on button and 'Build Event'
Pasted in my coded that I've posted above and all works fine.

Can you try using a local drive and file e.g. c:\temp to see if that works?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now