Use mid and inner join SQL query in VBA excel

Please find the below table where i want to execute mid and inner join SQL query.

table 1:-

table 2:-

Please find my below syntax and the respective error’s which i am facing on the same.

1st sql query syntax

sql_string = "SELECT [Sheet1$].[Sr], [Sheet2$].[Value], [Name] FROM" & _
" [Sheet2$] INNER JOIN [Sheet1$] ON" & _
" [Sheet1$].[Sr]=(SELECT mid([Sheet2$].[Name], INSTR([Sheet2$].[Name],""#""), len([Sheet2$].[Name])) FROM [Sheet2$]);"

error on above code.

2nd sql query syntax

sql_string = "SELECT [Sheet1$].[Sr], [Sheet2$].[Value], [Name] FROM " & _
"(SELECT mid([Sheet2$].[Name], INSTR([Sheet2$].[Name],""#""), len([Sheet2$].[Name]))KeyValue FROM [Sheet2$]) as [Sheet2$]" & _
" [Sheet2$] INNER JOIN [Sheet1$] ON [Sheet1$].[Sr]=[Sheet2$].KeyValue;"

error on above code.

Please guide me on the same.

Please find the expected output.

Answer

Use the below query

Select [Sheet1$].[Sr], [Sheet2$].[Name], [Sheet2$].[Value] From [Sheet1$] Inner Join [Sheet2$] On CInt([Sheet1$].[Sr]) = CInt(Mid([Sheet2$].[Name],InStr(1,[Sheet2$].[Name],""#"")+1))

Sample Code

Dim objCN, objRS

Set objCN = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.RecordSet")

On Error Resume Next
objCN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:Userspankaj.jajuDesktopBook1.xlsx';Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"
objRS.Open "Select [Sheet1$].[Sr], [Sheet2$].[Name], [Sheet2$].[Value] From [Sheet1$] Inner Join [Sheet2$] On CInt([Sheet1$].[Sr]) = CInt(Mid([Sheet2$].[Name],InStr(1,[Sheet2$].[Name],""#"")+1))", objCN, 3, 1, 1

If Err.Number <> 0 Then
    MsgBox Err.Number & ": " & Err.Description
Else
    On Error GoTo 0
    Sheet3.Range("A1").CopyFromRecordset objRS
End If

objRS.Close: Set objRS = Nothing: Set objCN = Nothing