join/union tables by querytable
 

Can querytable allow inner/left/right join or union table?
i got error msg code "1004" at the last line "varQry5.Refresh"...
my excel workbook has 3 sheets: table1, table2 and combine1.
actually i need to compare the 2 tables (sheets "table1" and "table2") cell
by cell, by first mapping the concatenated field NAME&AGE...
could anyone pls advise...?
is there any better way to do in Excel?
Thank you very much!!

--------------------------------------------------------------------------------
Sub test111()

Dim varConn5 As String, varSql5 As String
Dim varQry5 As QueryTable

varConn5 = "ODBC;DefaultDir=C:\testing;driver={Microsoft Excel Driver
(*.xls)};DriverId=790;dbq=C:\testing\test_table.xl s"

varSql5 = "SELECT [table1$].[NAME&AGE] as T1_KEY, [table2$].[NAME&AGE] as
T2_KEY from [table1$] join [table2$] on
[table1$].[NAME&AGE]=[table2$].[NAME&AGE]"

Set varQry5 = Worksheets("combine1").QueryTables.Add(Connection: =varConn5,
Destination:=Worksheets("combine1").Range("a1"), Sql:=varSql5)

Worksheets("combine1").Range("A:IV").ClearContents

varQry5.BackgroundQuery = False
varQry5.Refresh

End Sub