It's a long while since I used SQL into workbooks, so someone else might
have information readily to hand.

However, IIRC your SQL needs to refer to a workbook  file. I.e. it won't
look at information in a workbook that's open in RAM.

It is likely that the SQL would be able to refer to the copy of the workbook
that is on disk while you are running the code  from the copy of the workbook
that is in memory, but there is always the potential for these to be out of
step, so that's a real risk.

If you want to use SQL, then my thought is that you should use a second
workbook to access the one with the data  in it. If you want to stay inside
one workbook, then don't use SQL - simply use code and/or formulas.

You can do your union with a subroutine that is simply given a worksheet
name in its call and goes and gets the information from that sheet  and
appends it to your result sheet. Call this as many times as there are
sheets - from a master subroutine or button event handler. Once they
finish, sort the information as needed.

If your union is working on differently-formatted "tables", then you'd
probably have one subroutine for each "select" in the SQL and call them
appropriately from the master.

Note that I also seem to recall that SQL access to Excel is not very good at
referring separately to multiple worksheets in the one workbook. But it's a
hazy recollection and possibly even obsolete.

In essence, if it's Excel reading Excel, use code, not SQL. If it's Excel
reading Access (or any other DB), use SQL. If it's Access reading Excel,
use SQL in preference to code.