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
x
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.
Advertisement
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