Merge excel files into a new excel file based on filename

0 votes

I have a folder containing about 500-600 excel files from a script I have made where the file names end up like this

101a12345.xlsx
101a67899.xlsx
102a12345.xlsx
102a78999.xlsx

The file names follow that patern, 101a, 102a etc. What i want to do is merge those based on that paternt into 1 excel file. Therefore, the 101a12345.xlsx and 101a67899.xlsx should merge into an 101aMaster.xlsx. All excel files are single sheet.

I have found a sample code here which i am trying to implement: How to merge multiple workbooks into one based on workbooks names

Taken from the link above:

Sub test(sourceFolder As String, destinationFolder As String)
    Const TO_DELETE_SHEET_NAME As String = "toBeDeleted"
    '------------------------------------------------------------------
    Dim settingSheetsNumber As Integer
    Dim settingDisplayAlerts As Boolean
    Dim dict As Object
    Dim wkbSource As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim filepath As String
    Dim code As String * 4
    Dim wkbDestination As Excel.Workbook
    Dim varKey As Variant
    '------------------------------------------------------------------


    'Change [SheetsInNewWorkbook] setting of Excel.Application object to
    'create new workbooks with a single sheet only.
    With Excel.Application
        settingDisplayAlerts = .DisplayAlerts
        settingSheetsNumber = .SheetsInNewWorkbook
        .SheetsInNewWorkbook = 1
        .DisplayAlerts = False
    End With


    Set dict = VBA.CreateObject("Scripting.Dictionary")


    filepath = Dir(sourceFolder)

    'Loop through each Excel file in folder
    Do While filepath <> ""

        If VBA.Right$(filepath, 5) = ".xlsx" Then

            Set wkbSource = Excel.Workbooks.Open(sourceFolder & filepath)
            Set wks = wkbSource.Worksheets(1)
            code = VBA.Left$(wkbSource.Name, 4)


            'If this code doesn't exist in the dictionary yet, add it.
            If Not dict.exists(code) Then
                Set wkbDestination = Excel.Workbooks.Add
                wkbDestination.Worksheets(1).Name = TO_DELETE_SHEET_NAME
                Call dict.Add(code, wkbDestination)
            Else
                Set wkbDestination = dict.Item(code)
            End If

            Call wks.Copy(Before:=wkbDestination.Worksheets(1))
            wkbDestination.Worksheets(1).Name = VBA.Mid$(filepath, 6)

            Call wkbSource.Close(False)

        End If

        filepath = Dir

    Loop


    'Save newly created files.
    For Each varKey In dict.keys
        Set wkbDestination = dict.Item(varKey)

        'Remove empty sheet.
        Set wks = Nothing
        On Error Resume Next
        Set wks = wkbDestination.Worksheets(TO_DELETE_SHEET_NAME)
        On Error GoTo 0

        If Not wks Is Nothing Then wks.Delete


        Call wkbDestination.SaveAs(Filename:=destinationFolder & varKey & ".xlsx")


    Next varKey


    'Restore Excel.Application settings.
    With Excel.Application
        .DisplayAlerts = settingDisplayAlerts
        .SheetsInNewWorkbook = settingSheetsNumber
    End With


End Sub

However, this code opens all workbooks and at about 60-70 open excel files i receive an error: Run-time Error '1004' - Method 'Open' of object 'Workbooks' failed.

is there a way to make this code work?

Excel version is pro plus 2016.

Mar 28 in Database by Edureka
• 13,640 points
76 views

1 answer to this question.

0 votes
How do I merge Excel files that have the same name?
Open the Excel file in which you want to combine sheets from different workbooks and do the following steps:
To access the Macro dialogue, press Alt + F8.
Select MergeExcelFiles from the Macro name drop-down menu and hit Run.
The regular explorer window will open, and you can choose one or more workbooks to merge before clicking Open.
answered Mar 31 by gaurav
• 13,560 points

Related Questions In Database

0 votes
0 answers

How to split a string of text in excel based on a given word?

I have a list of combinations of ...READ MORE

Apr 4 in Database by gaurav
• 13,560 points
49 views
0 votes
1 answer

Converting a large XML file to Excel

Using the web URL, import an XML ...READ MORE

answered Mar 24 in Database by gaurav
• 13,560 points
216 views
0 votes
1 answer

Importing Excel files into R, xlsx or xls

To get started, here's a template for ...READ MORE

answered Mar 30 in Database by gaurav
• 13,560 points
37 views
0 votes
1 answer

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

If anyone needs to do this in ...READ MORE

answered Mar 30 in Database by gaurav
• 13,560 points
35 views
0 votes
1 answer

Pasting an Excel range into an email as a picture

Hold down the ALT + F11 keys ...READ MORE

answered Mar 30 in Database by gaurav
• 13,560 points
94 views
0 votes
0 answers

Check Excel file format from PowerScript not relying on file extension

We have a number of Excel files ...READ MORE

Mar 31 in Database by Edureka
• 13,640 points
18 views
0 votes
1 answer

Merge multiple Excel sheets into one sheet

Merging Excel Sheets: A Step-by-Step Guide Make sure ...READ MORE

answered Apr 4 in Database by gaurav
• 13,560 points
336 views
0 votes
1 answer

How do I split a cell in Excel into two or more where they are divided horizontally?

Table cells should be combined. To make a ...READ MORE

answered Apr 6 in Database by gaurav
• 13,560 points
45 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP