Merging Multiple excel files into a master file

[terrif]

I was hoping someone would be able to help me. I am trying to look for the VBA code needed in order to create a Macro in an Excel File that would take the data entered by a user and merge/append it to a master file.

So basically it would be like this.

Master File
1 entry file
2 entry file
3 entry file

I need to take the data from each entry file and merge/append it into the Master File. Update any existing records with the new Status and completed date and add in any new records that do not already exist. Each entry file will only have the data of the person using that file. Entry Files are daily requests. Master file will house all the daily requests by each person so there can then be analysis on the workload.

I tried to use the Compare and Merge Workbooks, but data was getting over written by each users entry. I guess it does by row number and each user can be using Row two, but for there own entry.

I just don't know if there is any way that you can be able to do this without each user overriding the other users data.

Any assistance in this would be much appreciated.

Thanks,
Terri

[Bob Phillips]

Have you got access to Power Query? This is a simple function in that.

[terrif]

Have you got access to Power Query? This is a simple function in that.

I don't have power query, but I can see that it is a free download. Since I don't have admin rights not sure if it will let me download and install. Is this something that would need to be on all users Excel?

I actually just tried and it will not let me download it since I don't have Admin rights. Also I don't see them letting us have this either.

I was trying the following VBA, but when I try and run it nothing happens.

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "C:\Users\tefe\Desktop\RR_Requests"
MyFile = Dir(Filepath)


Do While Len(MyFile) > 0
If MyFile = “RR_Master.xlsm” Then
Exit Sub
End If


Workbooks.Open (Filepath)
Range("A2:J2").Copy
ActiveWorkbook.Close


erow = RR_Requests.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("RR_REQUESTS").Range(Cells(erow, 1), Cells(erow, 10))


MyFile = Dir
Loop
End Sub

I found this online and was not exactly sure if it would work with what I was trying to do, but I thought I would give it a try.

[Bob Phillips]

I don't have power query, but I can see that it is a free download. Since I don't have admin rights not sure if it will let me download and install. Is this something that would need to be on all users Excel?

That depends on whether you want them to merge the data, or just use the merged data.

I actually just tried and it will not let me download it since I don't have Admin rights. Also I don't see them letting us have this either.

Then suggest that they are welcome to write you an application to do what you need to do.

I was trying the following VBA, but when I try and run it nothing happens.

I would suggest that is because Filepath should have a \ at the end, the open should include the filename, and it immediately encounters RR_Master.xlsm and exits sub.

See if this is better ...

Sub LoopThroughDirectory()
Dim this As Workbook
Dim wb As Workbook
Dim MyFile As String
Dim erow As Long
Dim Filepath As String

    Set this = ActiveWorkbook
    
    Filepath = "C:\Users\xld\Documents\2015 London Power BI\Materials\" '"C:\Users\tefe\Desktop\RR_Requests"
    MyFile = Dir(Filepath, vbNormal)
     
    With this.Worksheets("RR_REQUESTS")
    
        Do While Len(MyFile) > 0
            
            If MyFile <> "RR_Master.xlsm" Then
             
                Set wb = Workbooks.Open(Filepath & MyFile)
                wb.Worksheets(1).Range("A2:J2").Copy
                erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                .Paste Destination:=.Cells(erow, 1).Resize(, 10)
            End If
             
            MyFile = Dir
        Loop
    End With
End Sub

[terrif]

Well that did not work either, but I went in another direction and it seems to work. Within my Master file I am just linking to the data through an ODBC data source. This seems to work for what we need.

Thanks for your suggestions. Have a good day.

Terri