I'm not aware of any Tableau specific limitation because Tableau simply passes the custom SQL to the underlying data source. At that point, custom sql may run into issues based on the underlying data source's limitations. This Microsoft documention outlines the limitations for MS Excel. Excel specifications and limits - Excel Does this help to explain the issues you are experiencing?
Why are you using the legacy Excel connector and custom SQL at all? The native connector and the union feature will do this for you with fewer problems.
This was just an example. I needed to select some specific fields from a large spreadsheet.