MAC Excel 365 VBA to Combine Multiple Files into one

roundaboutrc

New Member
Joined
Jul 24, 2019
Messages
5
I have many excel files, contained in a single folder, that all contain the same type of data in the same columns with varying amounts of rows in each. I am working on a macro that will allow me to select the folder these files are contained in and will then will loop through that folder and pull the necessary data out of the excel files and combine all of that into one master worksheet. There are 2 issues that I am running into with the code.

1. With the first file the marco is set to pull the headers, but then on the second file and all subsequent files it isn't supposed to grab the headers. But the second file that is pulled the headers are grabbed, but that is the only other file where the headers are pulled.

2. When I only have 4 files in the folder, all of the correct data is pulled as is should, minus issue 1 above. But when I add any additional files to the same folder and run the marco all the data from the first 4 files is pulled but only 20 records from each of the remaining files are pulled. Im really at a loss on this one since it is just a loop doing the exact same thing that it did on the ones previous. HELP LOL.

VBA Code:
Sub Grab_FIles()

    Dim wb As Workbook
    Dim folderPath As String
    Dim RootFolder As String
    Dim ScriptStr As String
    Dim filename As String
    Dim Sheet As Worksheet
    Dim lRow As Long
    Dim n As Long
    Application.ScreenUpdating = False

    
    Set wb = ActiveWorkbook
    
    On Error Resume Next
    RootFolder = MacScript("return (path to desktop folder) as String")

    ScriptStr = "return posix path of (choose folder with prompt ""Select the folder""" & _
            " default location alias """ & RootFolder & """) as string"

    folderPath = MacScript(ScriptStr)
    filename = Dir(folderPath & "*.xls*")

    On Error GoTo 0
    
    If folderPath <> "" Then
    
    n = 1
    Do While n = 1
    n = n + 1
    
        Workbooks.Open filename:=folderPath & filename, ReadOnly:=False
        
        'Create Table
        Workbooks(filename).Activate
        Range("A1").Select
        ActiveCell.CurrentRegion.Select
        ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Table1"
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7, Criteria1:="<>", Operator:=xlFilterValues
        Selection.Copy
        
        wb.Activate
        Sheets("Sheet1").Select
        Range("A1").Select
        lRow = Cells(Rows.Count, 1).End(xlUp).Row
        Cells(lRow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        
        Workbooks(filename).Close SaveChanges:=False
        filename = Dir()
    Loop
    
    'Second Loop
    
    Do While filename <> ""
        Workbooks.Open filename:=folderPath & filename, ReadOnly:=False

        Workbooks(filename).Activate
        Range("A1").Select
        ActiveCell.CurrentRegion.Select
        ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Table1"
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7, Criteria1:="<>", Operator:=xlFilterValues
        Range("A2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        
        wb.Activate
        Sheets("Sheet1").Select
        Range("A1").Select
        lRow = Cells(Rows.Count, 1).End(xlUp).Row
        Cells(lRow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
        Workbooks(filename).Close SaveChanges:=False
        filename = Dir()
    Loop
    
    End If
    
    Application.ScreenUpdating = True
    
End Sub
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,183
Members
416,077
Latest member
SJSB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top