query (join) external wokbooks from a macro

Question

I got 3 Excel workbooks:

• the macro (will be an add-in when finished)
• the previous period data list
• the current period data list
  Both lists are less than 1000 rows.

I am trying to make an SQL join between those lists but I can't get it working. Since the sheets names have dots in them (like "TB 03.18"), I used the trick -found somewhere on SO- to define named ranges in the respective workbooks.

Below is the code with the information of the 2 SQL that work and the one that gives an error. I am lost.

'set connection to 'current tb'
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & wbCurr.FullName & _
        ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set con = New ADODB.Connection
con.Open sConn

wbCurr.Names.Add Name:="cur", RefersTo:=shCurr.Range("A4").CurrentRegion    'to be made dynamic !!!
wbPrev.Names.Add Name:="pre", RefersTo:=shPrev.Range("A9").CurrentRegion    'to be made dynamic !!!

lastRow = shPrev.Range("a15000").End(xlUp).Row
shCurr.Range("F5:I15000").Clear
Dim xl12 As String: xl12 = "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='HDR=YES';] "


'this works ------------------
'sSql = "select reference from  cur " & vbCrLf & _
       "where cur.reference like '1%'"

'this works ------------------
sSql = "select pre.reference  " & vbCrLf & _
       "from pre IN '" & wbPrev.FullName & "' " & xl12 & vbCrLf & _
       "where pre.reference like '1%'"

'not working: syntax error in FROM clause
'sSql = "select cur.reference from  cur " & vbCrLf & _
       "inner join pre IN '" & wbPrev.FullName & "' " & xl12 & " on cur.reference = pre.reference " & vbCrLf & _
       "where cur.reference like '1%'"

Debug.Print Now, sSql
Set rs = con.Execute(sSql)

Answer 1

With the Jet/ACE SQL engine, you can escape special characters in identifiers with square brackets or backticks. Hence, the period should not be an issue and you can then use full sheet names and A1 range notation for clarity in your SQL query instead of named ranges.

However, your specific SQL error is due to using IN inside an INNER JOIN clause. Avoid IN which is best used for one table and use the period qualifier syntax that passes workbook path as parameter within the inline call where even Provider is not needed:

FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Workbook].[Worksheet$] AS w

Altogether, consider the following adjustment without named ranges which are tough to debug. Below shows how periods can be handled with brackets or backticks and how to reference internal and external worksheets using A1 notation ranges.

strSQL = "SELECT s1.[reference]" & _ 
         " FROM [S.h.e.e.t1$A4:Z100] s1" & _
         " INNER JOIN [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Workbook].`S.h.e.e.t.2$A9:Z100` s2 
              ON s1.[reference] = s2.[reference]" & _
         " WHERE s1.reference LIKE '1%'"

rs.Open strSQL, con