Hi Everyone,

I have a small company that receives orders via an excel file. The excel order forms are always exactly same. I've attached three example order forms. I need to keep track of how much we are selling of each item in a master excel file. Is there a way to extract the data from each file in the folder and add it together into a master excel file? This needs to be a simple solution, since we are continuously receiving orders and need to continuously update this master file. Also, I am running excel 2011 on a mac.

I already posted this problem a few weeks ago and I got some great answers but we couldn't get the macro to work on mac. Here is the thread:
HTML Code: 
Here is the macro code I was trying:
Option Explicit

Sub ImportOrders()
Dim fPATH As String, fMAIN As String, fNAME As String, wbDATA As Workbook, MyItem As Range, wsMAIN As Worksheet
Dim ProductCol As Long, RALrow As Long, wbProdCol As Long, wbRALrw As Long, ItemRow As Long, ItemCol As Long, wbLR As Long

fPATH = "untitled:Users:kylemccoy:Google Drive:Blocz:Orders:New:"          'don't forget the final \ in this folder string
fMAIN = "untitled:Users:kylemccoy:Google Drive:Blocz:Orders:"        'don't forget the final \ in this folder string

Set wsMAIN = ThisWorkbook.Sheets("Order")   'sheet to collect information into
                                            'spot the reference col for products and ref row for Ral #
ProductCol = wsMAIN.Cells.Find("Product #", LookIn:=xlValues, LookAt:=xlWhole).Column
RALrow = wsMAIN.Cells.Find("RAL #", LookIn:=xlValues, LookAt:=xlWhole).Row

fNAME = Dir(fPATH , MacID("XLS5"))               'get the first filename for import

Do While Len(fNAME) > 0                     'one file at a time
    Set wbDATA = Workbooks.Open(fPATH & fNAME)                  'open the found file
    With wbDATA.Sheets("Order")                                 'reference the ORDER sheet
        wbProdCol = .Cells.Find("Product #", LookIn:=xlValues, LookAt:=xlWhole).Column  'find the Product column
        wbRALrw = .Cells.Find("RAL #", LookIn:=xlValues, LookAt:=xlWhole).Row           'find the RAL # row
        wbLR = .Cells.Find("Shipping", LookIn:=xlValues, LookAt:=xlWhole).Row - 2       'find the last row of order data

        For Each MyItem In .Range("G" & wbRALrw + 3 & ":AH" & wbLR).SpecialCells(xlConstants)   'check each ordered item
            If IsNumeric(MyItem.Value) And MyItem.Value > 0 Then                                'make sure it's a valid number
                                                                                                'match item to the MAIN worksheet
                ItemRow = WorksheetFunction.Match(.Cells(MyItem.Row, wbProdCol).Value, wsMAIN.Columns(ProductCol), 0)
                ItemCol = WorksheetFunction.Match(.Cells(wbRALrw, MyItem.Column).Value, wsMAIN.Rows(RALrow), 0)
                wsMAIN.Cells(ItemRow, ItemCol).Value = wsMAIN.Cells(ItemRow, ItemCol).Value + MyItem.Value  'update the MAIN
            End If
        Next MyItem
    End With
    wbDATA.Close False                      'close the found workbook
    
    Name fPATH & fNAME As fMAIN & fNAME     'move the file
    fNAME = Dir                             'get the next filename
Loop

ThisWorkbook.Save                           'save the main
End Sub
Thanks in advance for the help,
Kyle


Master-1.xlsm
Order Form ex3.xlsx
Order Form ex2.xlsx
Order Form ex1.xlsx