I have two excel files I need to merge. Essentially, this is one step in a larger process. I have a master workbook that pulls in data from several smaller workbooks and manipulates/formats the data for monthly reporting.

While I have built several SQL queries before, this one has remained defiant. I will attach my code below

` Set file`
meta_file = "C:\Path\File.xlsm"

site_file = "C:\Path\OtherFile.xlsx"


` Create the connection string`
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
               & "Data Source=" & meta_file & ";" _
               & "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"



` Populate Data`
` SQL`
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

sql = "SELECT a.[Col NameID], a.[Col Name2], a.[Col Name3] " & _
"FROM ([Report$] a " & _
"LEFT JOIN " & _
"(SELECT * FROM [Excel 12.0 Xml;HDR=No;" & _
"Data Source=" & site_file & ";Readonly=FALSE].[Results$]) b " & _
"ON a.[Col NameID] = b.[F1])"
    
` Open the connection and execute`
conn.Open sConnString
Set rs = conn.Execute(sql)

` Lets pull it`
Worksheets("Data").Activate

ActiveSheet.Range("A4").CopyFromRecordset rs
rs.Close
conn.Close

After which, I receive this error at the "conn.Open sConnString" step.

Cannot Find Installable ISAM

For additional context, neither file is read-only. I was able to use SQL to successfully pull data from both files separately, it is in the process of joining that I find this issue. Note that I must use HDR=No for the second workbook due to formatting issues (There is an annoying logo at the top and everything is shifted down 5 rows) and I'd like to work around it without modifying the raw files. Changing "Database" and "Data Source" does not make a difference either.