Opening the Newest File in a Folder with VBA

Posted on by Dick Kusleika

From Quickbooks, I can export any report directly into Excel. Quickbooks tries to maintain perfect fidelity between the report generated and what you get in Excel. An admirable goal, maybe, but here’s the result:

In this small piece of a physical inventory worksheet, you can see that columns A:D are all small. This so they can indent. Even though you can only see one level of indent in this shot, all four of those columns are used. Also G, I, and K are small, which may look nice, but it’s a pain. Generally, I’m not exporting to Excel to print – I can do that directly from Quickbooks. I’m exporting to manipulate; sort, pivot table, autofilter, and the like. These blank columns do not help in that regard. I find myself deleting them, which can be time consuming for wider reports.

I can also export from Quickbooks as a CSV. The resulting file has no blank columns and no formatting. Perfect. It’s a bit more of a process though. When I export to Excel, it opens the workbook in Excel with no interaction from me. For CSV files, I have to choose a place to save it, then navigate to the place from Excel to open it, and change the Files As Type to ‘Text Files’. But I do the same thing over and over. I export the file then immediately go and open it in Excel. I almost never want the CSV to remain on the disk or even to ever exist on the disk. I’d prefer if it opened in Excel like the other export does.

To try to eliminate steps, I created a macro to open the newest CSV file in a particular folder. My system now is to save every CSV to the same folder under My Documents. Since Quickbooks starts in My Documents, it will be me less navigation than saving it in an appropriate place on the server. The macro will check the creation dates of all the CSV files in that folder and pick the newest.

I’m using the File Scripting Object, so I start by setting a reference.

There should be an easier way to find the newest file in a directory, but I couldn’t figure it out, so I just loop through all of them. I also don’t intend to store anything in this directory except CSV files, so checking fsoFile.Type is a bit of waste. Better safe than sorry – that’s what I say when it suits me. I wonder how the Type property changes in other languages or even other Excel versions. I expected something a little more generic. I probably should have used:

That may help with other versions, but probably not other languages.

14 thoughts on “Opening the Newest File in a Folder with VBA”

  1. This is a very useful function. I think whatever you do to find the newest file you need to iterate over the .files collection to get the names and create dates, so you may not be able to avoid looping using vbscript.

  3. Sub recentfile()
      c0 = Dir(“E:OF*.csv”)
      Do Until c0 = “”
        If CreateObject(“scripting.filesystemobject”).Getfile(c0).datecreated > c1 Then
          c1 = CreateObject(“scripting.filesystemobject”).Getfile(c0).datecreated
          c2 = c0
        End If
        c0 = Dir
      Loop
      Workbooks.Add c2
    End Sub

  4. Some improvement:

    Sub recentfile()
      c0 = Dir(“E:OF*.csv”)
      Do Until c0 = “”
        with CreateObject(“scripting.filesystemobject”).Getfile(c0)
          If .datecreated > c1 Then
            c1 = .datecreated
            c2 = c0
          End If
        end with
        c0 = Dir
      Loop
      Workbooks.Add c2
    End Sub

  6. An easier way? How about running a console command?

    for /F %%a in (‘dir /b/o-d “directorynamehere*.csv”‘) do (start %%a & exit)

    Use VBA’s Shell function to run it.

    This is what command processors and shells were meant to do. Trying to do the same thing with any procedural language (no matter how much OO is piled onto it) requires lots more code.

    If all you have is a hammer, all problems look like nails.

  7. A possible alternative approach provided “I almost never want the CSV to remain on the disk” and the exported data is reasonably similar in format:
    Export to the same file name each time and set up a workbook with a query to the csv file as an external data source. Export the file, refresh the query and manipulate as required. If sets of similar data are being exported then pivot tables or other analysis tools can be left setup in the workbook and simply refreshed with the new data.

  8. Hello Experts,

    I want to open latest file from a folder using a macro. I don’t see any extension to the file. the file is in Windows shell command format. File name is constant followed by date in yyyymmdd format. Can you please provide any script that will help. Thanks in Advance.

    Regards,
    Naveen

  9. the ‘latest’ (most recent) file in folder G:\OF\

  10. snb’s ‘cmd’ command is robust, and the .Exec() object also has an stdERR stream for more informative error messages.

    However, the .Exec() object always flashes the Command window on-screen, and there’s no way to hide the annoying flicker.

    I chose to run snb’s command in a wShell .Run() object, pipe the results to a file, and read the file. It’s clunky, and it needs quite a lot of ‘wrapper’ code, but it’s still far faster than iterating the FSO.Files or Dir() collection on a large network folder:

  11. You might consider using:

  12. Just studying ‘Nigel Heffernans’ solution after (flawlessly) using it for few years, because I want it to return all files sorted on date….

    However, I wonder if it is really coded as it was intended to be. Shouldn’t it be:

    instead of

    I think the idea is to rescan the folder, but not more often than every 5 seconds OR if the SearchPattern has changed.

  13. Just studying ‘Nigel Heffernans’ solution after (flawlessly) using it for few years, because I want it to return all files sorted on date….

    However, I wonder if it is really coded as it was intended to be. Shouldn’t it be:

    instead of

    I think the idea is to rescan the folder, but not more often than every 5 seconds OR if the SearchPattern has changed.

  14. The above was not what I posted!!!!!

    Just studying ‘Nigel Heffernans’ solution after (flawlessly) using it for few years, because I want it to return all files sorted on date….

    However, I wonder if it is really coded as it was intended to be. Shouldn’t it be:
    If nLastCall < VBA.Timer - 5 Or SearchPattern <> sLastSrch Or sLastFile = "" Then
    instead of
    If nLastCall < VBA.Timer - 5 Or SearchPattern > sLastSrch Or sLastFile = "" Then
    I think the idea is to rescan the folder, but not more often than every 5 seconds OR if the SearchPattern has changed.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.