Update Master if there's new data in separate sub-books

[ValueLinks]

Newbie here using Excel 2010. Have spent over 6 hours trying to find a solution to my problem but have had no luck thus far. Hopefully an expert can lend their knowledge and help build a macro that would :

Update a master workbook (Test.xlsx) with other workbooks in the same directory


I used the master workbook to filter data and created individual sub-workbooks. At this point both Master and sub-books contain the same data (based on the ID number). These sub-workbooks are then given to the manager to review and update. The managers can only update data in white/blank colored cells (they cannot touch cells in light-blue). Once they have updated their own workbooks I'd like to run a macro that automatically looks in to each sub-book (ie. Test - Tim.xlsx) and update the master workbook only if the data is new (Sub-book updates the Master only if sub-book data is different than the Master).

Some constants:

• The one matching key in the all the workbooks is the ID number in column A.
• Column headers are the same between Master and sub-books
• Managers could add new columns but cannot add new lines (new ID number)

In summary: Can a macro in the Master look in to various Sub-books (in the same directory) grab new data and overwrite the same data in the Master?

The macro would have to compare the cell with the corresponding ID number and Column Header Name in the Master with the corresponding cell in the Sub-book. If the cell is different in the Sub-book then the Master would grab that data and overwrite the corresponding cell in the Master.

I've attached sample files. Sub-books are named "Test - ????.xlsx" where there are various names such as Test - Mike.xlsx
The master is simply "Test.xlsx"



Not sure where to start, hoping someone can help direct me or provide a starting point. Let me know if you have questions, looking to learn as much as possible here. Thank you in advance!

[Ken Puls]

Can it be done? Absolutely. But there's going to be some questions.

The issue you always have to worry about here is what things are constants, as you'll need those in order to reach into the other workbooks. I'm trying to think of the best way to do it.

Will the column headers always be the same? (Will the manager's ever change those?)

The loop of all files in the directory is the easy part. I'm thinking that, if the column headers always stay the same, we could actually use a SQL query to pull the data into the master workbook temporarily, then we'd need to check each record and update it. The trick will be to make that part efficient.

How are your VBA skills so far?

The following will create a Hyperlinked list of all Excel files in a folder, which would be the starting point of what files needed to be looked at:

Sub HyperlinkFileList()
     'Macro purpose:  To create a hyperlinked list of all files in a user
     'specified directory, including file size and date last modified
     'NOTE:  The 'TextToDisplay' property (of the Hyperlink object) was added
     'in Excel 2000.  This code tests the Excel version and does not use the
     'Texttodisplay property if using XL 97.
     
    Dim fso As Object, _
    ShellApp As Object, _
    File As Object, _
    SubFolder As Object, _
    Directory As String, _
    Problem As Boolean, _
    ExcelVer As Integer
     
     'Turn off screen flashing
    Application.ScreenUpdating = False
     
     'Create objects to get a listing of all files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")
     
     'Prompt user to select a directory
    Do
        Problem = False
        Set ShellApp = CreateObject("Shell.Application"). _
        Browseforfolder(0, "Please choose a folder", 0, "c:\\")
         
        On Error Resume Next
         'Evaluate if directory is valid
        Directory = ShellApp.self.Path
        Set SubFolder = fso.GetFolder(Directory).Files
        If Err.Number <> 0 Then
            If MsgBox("You did not choose a valid directory!" & vbCrLf & _
            "Would you like to try again?", vbYesNoCancel, _
            "Directory Required") <> vbYes Then Exit Sub
            Problem = True
        End If
        On Error GoTo 0
    Loop Until Problem = False
     
     'Set up the headers on the worksheet
    With ActiveSheet
        With .Range("A1")
            .Value = "Listing of all files in:"
            .ColumnWidth = 40
             'If Excel 2000 or greater, add hyperlink with file name
             'displayed.  If earlier, add hyperlink with full path displayed
            If Val(Application.Version) > 8 Then 'Using XL2000+
                .Parent.Hyperlinks.Add _
                Anchor:=.Offset(0, 1), _
                Address:=Directory, _
                TextToDisplay:=Directory
            Else 'Using XL97
                .Parent.Hyperlinks.Add _
                Anchor:=.Offset(0, 1), _
                Address:=Directory
            End If
        End With
        With .Range("A2")
            .Value = "File Name"
            .Interior.ColorIndex = 15
            With .Offset(0, 1)
                .ColumnWidth = 15
                .Value = "Date Modified"
                .Interior.ColorIndex = 15
                .HorizontalAlignment = xlCenter
            End With
            With .Offset(0, 2)
                .ColumnWidth = 15
                .Value = "File Size (Kb)"
                .Interior.ColorIndex = 15
                .HorizontalAlignment = xlCenter
            End With
        End With
    End With
     
     'Adds each file, details and hyperlinks to the list
    For Each File In SubFolder
        Select Case Right(File.Path, 4)
        Case Is = ".xls", ".xlt", ".xla", "xlsx", "xlsm", "xltx", "xltm", "xlam"
            With ActiveSheet
                 'If Excel 2000 or greater, add hyperlink with file name
                 'displayed.  If earlier, add hyperlink with full path displayed
                If Val(Application.Version) > 8 Then 'Using XL2000+
                    .Hyperlinks.Add _
                    Anchor:=ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Offset(1, 0), _
                    Address:=File.Path, _
                    TextToDisplay:=File.Name
                Else 'Using XL97
                    .Hyperlinks.Add _
                    Anchor:=ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Offset(1, 0), _
                    Address:=File.Path
                End If
                 'Add date last modified, and size in KB
                With .Range("A" & ActiveSheet.Rows.Count).End(xlUp)
                    .Offset(0, 1) = File.datelastModified
                    With .Offset(0, 2)
                        .Value = WorksheetFunction.Round(File.Size / 1024, 1)
                        .NumberFormat = "#,##0.0"
                    End With
                End With
            End With
        Case Else
            'Ignore file as not an excel file
        End Select
    Next
     
End Sub