In legacy mode, sheet name of excel file should be followed by $ sign. For example if your sheet name is Sheet1 then sql query would be
"Select * from Sheet1$" instead of "Select * from Sheet1"'.
To confirm the exact sheet name follow the below steps.
1. Drag sheet directly into data pane area on data source page.
2. Go to Data Menu and Convert to Custom sql. Now Tableau will show you the exact sheet name to use in Custom sql.
Hope that helps.
Following up on this thread. If above solution solve your problem then Mark it correct to close the thread so that it will be removed from unanswered list.