Showing the last saved date in a cell (external file)

[Corleone]

Hi
Im currently using Excel 2010
Is there a way of doing the above?

the external file in question is in a separate directory "\\H:\Dashboards\P6Update
I would like my spreadsheet to just show the latest saved date of this file whenever it gets saved in cell C2

Thanks

 

[James006]

Hi,

Does it mean you need to always get the value of cell C2 from a closed file ...?

 

[Corleone]

No
I would like C2 in my file to show the current saved date of the external file "P6 Update" whether that external file is open or not

thanks

 

[James006]

Thanks for clarification ...

You can use the following function :

<code class=" language-vbnet" style="margin: 0px; padding: 0px 3px; border: 0px; font-family: Consolas, Monaco, 'Andale Mono', 'Ubuntu Mono', monospace; vertical-align: baseline; text-shadow: white 0px 1px; word-spacing: normal; word-break: normal; word-wrap: normal; line-height: 1.5; tab-size: 4; background: none;">[COLOR=#0077AA][FONT=inherit]Function[/FONT][/COLOR] FileLastModified[COLOR=#999999][FONT=inherit]([/FONT][/COLOR]strFullFileName [COLOR=#0077AA][FONT=inherit]As[/FONT][/COLOR] [COLOR=#0077AA][FONT=inherit]String[/FONT][/COLOR][COLOR=#999999][FONT=inherit])[/FONT][/COLOR]
    [COLOR=#0077AA][FONT=inherit]Dim[/FONT][/COLOR] fs [COLOR=#0077AA][FONT=inherit]As[/FONT][/COLOR] [COLOR=#0077AA][FONT=inherit]Object[/FONT][/COLOR][COLOR=#999999][FONT=inherit],[/FONT][/COLOR] f [COLOR=#0077AA][FONT=inherit]As[/FONT][/COLOR] [COLOR=#0077AA][FONT=inherit]Object[/FONT][/COLOR][COLOR=#999999][FONT=inherit],[/FONT][/COLOR] s [COLOR=#0077AA][FONT=inherit]As[/FONT][/COLOR] [COLOR=#0077AA][FONT=inherit]String[/FONT][/COLOR]
     
    [COLOR=#0077AA][FONT=inherit]Set[/FONT][/COLOR] fs [COLOR=#A67F59][FONT=inherit]=[/FONT][/COLOR] CreateObject[COLOR=#999999][FONT=inherit]([/FONT][/COLOR][COLOR=#669900][FONT=inherit]"Scripting.FileSystemObject"[/FONT][/COLOR][COLOR=#999999][FONT=inherit])[/FONT][/COLOR]
    [COLOR=#0077AA][FONT=inherit]Set[/FONT][/COLOR] f [COLOR=#A67F59][FONT=inherit]=[/FONT][/COLOR] fs.GetFile[COLOR=#999999][FONT=inherit]([/FONT][/COLOR]strFullFileName[COLOR=#999999][FONT=inherit])[/FONT][/COLOR]
     
    s [COLOR=#A67F59][FONT=inherit]=[/FONT][/COLOR] [COLOR=#DD4A68][FONT=inherit]UCase[/FONT][/COLOR][COLOR=#999999][FONT=inherit]([/FONT][/COLOR]strFullFileName[COLOR=#999999][FONT=inherit])[/FONT][/COLOR] [COLOR=#A67F59][FONT=inherit]&[/FONT][/COLOR] vbCrLf
    s [COLOR=#A67F59][FONT=inherit]=[/FONT][/COLOR] s [COLOR=#A67F59][FONT=inherit]&[/FONT][/COLOR] [COLOR=#669900][FONT=inherit]"Last Modified: "[/FONT][/COLOR] [COLOR=#A67F59][FONT=inherit]&[/FONT][/COLOR] f.DateLastModified
    FileLastModified [COLOR=#A67F59][FONT=inherit]=[/FONT][/COLOR] s
     
    [COLOR=#0077AA][FONT=inherit]Set[/FONT][/COLOR] fs [COLOR=#A67F59][FONT=inherit]=[/FONT][/COLOR] [COLOR=#0077AA][FONT=inherit]Nothing[/FONT][/COLOR][COLOR=#999999][FONT=inherit]:[/FONT][/COLOR] [COLOR=#0077AA][FONT=inherit]Set[/FONT][/COLOR] f [COLOR=#A67F59][FONT=inherit]=[/FONT][/COLOR] [COLOR=#0077AA][FONT=inherit]Nothing[/FONT][/COLOR]
     
[COLOR=#0077AA][FONT=inherit]End[/FONT][/COLOR] [COLOR=#0077AA][FONT=inherit]Function[/FONT][/COLOR]</code>

Hope this will help

 

[Corleone]

Thanks James
Excuse my ignorance

In the above code where would i place the C2 reference in the File im using (ie where the date would get updated) e.g sheet1 C2
And where would i reference the external file \\H:\Dashboards\P6Update

Cheers

 

[James006]

Re,

Sorry ... should have added an example ...in the code ...:wink:

Function FileLastModified(strFullFileName As String)
' example how to use Function in any cell
' =FileLastModified("C:\Documents and Settings\My Documents\Excel\Private\myfile.xlsx")
    Dim fs As Object, f As Object, s As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(strFullFileName)
    s = UCase(strFullFileName) & vbCrLf
    s = f.DateLastModified
    FileLastModified = s
    Set fs = Nothing: Set f = Nothing
End Function

Hope this clarifies