VBA to normalize files in a folder

[BBalazs]

First of all, thank you for all the support I got here during my previous question. I much appreciate it.

I am still learning VBA, slowly getting the hang of it, but now I've ran across a complex problem which I have just no idea how to handle. Here is how it goes.

I have my workbook stored in "myDir" and a subfolder, myDir & "\CSV" contains source files which I will be using later. The problem is that the source files are updated, but not overwritten automatically. Therefore, the folder contains files like this:
Apples_Report.csv
Oranges_Report.csv
Oranges_Report-1.csv
Pears_Report.csv
Pears_Report-1.csv
Pears_Report-2.csv

I will have to clean this folder using VBA. Here is the logic of what I'd like to do (as this is not real code, I'm not sure if I need to wrap it into CODE tags, sorry about that):

Iterate for All Files

Get CurrentFilename
id = 1
If (CurrentFilename & "-" & id & ".csv") is Found
Then Delete CurrentFilename
Else Next File

mainiter:
id = id+1
If (CurrentFilename & "-" & id & ".csv") is Found
Then Delete CurrentFilename & "-" & id-1 & ".csv"; GoTo mainiter
Else Rename File CurrentFilename & "-" & id & ".csv" To CurrentFilename & ".csv"; Next File

Something like that... Of course that riddle is obviously ambiguous and has logical loopholes but technically:

For files that have 1 instance only, leave them intact
For files that have more recent instances, leave only the file with the largest suffix, delete the others and rename it to a suffix-free version.
Do this for all files in the CSV subfolder.


I know I should perhaps be using the FileSystemObject, but I have little experience with that. Do you thing you can help me out?

 

[Gideon1973]

Edit: So we cross-posted one-another bigtime :D Thanks for answering my question, I will fiddle around with that and come back if I fail miserably Thanks for your help!

No problem...glad the code is working out for you! Hope the comments in the code help you get a handle on how to traverse the FSO API! Once you get the hang of it, its very nice...

I misspoke earlier about moving the constant to the CleanupReportFiles() routine and making it a string variable. Since I reference that constant in the Private Sub DeleteIncrementalFiles(), you won't have variable scope of the ReportFolder variable within that routine unless you pass it in. So, I would leave that constant at the module level, but then turn it into a variable. That way, all routines in the module would "see" that variable. You would just assign it early on in the CleanupReportFiles() routine.

Something like this:

Private FSO As New Scripting.FileSystemObject
[B]Private ReportFolder As String[/B]


Public Sub CleanupReportFiles()
    Dim oFolder As Scripting.Folder, oFile As Scripting.File
    
[B]    ReportFolder = ThisWorkbook.Path & "\CSV\"[/B]


    Set oFolder = FSO.GetFolder(ReportFolder) ' intialize report folder variable
    ...
    ...
    ...
End Sub

Oh, and I changed the FSO variable to Private since that's really the proper scope based on how and where it's being used.