Excel macro help for batch process

[Ballatician]

Hi - Hope this is in the right forum.

So, I have an existing macro which can open a bunch of data files in an already open folder consecutively and copy a defined number of cells over to my main sheet (the one in which the macro was built).

That works fine because I had control over the data sheets filenames and I had the macro read a filename from the main sheet and look for the same file in the folder and just made the number go up by 1 each time.

My current problem is that one of our machines outputs data but the only identifier in the filename is the date and time in the format yyyy-Month-Day_Hour_Minute_Second and it is a huge volume of individual sheets with data in csv format that needs to be delimited and then compiled in a single sheet.

Anyone know how I can write a macro that will go through the files in an individual folder by filename in order or by time modified?

I'm not really a pro at this, I just got lucky with the other one because I had a model to work off of and did a lot of googling. This time my google-fu is failing me but I'll keep trying.

TIA

 

[PowerEngineer]

It sounds like you should get familiar with FileSearch. There's some help for this in the VB help (and through Google). Here's an example that I chopped out of one of my spreadsheets that should help give you a sense of what it does:

Dim FFS As Object, FSO As Object, FR As Object

Set FFS = Application.FileSearch
Set FSO = CreateObject("Scripting.FileSystemObject")
With FFS
.NewSearch
.LookIn = str100Dir
.SearchSubFolders = False
.Filename = "__*.txt"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute

If .FoundFiles.Count > 0 Then

' Make sure the number of files is kept below 1000
If .FoundFiles.Count > 1000 Then
FileCount = 1000
MsgBox "Too many files found: " & .FoundFiles.Count & "set back to the maximum of " & FileCount
Else
FileCount = .FoundFiles.Count
End If

For FileIndex = 1 To FileCount
' Find just the file name (no directory info)
intPointerA = InStrRev(FFS.FoundFiles(FileIndex), "\", -1) + 1
strFileNames(FileIndex) = Trim(Mid(FFS.FoundFiles(FileIndex), intPointerA))
Next FileIndex

Else

MsgBox "No files found in the first directory"

End If

End With


Hope this helps...

Not sure exactly what your options are, however the thought of trying to wrestle with a huge multi-sheet spreadsheet in CSV format doesn't bring a smile to my face. Assuming this spreadsheet is actually produced from a CSV formatted text file (or files), you might consider opening the CSV file(s) and processing the individual lines of CSV data instead.

Good luck!

 

[Ballatician]

I'll take a closer look when I get to work tomorrow but this is at least a start. Thanks!