+ Reply to Thread
Results 1 to 3 of 3

opening file based on most recent modification date in

Hybrid View

  1. #1
    Registered User
    Join Date
    11-14-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    96

    opening file based on most recent modification date in

    I am trying to open the most recently modified .CSV file from a folder, and it's seemingly working except for the .Open part. When i manually open it, there is no prompt stopping me so i dont understand. The error message i get says, run-time 1004, "*the exact file name im trying to open*" could not be found. Check the spelling and verify file location. ... So its finding the right file because it tells me in the error msg. I can also use an input box to specifiy the file to open by name, and that works. Im hoping someone has seen the issue before, or maybe its something that stands out to the professional (not me) eye...

        Dim FileSys As FileSystemObject
        Dim objFile As File
        Dim myFolder
        Dim strFilename As String
        Dim dteFile As Date
           
        'path for files
        Const myDir As String = "*folder path*"
       
        'filesys objects
        Set FileSys = New FileSystemObject
        Set myFolder = FileSys.GetFolder(myDir)
       
        'loop through each file and get date last modified. If largest date then store Filename
        dteFile = DateSerial(1900, 1, 1)
        For Each objFile In myFolder.Files
            If InStr(1, objFile.Name, "FILE01", vbTextCompare) Then 
                If objFile.DateLastModified > dteFile Then
                    dteFile = objFile.DateLastModified
                    strFilename = objFile.Name
                End If
            End If
        Next objFile
    
        Workbooks.Open strFilename
    
        Set FileSys = Nothing
        Set myFolder = Nothing
    Any help would be appreciated, thank you!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: opening file based on most recent modification date in

    Include the file path with the file name.

    strFilename = objFile.Path & "\" & objFile.Name
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    11-14-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: opening file based on most recent modification date in

    @Alpha : i dont care how simple it was, or how dumb i feel... im always equally amazed when presented with the answers. THANK YOU VERY MUCH!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Opening the most recent file saved to a folder
    By athyeh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2015, 08:50 PM
  2. check if file exists and opening it based on file date
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-26-2014, 03:45 AM
  3. Macro For opening Most recent file in folder
    By dtaganovic16 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2012, 12:00 PM
  4. Opening workbooks based on Date in the file's name
    By twills in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-31-2009, 09:21 AM
  5. modification date for a file
    By neta in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-05-2009, 12:31 PM
  6. UDF for opening most recent file added
    By jaden41 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2005, 07:05 AM
  7. Excel file modification date
    By GROSNER in forum Excel General
    Replies: 5
    Last Post: 03-03-2005, 10:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1