Help HOW-TO VBA/SQL doing LEFT JOIN from local named table to a database ADODB table

I regularly run queries inside excel VBA.

What I do is I store the SQL code in a String variable 'sqlStr', and then I use that string and pass it to a function which runs SQL Code by opening a connection with ADODB, connecting to a 'serverName' and 'databaseName'...

there are hundreds of tables inside of 'databaseName' and I can easily do things like

FROM [databaseName].[customerTable]
LEFT JOIN [databaseName].[purchHistoryTable]
ON [databaseName].[customerTable].[orderNumber] = [databaseName].[purchHistoryTable].[orderNumber]
etc, etc, etc, and then the whole sql code is stored in a string and passed to the other function to run the query and data dump the results in some empty excel sheet.

and this all works fine until.............. I try to do the following:

Lets say i have an excel sheet called Sheet1, and inside that sheet there is, what I like to call, a temporary table which we'll just assign a range to... "Sheet1!A1:L500"

I have stored the range inside a string variable called rangeTable to reference the local table inside the workbook, and then I try to do a LEFT JOIN on rangeTable

the headers on rangeTable are clearly titled, so i try to link a LEFT JOIN using (for example, lets say one of the column header titles is orderNumber), then I might try and do a LEFT JOIN like this

FROM [rangeTable]
LEFT JOIN [databaseName].[purchHistoryTable]
ON [rangeTable].[orderNumber] = [databaseName].[purchHistoryTable].[orderNumber]

However, this FAILS !

Run-Time error '-2147217865 (80040e37): Invalid Object name 'rangeTable'


?????????????????????

If anyone can help, please help me to do a LEFT JOIN, from a local workbook sheet range, TO a database table field.

If anyone can please help, I would really really appreciate it !