Results 1 to 2 of 2

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

  1. #1
    Neophyte ValueLinks's Avatar
    Join Date
    Dec 2011
    Beautiful British Columbia

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

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    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
            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, _
                Else 'Using XL97
                    .Parent.Hyperlinks.Add _
                    Anchor:=.Offset(0, 1), _
                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, _
                    Else 'Using XL97
                        .Hyperlinks.Add _
                        Anchor:=ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Offset(1, 0), _
                    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
    End Sub
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts