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: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 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)
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!
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:
Code: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
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 M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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.
Bookmarks