Find and open latest csv file in given directory

[ChrisAch]

Hi All

I am trying to search a directory

"B:\CHRIS A\MISSING DATA" (Example directory)

loop through the sub folders within the above directory, and find the latest created csv file and open.

I have tried a similar macro, for .zip Files, but am unable to make work.

Can anyone point me in the right direction?

Thank you

[snb]

Sub M_snb()
  c00="B:\CHRIS_A\MISSING_DATA\"
  workbooks.open split(createobject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/a-d/o-d").stdout.readall,vbcrlf)(0)
end sub

To stress the important things in red:

Sub M_snb()
c00="B:\CHRIS_A\MISSING_DATA\"
workbooks.open split(createobject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/a-d/o-d").stdout.readall,vbcrlf)(0)
end sub

[ChrisAch]

Hi snb, this works great. thank you

[ChrisAch]

snb, just a quick one....

I notice the above code that you provided

Sub M_snb()
  c00="B:\CHRIS_A\MISSING_DATA\"
  workbooks.open split(createobject("wscript.shell").exec("cmd /c dir """  & c00 & "*.csv"" /b/s/a-d/o-d").stdout.readall,vbcrlf)(0)
 end sub

opens up a csv file within given directory, but it does not open the 'latest' i.e most recently created csv in the directory.

Is anyone able to advise how to adapt the above to cater for the requirement?

Thank you

[snb]

there's an 's' missing:

Sub M_snb() 
    c00="B:\CHRIS_A\MISSING_DATA\" 
    workbooks.open split(createobject("wscript.shell").exec("cmd /c dir """  & c00 & "*.csv"" /b/s/a-d/o-d").stdout.readall,vbcrlf)(0) 
End Sub

I hope you adapted the Pathname.

[ChrisAch]

Hey snb, hope your well.

Yep, I adapted the pathname accordingly , which is fine, and it does open A file from the correct directory, but its opening a file from 2015 not a recent file from today or yesterday. I checked the code for a missing 's' but could not see where its missing.

[mdmackillop]

s was missing from "split"

[ChrisAch]

ah, I already worked that out, and would not work full stop had I not added the s. However the code still only picks up the oldest file in the directory as opposed to the newest. weird

[mdmackillop]

This will list the file data which may assist in resolving the issue.

Sub MD_snb()
c00 = "B:\CHRIS_A\MISSING_DATA\"
Set fs = CreateObject("Scripting.FileSystemObject")
x = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/a-d/o-d").stdout.readall, vbCrLf)
Cells(1, 1).Resize(, 4) = Array("File Name", "Created", "Modified", "Accessed")
Cells(2, 1).Resize(UBound(x)).Value = Application.Transpose(x)
For Each cel In Cells(2, 1).Resize(UBound(x))
Set f = fs.GetFile(cel)
cel.Offset(, 1) = f.datecreated
cel.Offset(, 2) = f.datelastmodified
cel.Offset(, 3) = f.datelastaccessed
Next
Cells(1, 1).CurrentRegion.Columns.AutoFit
' Workbooks.Open Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/a-d/o-d").stdout.readall, vbCrLf)(0)
End Sub

[snb]

You are mistaken: the files are being sorted descendingly /o(rder)-d(ate).
So the first file will be the most recent one.
You can check it, ignoring the subfolders:

Sub M_snb() 
    c00="B:\CHRIS_A\MISSING_DATA\" 
    workbooks.open split(createobject("wscript.shell").exec("cmd /c dir """  & c00 & "*.csv"" /b /a-d /o-d").stdout.readall,vbcrlf)(0) 
End Sub

[ChrisAch]

You are mistaken: the files are being sorted descendingly /o(rder)-d(ate).
So the first file will be the most recent one.
You can check it, ignoring the subfolders:

Hi snb, I do not believe I'm mistaken, given I can see the date on the ribbon being 2015!

I tried your test above but because all of the csv files are within folders in the directory the code does not pick up any csv's at all.

Unless im not understanding your code correctly - but it definitely opens up a file from 2015 which would be one of the oldest folders in the directory.

[ChrisAch]

Hey Md

Stalls on this line. I took a guess should be cell, but that also did not work 'invalid procedure or argument'

 Set f = fs.GetFile(cel)

Sub MD_snb()
    c00 = "\\ukhibmdata04.HBEU.ADROOT.HSBC\global\GFX_DFA\sentToMO\PB-EB Missing UTI Issue\"
    Set fs = CreateObject("Scripting.FileSystemObject")
    x = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/a-d/o-d").StdOut.ReadAll, vbCrLf)
    Cells(1, 1).Resize(, 4) = Array("File Name", "Created", "Modified", "Accessed")
    Cells(2, 1).Resize(UBound(x)).Value = Application.Transpose(x)
    For Each cel In Cells(2, 1).Resize(UBound(x))
        Set f = fs.GetFile(cel)
        cel.Offset(, 1) = f.DateCreated
        cel.Offset(, 2) = f.DateLastModified
        cel.Offset(, 3) = f.DateLastAccessed
    Next
    Cells(1, 1).CurrentRegion.Columns.AutoFit
     ' Workbooks.Open Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/a-d/o-d").stdout.readall, vbCrLf)(0)
End Sub

[ChrisAch]

Chaps, I used Md's code to list all files - worked out what I did wrong.

So .. the list generated sorts and for some strange reason sorts the top file as 14/12/2015

When I manually z-a the list the most recently created file is actually 14/04/2016

but the macro, is opening 2015 given that's the file that is at the top of the pile on the back of the code.

Any thoughts?

[ChrisAch]

14/07/2016 even

[mdmackillop]

Try

 Workbooks.Open Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/t:c/o-d").stdout.readall, vbCrLf)(0)

[ChrisAch]

Hmmm, still doesent work. I think the code is potentially opening the 'last accessed' file, rather than the 'recently created' Please note my directory file names are examples. File Name	Created	Modified	Accessed
\\20151214\Chris\error\20151214	14/12/2015 07:00	14/12/2015 07:00	14/07/2016 13:33
\\20151215\Chris\error\20151215	15/12/2015 07:00	15/12/2015 07:00	15/12/2015 17:27
\\20151216\Chris\error\20151216	16/12/2015 07:00	16/12/2015 07:00	16/12/2015 12:31
\\20151217\Chris\error\20151217	17/12/2015 07:00	17/12/2015 07:00	17/12/2015 11:57
\\20151218\Chris\error\20151218	18/12/2015 07:00	18/12/2015 07:00	18/12/2015 11:30
\\20151221\Chris\error\20151221	21/12/2015 07:00	21/12/2015 07:00	21/12/2015 10:59
\\20151222\Chris\error\20151222	22/12/2015 07:00	22/12/2015 07:00	22/12/2015 11:43

[mdmackillop]

This should show in Date Created descending order. You can see the switch coding here

Sub MD_snb()
    c00 = "C:\TestDir\"
    Set fs = CreateObject("Scripting.FileSystemObject")
    x = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/t:c/o-d").StdOut.ReadAll, vbCrLf)
    Cells(1, 1).Resize(, 4) = Array("File Name", "Created", "Modified", "Accessed")
    Cells(2, 1).Resize(UBound(x)).Value = Application.Transpose(x)
    For Each cel In Cells(2, 1).Resize(UBound(x))
        Set f = fs.GetFile(cel)
        cel.Offset(, 1) = f.DateCreated
        cel.Offset(, 2) = f.DateLastModified
        cel.Offset(, 3) = f.DateLastAccessed
    Next
    Cells(1, 1).CurrentRegion.Columns.AutoFit
     ' Workbooks.Open Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s/t:c/o-d").stdout.readall, vbCrLf)(0)
End Sub

[ChrisAch]

Hey Mdmackillop

Yes that definitely shows created date in descending order. The newest created date at the bottom as expected.

So Im guessing, my question is, how do I then open that file.

Ive tried the code above, but it 100% opens 20151213.csv not 20160713.csv.

I just want to be able to open the latest created csv file in the directory.

Thanks for your patience :-)

[mdmackillop]

Have you "uncommented" the last line in the code?

This is contradictory

Yes that definitely shows created date in descending order. The newest created date at the bottom as expected.

[ChrisAch]

Example.jpg

Hi

Hopefully this shows in more granular detail ...

The code sorts the files into order, but opens 10/07/2016 instead of 14/07/2016 or what ever the file is at the bottom of the sorted pile.