Sounds like what you need to do is place each table as a separate sheet in the same excel file. Then when you make the connection in Tableau you need to use a custom SQL function to write a union (cf. joins) between the two tables. The article linked below to the Tableau Knowledge Base does a good job of breaking the steps and the SQL language down.
Using this method when you wanted to update the data in Tableau, you would then replace the sheet in Excel and tell Tableau to refresh the data.
Awesome, I was able to combine the 2 sheets via custom SQL as you suggested.
But its not ideal, If I was using large amounts of data excel couldn't handle it, or combining archived data with new data.
For all I know it may not be possible, but if anyone has any ideas please let me know.