Importing data and automatic updating

Hi,



I am intermediate user of excel with no coding background. Not sure if I need to use power query or not for this. If not, any VBA code to accomplish the same would be great.



I want to automate the consolidation of data and processing it for 10 production lines.



Each production line has its own folder on the drive.



Within each line's folder, there is a monthly folder example - January, February, March, April etc.



Within each Monthly folder there is a day folder - May 1, May 2 , May 3, etc.



Within each day there are 3 excel files - Shift 1, Shift 2 and Shift 3.




There is a master file located elsewhere on the drive which processes data that is manually transferred to the master file - which is a pain and super time consuming.



The master file contains 2 worksheets for each production line. I would like to copy and paste specific range of cells (H9:R24) from the shift production line files into specific worksheets within the master file specifically made for those production lines.



The column before the cell ranges where I would like to paste the data (in the master file) contains dates and hence I am hoping that the code/query will help me paste via matching the date from within the shift excel file.



Each shift excel file is exactly the same in terms of naming of columns, formatting, formulas etc.



Once pasted into the specific worksheet within the master file, cells in another worksheet (within the master file) use these numbers to process these via formulas and more numbers are generated such as waste % etc. which will be automatically plotted on different graphs.



I would like to super automate this process with a click of the button in the master file to access the data within those files, copy and paste into specific cells in specific worksheets within the master file and process data according to specified formula (already within the cell) to ultimately give processed numbers and graphs for each line for the previous day.



This needs to be repeated for 30 files - 3 files each for each day for 10 lines - every single day first thing in the morning.



Not sure if I need to use power query or not for this. If not, any VBA code to accomplish the same would be great.



Requesting you kindest help.



Thank you !


Waste Data Project Image.PNG

Hi and welcome to the forum,

We have a great answer for you.... Excel 2016 has a newer tool called Get & Transform that does almost exactly what you request. The older tool was called Power Query but the new stuff is in name only. Learn more about Power Query (Get & Transform in 2016) and you can nail this problem.

http://excelunplugged.com/2015/02/10...n-power-query/
https://www.myonlinetraininghub.com/...-from-a-folder

Power Query is much easier to learn and use than VBA but it will take you a few days to read and learn before it becomes clear how much it can do for you.

I hope this answer helps you solve your problem.

Hi hopeful,

When you bring the data into Power Query you can remove columns or rows of data, leaving what you want. Then you close PQ and the remaining data ends up in the normal Excel sheet. You then do formulas and graphs from the data in normal Excel. You need to look at Power Query as just another tool with functions to limit/filter or sort or transpose the data before it gets into Excel. If you add more files into the folder, PQ can refresh using these new files. PQ is much easier to use and understand than VBA.

Please refer the the original post

Hi,

This problem is a matter of you learning enough about Excel to accomplish your need(s). Start by doing what you need to a single file or folder. Then enhance the process to expand it to the next level.

I still lean towards using Power Query as all your data looks the same for all the sheets you are pulling in. Can you tell what date the file is from its name? If not you can change the Source of a Power Query to move from a single file to different files.

There are lots of YouTube videos showing many different ways that PQ can help you. See one at:
https://www.bing.com/videos/search?q...9F43&FORM=VIRE
Note there are over 500,000 rows of data being imported in a few seconds.

You might also look at:
https://www.excelguru.ca/blog/2014/1...h-power-query/