Move, Rename all files from a folder

[kinseld5]

Hi,

I have a folder which is constantly updated with files every 5 minutes and the naming convention is fairly complex and unnecessary but I cannot change it.

Basically I need a macro to automatically move all files from this folder 'C:\Test1' to another folder 'C:\Test2', rename the moved files based on most recently added, most recently added being '1.csv' and open up each of these .csv files on a different sheet in an active workbook. After this has completed the files in 'C:\Test2' will be deleted.

I have been at this for hours and cannot do it so any help is much appreciated.

Regards,
David

[Chippy]

See if this does what you want.

Option Explicit

Sub Import_Files()

    Dim sourceFolder As String, destinationFolder As String
    Dim fileName As String
    Dim fileNumber As Integer
    
    sourceFolder = "C:\Test1\"
    destinationFolder = "C:\Test2\"
    
    If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
    
    'Move files from source folder to destination folder, renaming them as 1.csv, 2.csv, 3.csv etc.
    
    fileNumber = 0
    fileName = Dir(sourceFolder & "*.*")
    While fileName <> ""
        fileNumber = fileNumber + 1
        Name sourceFolder & fileName As destinationFolder & fileNumber & ".csv"
        fileName = Dir
    Wend
    
    'Import *.csv files from destination folder into a new sheet in this workbook and delete each file
    
    fileName = Dir(destinationFolder & "*.csv")
    While fileName <> ""
        Workbooks.Open destinationFolder & fileName
        With ActiveWorkbook
            .Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            .Close savechanges:=False
        End With
        Kill destinationFolder & fileName
        fileName = Dir
    Wend
        
End Sub

Call Import_Files() using Application.OnTime if you want it to run every 5 minutes.

[kinseld5]

Chippy, You hero. Cheers mate