Convert all four files to .csv and keep in a single folder and Retry UNION.
What version of Tableau are you using ?
You could use Prep to do this before opening in Tableau.
Sheet level data should be union.which error you are facing?.
when you drag sheet into drag n drop area in tableau home page or plate. You can override all sheets into sheet 1 all sheets are automatically union to each other.
Amar D. Savale
You can not union data from 2 different data sources in Tableau, In your case 2 different excel can not be unioned. you can either merge them in separate sheet of 1 excel or you can use Tableau Prep for unioning the data.