Date and Time not showing

[Fathermole]

have this code but when it runs date and time do not Show

Sub list_save_times()

Dim myPath As String
Dim MyFile As String
Dim FldrPPicker As FileDialog
Dim sh As Worksheet
Dim i As Integer

Application.ScreenUpdating = False

Set sh = ThisWorkbook.Sheets("Last Save Times")
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Please Select Folder"
        .AllowMultiSelect = False
        .ButtonName = "Confirm!!!"
        If .Show = -1 Then
            myPath = .SelectedItems(1) & "\"
        Else
            End
        End If
    End With

sh.Activate
sh.Cells.ClearContents
Cells(1, 1) = "File Name(s)"
Cells(1, 2) = "Last Time Saved"
Cells(1, 4) = "Location:"
Cells(1, 5) = myPath
    
    
MyFile = Dir(myPath)
i = 1

Do While MyFile <> ""

    sh.Cells(i + 1, 1) = MyFile
    Workbooks.Open Filename:=myPath & MyFile
    With sh.Cells(i + 1, 2)
    .Value = ActiveWorkbook.BuiltinDocumentProperties(4)
    .NumberFormat = "mm-dd-yyyy h:mm AM/PM"

    
        End With
        ActiveWorkbook.Close savechanges:=False
        
    MyFile = Dir
    i = i + 1
Loop
sh.Range("A:B").Columns.AutoFit

If i = 1 Then
    
        
        MsgBox "There are no items in this folder"
         End If
        Application.ScreenUpdating = True
   
        
End Sub

 

[rlv01]

.Value = ActiveWorkbook.BuiltinDocumentProperties(4)

Because the above is returning an empty value. The Item(4) property is not a date. To display a date, you have to choose a property that is a date.

Workbook.BuiltinDocumentProperties property (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Fathermole]

It Gets the Files just not the save times

 

[Alex Blakenburg]

@faterhmore, @rlv01 if referring to your comment

date and time do not Show

which in your code is represented by

    With sh.Cells(i + 1, 2)
    .Value = ActiveWorkbook.BuiltinDocumentProperties(4)

and trying to point out that 4 does not represent the item number for date last saved.

You need to be using either of these 2,

ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
ActiveWorkbook.BuiltinDocumentProperties(12)

You can find the complete list on the BetterSolutions site here:

Excel Workbooks - Workbook Properties

Excel Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

www.bettersolutions.com

I couldn't find any enumeration for Excel as there is for word. The word item number for most of them are the same and can be found here.

WdBuiltInProperty enumeration (Word)

Office VBA reference topic

learn.microsoft.com

 

[rlv01]

MsoDocProperties is the enumeration type.

Name	Value	Description
msoPropertyTypeBoolean	2	Boolean value.
msoPropertyTypeDate	3	Date value.
msoPropertyTypeFloat	5	Floating point value.
msoPropertyTypeNumber	1	Integer value.
msoPropertyTypeString	4	String value.

MsoDocProperties enumeration (Office)

Office VBA reference topic

learn.microsoft.com

 

[Alex Blakenburg]

MsoDocProperties is the enumeration type.

Name	Value	Description
msoPropertyTypeBoolean	2	Boolean value.
msoPropertyTypeDate	3	Date value.
msoPropertyTypeFloat	5	Floating point value.
msoPropertyTypeNumber	1	Integer value.
msoPropertyTypeString	4	String value.

MsoDocProperties enumeration (Office)

Office VBA reference topic

learn.microsoft.com

That is very short list and doesn't include one for Last Save Time.

 

[rlv01]

That is very short list and doesn't include one for Last Save Time.

Because it is a list of the property type enumerations, not the property names. A list of the property names would be longer.

Item	Property Names	Type	Type Enumeration
1	Title	4	msoPropertyTypeString
2	Subject	4	msoPropertyTypeString
3	Author	4	msoPropertyTypeString
4	Keywords	4	msoPropertyTypeString
5	Comments	4	msoPropertyTypeString
6	Template	4	msoPropertyTypeString
7	Last author	4	msoPropertyTypeString
8	Revision number	4	msoPropertyTypeString
9	Application name	4	msoPropertyTypeString
10	Last print date	3	msoPropertyTypeDate
11	Creation date	3	msoPropertyTypeDate
12	Last save time	3	msoPropertyTypeDate
13	Total editing time	1	msoPropertyTypeNumber
14	Number of pages	1	msoPropertyTypeNumber
15	Number of words	1	msoPropertyTypeNumber
16	Number of characters	1	msoPropertyTypeNumber
17	Security	1	msoPropertyTypeNumber
18	Category	4	msoPropertyTypeString
19	Format	4	msoPropertyTypeString
20	Manager	4	msoPropertyTypeString
21	Company	4	msoPropertyTypeString
22	Number of bytes	1	msoPropertyTypeNumber
23	Number of lines	1	msoPropertyTypeNumber
24	Number of paragraphs	1	msoPropertyTypeNumber
25	Number of slides	1	msoPropertyTypeNumber
26	Number of notes	1	msoPropertyTypeNumber
27	Number of hidden Slides	1	msoPropertyTypeNumber
28	Number of multimedia clips	1	msoPropertyTypeNumber
29	Hyperlink base	4	msoPropertyTypeString
30	Number of characters (with spaces)	1	msoPropertyTypeNumber
31	Content type	4	msoPropertyTypeString
32	Content status	4	msoPropertyTypeString
33	Language	4	msoPropertyTypeString
34	Document version	4	msoPropertyTypeString

 

[Fathermole]

I get run-time error 5
Invalid procedure call or argument

 

[Skyybot]

This worked for me. Won't work on a Workbook that hasn't been saved yet (obviously)

d = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")

 

[Alex Blakenburg]

@Fathermole The grey code boxes have copy symbol in the top right corner, it’s preferable to use that.
You have a typo Times should be Time singular