Update Merged Files

[jonastiger]

Hi
I need some help to this, if you please:
I use the code below (credit to Henrik Schiffner, http://professor-excel.com) to merge three large files (each one with one sheet).

Sub MergeExcelFiles()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook

    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

    If (vbBoolean <> VarType(fnameList)) Then

        If (UBound(fnameList) > 0) Then
            countFiles = 0
            countSheets = 0

            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual

            Set wbkCurBook = ActiveWorkbook

            For Each fnameCurFile In fnameList
                countFiles = countFiles + 1

                Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

                For Each wksCurSheet In wbkSrcBook.Sheets
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Next

                wbkSrcBook.Close SaveChanges:=False

            Next

            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic

            MsgBox "Procesed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
        End If

    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub

The problem is that, using code for second time, it duplicates sheets instead of replacing the existing sheets.
So, is there a way to change the code to fit my needs? And, if possible, to update merge task every24hours?

Thank you all in advance.

JT

 

[Chihiro]

You can't use the code as is. Since it's copying entire sheet object. It will insert new sheet each time.

What is your use case? Do you perform additional operation on the merged sheets? If so, above code won't be suitable.

Please upload sample workbook that's being merged and the end result you are looking for. Along with how the collected data will be used.

 

[jonastiger]

Hi
Hope you have great holiday

My goal in this subject is to compile in one single file data from other three files and make easier to get my reports, through PQ (because files are very large size, PQ "from folder" works too slow and drains pc memory). With that, I would have one single source. But sheet names must be the same, so queries can be update correctly.
What I need is, everytime macro runs to merge files sheets have to assume existing name or, in alternative, delete the previous sheets and paste the new ones.

Thanks

 

[Chihiro]

Then before you run the code. Just delete the sheets.

Since I don't know your workbook structure. Can't give you exact code.

Alternately you can check for ISOBJECT and delete if true. This should be done just before the sheet is copied.

If IsObject(ThisWorkbook.Sheets(wksCurSheet.Name)) Then
    Thisworkbook.Sheets(wksCurSheet.Name).Delete
End If