G
guy
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.xls"
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
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.xls"
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