macro to pull all data from every file in a folder and copy Data into the open workbook, then delete the file before the loop

[lauhelp]

Hello,

I have a code that opens each excel file in a folder, copies a range from that file and paste it on the other main file which I call consolidated database. This part of the code works fine, what is troubling me is that it only deletes all the file in that folder when the loop has been completed. Here's part of my current code that deletes all of them:

Sub
My Code here to open, copy, then paste...

Loop

Kill "C:\Users\[Redacted - My User ID]\OneDrive - [Redacted - My Company's Name]\Temp\*.xls"
End Sub

What I want to happen is to add a code to delete each file after it has been opened, information has been copied, and then pasted to database, and closed... just before opening another file. Reason is that when loop process fails sometime, it will not delete those files that have been recorded to my main database already, and I would have to manually check thousand of files, when I am processing a batch file for consolidation.

I hope my narration makes sense.

Many thanks in advance!

 

[John_w]

You need to show your code which loops through the folder. If you're using a Dir function loop you can delete the current file with a Kill statement before getting the next file, like this:

    Dim fileName As String
    fileName = Dir("C:\folder\path\*.xls")
    While fileName <> vbNullString
        
        'Code to open and close fileName
        
        Kill "C:\folder\path\" & fileName
        fileName = Dir
    Wend

 

[lauhelp]

You need to show your code which loops through the folder. If you're using a Dir function loop you can delete the current file with a Kill statement before getting the next file, like this:

    Dim fileName As String
    fileName = Dir("C:\folder\path\*.xls")
    While fileName <> vbNullString
      
        'Code to open and close fileName
      
        Kill "C:\folder\path\" & fileName
        fileName = Dir
    Wend

Hi @John_w,

Appreciate you looking into this, here's my actual code:

Sub UpdateDatabase()

'Consolidate
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Dim we As Worksheet
    Set wkbDest = ThisWorkbook
    
    Const strPath As String = "C:\Users\UserID\OneDrive\Temp\"
    ChDir strPath
    strExtension = Dir("*.xls")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open (strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("Sheet1").Range("A2:BV" & LastRow).Copy wkbDest.Sheets("Database").Cells(Rows.Count, "A").End(xlUp).Offset(1,0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = Truew

'Delete all files in the folder

Kill "C:\Users\UserID\OneDrive\Temp\*.xls"

End Sub

I want to be ale to delete the file after the code .Close savechanges:=False.

Thanks John!

 

[John_w]

As I said, a Kill statement before getting the next file. Therefore replace:

        strExtension = Dir

with:

        Kill strPath & strExtension
        strExtension = Dir

 

[lauhelp]

As I said, a Kill statement before getting the next file. Therefore replace:

        strExtension = Dir

with:

        Kill strPath & strExtension
        strExtension = Dir

Works like a charm! Thank you so much!