MAC Excel 365 VBA to Combine Multiple Files into one

[roundaboutrc]

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.

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

 

[Ron de Bruin]

I have some examples and also a add-in in this section on my site : Files/Folders | Mac Excel Automation
Post back if you have problems

 

[Ron de Bruin]

This is the link to the add-in if you want to try it

RDBMerge | Mac Excel Automation

Merge Add-in for Excel 2016 or higher for the Mac

macexcel.com

 

[honkin]

Did you ever get a resolution, roundaboutrc? I tried the RDBMerge suggestion, but was unable to get a toolbar button showing after installing the add-in

Here is a simply solution, though.

1. Open Terminal
2. Type pwd and hit Enter - This just shows you the current working directory
3. Type cd /Users/xxx/ and whatever folder you are after. (The xxx is the second part of YOUR working directory which you found out above. I just found it easiest to create a folder on the desktop with a single word name and just use cd /Users/xxx/Desktop/NewFolderName/) This simply changes you into the folder you want to work in
4. Type cat *.csv >xxxxx.csv - xxxxx.csv is the new filename, so put whatever you want. A new file is created out of however many you have in that folder. Only a single header is used.

I hope that helps, if you hadn't already resolved your issue

cheers