PDA

View Full Version : [SOLVED] Find and open latest csv file in given directory



ChrisAch
07-12-2016, 02:42 AM
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
07-12-2016, 03:16 AM
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
07-12-2016, 04:54 AM
Hi snb, this works great. thank you

ChrisAch
07-13-2016, 08:17 AM
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
07-13-2016, 09:41 AM
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
07-14-2016, 01:33 AM
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
07-14-2016, 01:35 AM
s was missing from "split"

ChrisAch
07-14-2016, 01:49 AM
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
07-14-2016, 02:08 AM
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
07-14-2016, 02:35 AM
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
07-14-2016, 04:14 AM
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
07-14-2016, 04:16 AM
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 (file://\\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
07-14-2016, 04:28 AM
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
07-14-2016, 04:30 AM
14/07/2016 even

mdmackillop
07-14-2016, 05:01 AM
Try

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

ChrisAch
07-14-2016, 05:45 AM
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 (file://1.51.123.174/Chris/error/20151214)
14/12/2015 07:00
14/12/2015 07:00
14/07/2016 13:33


\\20151215\Chris\error\20151215 (file://1.51.123.175/Chris/error/20151215)
15/12/2015 07:00
15/12/2015 07:00
15/12/2015 17:27


\\20151216\Chris\error\20151216 (file://1.51.123.176/Chris/error/20151216)
16/12/2015 07:00
16/12/2015 07:00
16/12/2015 12:31


\\20151217\Chris\error\20151217 (file://1.51.123.177/Chris/error/20151217)
17/12/2015 07:00
17/12/2015 07:00
17/12/2015 11:57


\\20151218\Chris\error\20151218 (file://1.51.123.178/Chris/error/20151218)
18/12/2015 07:00
18/12/2015 07:00
18/12/2015 11:30


\\20151221\Chris\error\20151221 (file://1.51.123.181/Chris/error/20151221)
21/12/2015 07:00
21/12/2015 07:00
21/12/2015 10:59


\\20151222\Chris\error\20151222 (file://1.51.123.182/Chris/error/20151222)
22/12/2015 07:00
22/12/2015 07:00
22/12/2015 11:43

mdmackillop
07-14-2016, 05:55 AM
This should show in Date Created descending order. You can see the switch coding here (https://technet.microsoft.com/en-us/library/cc755121(v=ws.11).aspx)


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
07-14-2016, 06:12 AM
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
07-14-2016, 06:15 AM
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
07-14-2016, 06:49 AM
16617

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.

mdmackillop
07-14-2016, 07:31 AM
My own test on 100 randomly dated csv files consistently shows Created in Descending order. Are your files stored locally? If not, can you test the code on a local folder. The full path should show in column A.

Kenneth Hobs
07-14-2016, 10:54 AM
Sorting in order tells us nothing. Sort in descending file creation date order is what you want. Try /o:-d rather than /o-d. See this for command shell Dir's help. http://ss64.com/nt/dir.html

You can also try /od.

For the last line in post #18 code before End Sub, what does this show in the Immediate window after a run?

Debug.Print x(0)

If the path is not included in x(0) then that will be a problem. /s should poke the path in x() though. I have a routine that makes sure that happens but uses the concept as other code posted here.

snb
07-14-2016, 02:16 PM
If your system orders in a peculiar (opposite) way you can use:


Sub M_snb()
c00="B:\CHRIS_A\MISSING_DATA\"

st=split(createobject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b /s /a-d /o-d").stdout.readall,vbcrlf)
workbooks.open st(ubound(st)-1)
End Sub

mdmackillop
07-15-2016, 02:59 AM
If the Sort is unreliable, don't use it

Sub MD_snb()
c00 = "C:\TestDir\"
x = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s").stdout.readall, vbCrLf)
Set fs = CreateObject("Scripting.FileSystemObject")
For i = 0 To (UBound(x) - 1)
d = fs.GetFile(x(i)).DateCreated
If d > oldD Then
oldD = d
y = x(i)
End If
Next
Workbooks.Open y
End Sub