Updating excel file from another excel file

[coltan123]

Hi, I want to know if it is possible to update an excel file from another excel file through vba.
For example, I have a master excel file database, containing data for March 2019, April 2019, May 2019.
Then, I received a file that is June 2019.
I want to able to go to the master excel file and auto update June data in.
Is this possible?

[alansidman]

It is possible. If you wish additional help, then read the yellow banner at the top of this page on how to attach a file.

[coltan123]

Ok sure, I will create a new post. Thank you

[alansidman]

Do not create a new post. Edit this one. If you create a new post, it will be closed as a duplicate.

I have merged your threads. Do not duplicate requests for the same issue. If you have new information, just add it to the thread.

[coltan123]

Hi, I want to automatically pull in new report data into one excel masterfile. I receive monthly report and I want to update it to my main excel database. I try using power query to do it , but unable to do it because my monthly report are in an inconsistent format as shown in the pictures. However, the labeling and names are more or less consistent .
ExcelExample3.png
ExcelExample4.png
I am planning to extract the data as shown in the picture into a masterfile. The objective will be that when I recieve a monthly report , all I need to do is to put it in a folder , go to master database and press refresh and it will automatically update it.
Since the labeling and column names are consistent, I am thinking if there is such thing as a vlookup function in power query that allow me to extract the data.
Any suggestion is welcome . Thank you very much.
Attached is some sample files

[jindon]

mar19.xlsx, Apr19.xlsx must be in one folder
MainDatabase should not be in the same folder above.
Verify the value, some are different from yours.

Please Login or Register  to view this content.

[coltan123]

This is amazing work. Could I sent you a private message to understand the code better? I am a beginner VBA user and would like to update this code in the future. Thank you.

[jindon]

One line in the code has been replaced.

You can continue here as long as it relates to your original question.

Asking question via PM is not allowed by the forum rule.

[coltan123]

Hi, I have some question listed below.
1) if my monthly data happen to have a new product,it wont register the new product into the database. I want to be able to add the new product into the database, refresh it and extract the data for that product.
2)Also, for the monthly data, if the "gross sales" column is a different position, it doesnt work.
I want something to be more dynamic like what I describe in the photo below.
Excelexample5.png
excelexample7.png

3)Also, may I know which part of the code do I adjust, such that it does not take the whole file name in the database. For example, if my monthly data file is called
"ABC_Mar19", I only want "Mar19" in my database, not the full name.
Appreciate your help and advice regarding this. Thank you so much.

[dangelor]

Possible solution...

Please Login or Register  to view this content.

[jindon]

Your new date headings are different.
It was like "Jun-19" and serial date.
If it changes like that, I don't think I can code it.