Have you explored wildcard unions in Tableau 10.1? You can use them to union several files/sheets that match a certain name convention. If file's names and structure are consistent, this functionality should help!
Here's how Wildcard Union works: https://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#union.html#union_wildcard
And here's a great blog that explains it from a business case perspective: https://tableauandbehold.com/2016/11/15/using-wildcard-pattern-based-union-in-tableau-10-1-instead-of-wdc/#more-3431
Hope this helps!
Thanks for the reply. I browse thru the first one quickly before. I will look at it again.
My company is using version 10. Does wildcard union work for version 10?
No worries. Wildcard union was first available on 10.1, so you won't see it in 10, unfortunately. If an upgrade is not in the near future, you could automate this workflow using the Tableau Data Extract Command-Line Utility
This other thread tackles a very similar situation with that utility: Is it possible to include dynamic list of Excel files as data sources? If now, what would you do to include data from it?