By taking this part of your question : "Tableau prep is not taking the new files automatically"
I believe you need to do a wildcard union on your flow as you need to keep adding new files into to it as they are generated daily.
For that you need only 1 data connection to one of your Excel. In that connection you will need to setup the Multiple Files and select Wildcard union.
That should allow you to keep adding files to your union.
I hope this helps