Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I successfully import data from a .csv file into Excel using VBA.

    Is it also possible to use VBA to get the date/time the .csv file was last saved ?

    Then I will be able to show the date/time next to the data itself.

    Thanks,

    Martin

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The Function below accepts 2 arguments
    The First is the File Path, the second is the File Name
    See comments in it for return values.
    If there are NO errors it will return the last saved date.

    To use it you must go to Tools References in the VBE
    and set a reference to the
    Microsoft Scripting Runtime

    Code:
    Function GetLastSaved(strPath As String, strFile As String) As Date
    
    Dim ofs As FileSystemObject
    Dim off As File
    Dim dteSaveDate As Date
    
    'This returns a last saved date and time unless an error or invalid input
    'In this case it returns 01/01/1900 for an invalid file and 01/02/1900 for an error (US Date)
    'If you used is with Excel you can only use dates after 01/01/1900
    'If you only want the date then use the INT function to lose the time component
    
    On Error GoTo NoFileDateError
    
    Set ofs = New FileSystemObject
    
    If strPath = "" Then
        GetLastSaved = #1/1/1900# '1900 is an Invalid data Error date
        Exit Function
    End If
    
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    If Not ofs.FolderExists(strPath) Then
        GetLastSaved = #1/1/1900#
        Exit Function
    End If
    
    If Not ofs.FileExists(strPath & strFile) Then
        GetLastSaved = #1/1/1900#
        Exit Function
    End If
    
    'Get File
    Set off = ofs.GetFile(strPath & strFile)
    dteSaveDate = off.DateLastModified
    'dteSaveDate = Int(dteSaveDate) 'Remove Comment at start here to get just a date
    
    Set off = Nothing
    Set ofs = Nothing
    
    GetLastSaved = dteSaveDate
    
    Exit Function
    
    NoFileDateError:
    GetLastSaved = #1/2/1900#   'This is an Error Date
    
    Set off = Nothing
    Set ofs = Nothing
    
    Exit Function
    
    End Function
    You Could Call it From a Sheet or vba code

    e.g.

    dteLast=GetLastSaved("S:\MyDate\Whatever","MyFile. docx") VBA

    If you wanted to return nothing if the returned date was invalid you could use an IF to test the return values for not being 1/1/1900 or 1/2/1900

    Or just adapt it to your needs.
    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Adapted, tested, in use !

    Thank you very much.

    Martin

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I had to modify my comments, I had written it as an Access routine.
    However, it is also fine in Excel.
    But if you use it from an Excel Sheet, you would need to make sure the dates were the post 1900 ones since Excel cannot cope with 1800, although
    it can if in VBA.
    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I should have said that I had trouble with the Dim statement so I used the explicit Set ofs = CreateObject("Scripting.FileSystemObject")

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    CreateObject is just as good.

    You just don't get the properties help when you type the . in the IDE

    Did you set the Reference to the Scripting Runtime in Tools References when in the VB Editor?

    You can also type the following with Dim

    Dim ofs AS Scripting.FileSystemObject

    But you still need the Library Reference
    Andrew

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I had missed the scripting reference :-(

    Have now added it and, of course, both methods now work. (I'm amazed I found a workaround though !)

    Thanks again - as ever one often just needs a pointer in the right direction to get started.

    Martin

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    VBA also has a FileDateTime function.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    DOH! That is a lot easier!
    Andrew

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Now that's simpler . . . but I learned a lot doing it the hard way !

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    With the HARD WAY knowledge you can also find out a lot of other file based information, and manipulate folders and files.

    Not sure where FileDateTime slipped through my net though!
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •