Updating existing sheet with new data from another workbook

[Hewtee]

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  

[#2]

@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.

[#3]

@Riny_van_Eekelen  Thank you, I will send them now

[#4 · Accepted solution]

@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. 

[#5]

@Riny_van_Eekelen  Wow thank you. Ill follow your suggestions and let you know

[#6]

@Riny_van_Eekelen  More' meneer (my attempt as I speak Afrikaans:)

Ok I have got everything working thanks to you, however I selected combine files instead of merge. Is there a way to change the existing query or do I have to start again?

Thanks Hugh

[#7]

@Hewtee Again, not sure I follow, but if you intend to combine all the files (within a folder/sub-folder) that contain "RawData" in the file name, just leave out the step that filters for the most recent file. Then all files in the folder that match your criteria should be picked-up and "joined" into one query automatically. Add a newer file, refresh and its data will be added to the output. No need to create a new query.

[#8]

@Riny_van_Eekelen  Thanks again, will try

[#9]

@Riny_van_Eekelen  Morning.

Ok I see what the problem is. 

Lets say my first Workbook that I load has 10 rows. The next one (later date) has 14.

What the query does is add all the data into 24 rows and so its duplicating the common data from both workbooks. What I want is it to just "add" the new data.

I have attached the file and a screenshot of power editor.

I an concerned that I am abusing your kindness, if so I will post this question in the forum?

[#10]

@Hewtee No abuse at all. Looked at the xlsx file you uploaded, but see no query in it. Only a connection to a file "RankingsRawData(1-17).xlsx" on your Google drive. And, it's quite difficult to diagnose a query from a picture. Perhaps the ID column could be helpful to identify and delete duplicates. Or I just don't get a clear picture in my mind of what you want/need.

[#11]

@Riny_van_Eekelen  Ok thank you.

Yes I also noticed no query!!
I've attached my original workbook I was working on, but this one has no connections only queries

You will see that the query returns data from two other workbooks but duplicates the data.

(I'm not sure if I mentioned but I get my data from MSForms and then download it as an Excel file, which contains all the previous data plus any new inputs to the form.) 

My query has a filter in it that picks up any spreadsheet from MSForms with a name starting with GrowthPredictorRankings.... (as the suffix changes in each download from MSForms 

So my main problem remains the duplication of the data  Cheers Hugh