0

I have Office 2013 installed on Windows 7 x86. I have 4 Excel workbooks that have the same columns, and I'm thinking to connect those 4 Excel files to one "Master", so everyday these 4 Excel files automatically send the data to the master file. Keep in mind that the sent row will not be sent again the next day. Is there way to do that on Excel 2013?

Thanks.

0

For Excel 2010 or 2013, I would use the Power Query Add-In for this. From Excel 2016, Power Query is built in to the Data ribbon under "Get and Transform" section.

Power Query can start from an folder containing Excel files. It returns this as a table listing the files, which you can Filter as required. The key technique at that point is to Add a Column with the formula:

Excel.Workbook ( [Content] )

You can then expand that column to get a list of the Tables and Named Ranges in each file. Keep expanding and you will get to the row data in each table. Providing the files are consistent, you should be able to filter down to just the data you want. The result can be written out a new Excel Table.

By default this will do a full refresh each time you tell it to Refresh your Query - this is usually the most reliable method. If you need something more complicated, you can read in the "Master" data and match it using a Merge, to eliminate rows already loaded.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.