Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

LastSavedDate

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steven - 21 Jan 2006 20:36 GMT
I have a list of file names with their full name (ie Path + Name) in column
A.  50 files total.  Is there a macro or funtion that I can return the last
saved date for each file in column B and the time it was saved in column C?

Thank you for your help.

Steven
Tom Ogilvy - 21 Jan 2006 20:47 GMT
demo's from the immediate window:

dt = Filedatetime("C:\data\KZ081-Default Survey1.xls")
? cdate(clng(dt))
3/18/02
? dt-clng(dt)
6:08:40 AM

Signature

Regards,
Tom Ogilvy

> I have a list of file names with their full name (ie Path + Name) in column
> A.  50 files total.  Is there a macro or funtion that I can return the last
[quoted text clipped - 3 lines]
>
> Steven
Tom Ogilvy - 21 Jan 2006 22:11 GMT
? dt-clng(dt)
6:08:40 AM

should be

? dt-int(dt)
6:08:40 AM

Signature

Regards,
Tom Ogilvy

> demo's from the immediate window:
>
[quoted text clipped - 14 lines]
> >
> > Steven
Dave Peterson - 21 Jan 2006 20:49 GMT
One way:

Option Explicit
Sub testme()

   Dim iRow As Long
   Dim FirstRow As Long
   Dim LastRow As Long
   Dim wks As Worksheet
   Dim testStr As String
   Dim myFileName As String
   Dim myDate As Date
   
   Set wks = Worksheets("sheet1")
   
   With wks
       FirstRow = 2 'headers in row 1???
       LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
       
       For iRow = FirstRow To LastRow
           myFileName = .Cells(iRow, "A").Value
           testStr = ""
           On Error Resume Next
           testStr = Dir(myFileName)
           On Error GoTo 0
           
           If testStr = "" Then
               .Cells(iRow, "B").Value = "Not Found"
               .Cells(iRow, "C").ClearContents
           Else
               myDate = FileDateTime(myFileName)
               With .Cells(iRow, "B")
                   .NumberFormat = "mm/dd/yyyy"
                   .Value = Int(myDate)
               End With
               
               With .Cells(iRow, "C")
                   .NumberFormat = "hh:mm:ss"
                   .Value = myDate - Int(myDate)
               End With
               
           End If
       Next iRow
   End With            
           
End Sub

Although, I think I'd keep the time and date in one cell.  And just format it
pretty.

> I have a list of file names with their full name (ie Path + Name) in column
> A.  50 files total.  Is there a macro or funtion that I can return the last
[quoted text clipped - 3 lines]
>
> Steven

Signature

Dave Peterson

Steven - 21 Jan 2006 21:26 GMT
Dave:

Thank you for your help.  That is a really nice macro.   I was looking
through the Macro VB Help for how to get  LastSavedBy but could not find
anything.  Is there a way to get who saved the file last?

Thanks,

Steven

> One way:
>
[quoted text clipped - 53 lines]
> >
> > Steven
Dave Peterson - 21 Jan 2006 22:28 GMT
Are these Excel files (or MSOffice files)?

If yes, you can use the technique at Chip Pearson's site:
http://cpearson.com/excel/docprop.htm

Look for "Document Properties Of Closed Files"

and be careful.  There's instructions for both version 1.2 and 2.0 of that DSO
dll.

> Dave:
>
[quoted text clipped - 67 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.