Merge csv file from many folders

[Guest]

I retrieve data from a cash register on a daily base. The data are saved as
follows: C:\dd_mm_yyyy\hh_mm\plu.csv

every day a different folder and subfolder is created with different date
(main folder) and different time(subfolder).

in the .csv file is not stored the date.

What i have to do: tell excel to go to the main folder get the date go to
the subfolder and get the file plu.csv and copy few data and return them in
an excel LIST copy the data say in col B and put the date in column A and
copy through the end of the list. second step back to the main folder (the
one of the day after) and do the same loop untill all the date are retrieved.
PS csv file are closed. i looked at ron debruin web site but i was not able
to get a solution

Thanks
Andrea

 

[Guest]

try this code


Sub getcashregfiles()

datestring = Format(Now(), "dd_mm_yyyy")

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder("C:\temp\" & datestring)

If folder.subfolders.Count > 0 Then
For Each sf In folder.subfolders

Set fso1 = CreateObject _
("Scripting.FileSystemObject")
Set folder1 = _
fso1.GetFolder(sf)
If folder1.Files.Count > 0 Then
For Each file In folder1.Files

'add code to open each file here.
Next file
End If

Next sf
End If

End Sub

 

[Guest]

hi Joel,
i tried but it said path not found. of course i substituted your path with
the right one
thanks anyway
andrea

 

[Guest]

I fully tested the code before I posted it. I generated the same directories
you had listed I always use the temp subdirectory so I don't over-write my
own data on the c drive. After datestring is generated add the following
statement so you can verify the path name

msgbox("c:\" & datestring)

I suspect the date isn't matching the dates you are looking for. The code
uses the now() function which is todays date. It won't collect yesterdays
data.

Here is modifiied code that will allow you to select the date

Sub getcashregfiles()

For x = 9 To 0 Step -1
dates = dates & CStr(10 - x) & " - "
dates = dates & Format(Now() - x, "dd_mm_yyyy") & Chr(10)
Next
datenumber = InputBox("enter Date 1 to 10 " & Chr(10) & dates)
datestring = Format(Now() - (10 - datenumber), "dd_mm_yyyy")

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder("C:\temp\" & datestring)

If folder.subfolders.Count > 0 Then
For Each sf In folder.subfolders

Set fso1 = CreateObject _
("Scripting.FileSystemObject")
Set folder1 = _
fso1.GetFolder(sf)
If folder1.Files.Count > 0 Then
For Each file In folder1.Files

'add code to open each file here.
Next file
End If

Next sf
End If

End Sub