CODEWITHSUNDEEP
excelvba
Jandhi
Jandhi

Reputation: 1

Collate several files, each housed in separate folders, into a single folder

I need to copy 20+ files, each housed by unique folders, into a single folder. I know this should be fairly simple. I've created the below code but I keep getting a Compile Error: Object required error box. The below code looks sound to me so I'm really struggling to find where the error is.

Folder name is the date of the report (eg. 090118), hence, I decided to use a loop until the end of the month (931). I also added an error handling code so we can skip the holidays and weekends.

Sub CopyFiles()

    Dim NewFolder As String
    Dim NDay As Long
    Dim FileName As String
    Dim Month As Variant


    Month = InputBox("Enter month, eg. 01-January")
    NewFolder = "C:\Results\Trading\2018\" & Month & "\Backtest Daily Files\Daily GS\" 'Don't forget to edit this
    NDay = 901

    On Error Resume Next

    Do While NDay < 931


        FileName = Dir("C:\Reports\2018\" & Month & "\0" & NDay & "18\GS_Futures*.cs*")

        FileCopy FileName, NewFolder

        NDay = NDay + 1

    Loop

End Sub

Upvotes: 0

Views: 40

Answers (2)

Jandhi
Jandhi

Reputation: 1

Found a solution. Needs some tweaking, but I'm all set for now. Thank you to all who took the effort to help. The problem was that I wasn't giving a file name to the destination just the folder (no .csv).

Sub CopyFiles()

Dim NewFolder As String
Dim NDay As Long
Dim FileName As String
Dim Month As String


Month = InputBox("Enter month, eg. 01-January")
NDay = 901

On Error Resume Next

Do While NDay < 931


    FileName = "M:\MRL\2018\" & Month & "\0" & NDay & "18\GS_Futures_0" & NDay & "18.csv"
    NewFolder = "M:\RMC reports\Trading\2018\" & Month & "\Backtest Daily Files\Daily GS\GS_Futures_0" & NDay & "18.csv"


    FileCopy FileName, NewFolder


    NDay = NDay + 1

Loop

End Sub

...

Upvotes: 0

user4039065
user4039065

Reputation:

Dir doesn't return the full path. It either returns the file name only if found or a zero-length string if not found. If you want a definitive path and file name reference then you need to prefix the returned string with the folder.

An example would be,

...
Do While NDay < 931
    FileName = Dir("C:\Reports\2018\" & Month & "\0" & NDay & "18\GS_Futures*.cs*")

    if cbool(len(filename)) then _
        FileCopy "C:\Reports\2018\" & Month & "\0" & NDay & "18\" & FileName, NewFolder

    NDay = NDay + 1
loop
...

Seems to me that it would be easier to perform a recursive folder search within C:\Reports\2018\ but that's just MO. Relying on a user to type static input like 01-January unerringly is folly.

Upvotes: 1

Related Questions