Forum Discussion
Updating existing sheet with new data from another workbook
Hi there all, forgive the newb query, I hope I make my problem clear.
I am happy to share the Workbooks I am referring to 🙂
I have 2 workbooks that I need to link to each other so that new data in (1) updates data in (2) automatically. I have questions to do with each.
Workbook 1. Rawdata.xlsx.
Data source is MS Forms (Preview/Free Version) and using the "download Excel" option.
The name suffix changes with each download eg: Rawdata (1-14) and next will be Rawdata (1-21) etc (Each of these is downloaded to a separate folder)
Question 1. Is there to deal with name suffix change, apart from renaming each downloaded worksheet? seems clumsy to me, I'm hoping for an easier way.
Workbook 2. Analysis.xlsx
The first tab "DATA" contains filtered data sourced from Rawdata.xlsx . The rest of the tabs source their data Tables from the DATA tab.
Question 2. My problem apart from Question 1 is that I cannot get the data from Rawdata.xlsx to update the DATA tab
The flow therefore is :
MSForms Excel download - Rawdata.xlsx - Analysis.xlsx Data Tab - Analysis.xlsx to other tabs
Thanks Hugh
Hewtee Not sure if I fully comprehend your issue, but I'll give a try.
When you load data from a folder, you first need to filter the file names that contain e.g. "RawData". That will then give you a list of files like you describe, but with different suffixes. I assume that the latest downloaded file also has the most recent time stamp of them all. Then you can filter the most recent file from the "Date created" column (Date/time Filter, Is Latest), and then press the two downward arrows in the Binary column. A few helper query and a RawData (name of the folder I used to test this) query are created. The latter contains the data from the most recent file. Do your transformations/filtering and close and load back to an Excel sheet to form the basis of your other sheets.
Now, add a more recent data file to the designated folder, making sure the file name contains "RawData" (or whatever you filtered on in the beginning). Go to the Excel table you loaded earlier, right-click in it, choose Refresh, and the most recent data will be loaded.
10 Replies
Hewtee Yes, please share your workbooks. It's difficult to visualise what you are dealing with, otherwise. Sounds like a Power Query solution should be able to work for you, since you indicate that you download raw data repeatedly in one particular folder.
Riny_van_Eekelen Thank you, I will send them now 🙂
Hewtee Not sure if I fully comprehend your issue, but I'll give a try.
When you load data from a folder, you first need to filter the file names that contain e.g. "RawData". That will then give you a list of files like you describe, but with different suffixes. I assume that the latest downloaded file also has the most recent time stamp of them all. Then you can filter the most recent file from the "Date created" column (Date/time Filter, Is Latest), and then press the two downward arrows in the Binary column. A few helper query and a RawData (name of the folder I used to test this) query are created. The latter contains the data from the most recent file. Do your transformations/filtering and close and load back to an Excel sheet to form the basis of your other sheets.
Now, add a more recent data file to the designated folder, making sure the file name contains "RawData" (or whatever you filtered on in the beginning). Go to the Excel table you loaded earlier, right-click in it, choose Refresh, and the most recent data will be loaded.
