' MatchData.vbs ' Usage : CScript MatchData.vbs Const strFileName1 = "D:\home\edu\hatena\20040915\db1.xls" ' table1 として利用 Const strFileName2 = "D:\home\edu\hatena\20040915\db2.xls" ' table2 として利用 Const strFileName3 = "D:\home\edu\hatena\20040915\db3.xls" ' table3 として利用 MatchData Sub MatchData Dim cn Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName3 & ";Extended Properties=Excel 8.0" Dim strSQL strSQL = "INSERT INTO [Sheet1$]" & _ " SELECT t2.c1, t2.c2, t2.c3, t2.c4, t2.c5" & _ " FROM [Excel 8.0;database=" & strFileName1 & "].[Sheet1$] AS t1" & _ " INNER JOIN [Excel 8.0;database=" & strFileName2 & "].[Sheet1$] AS t2" & _ " ON (t1.c3 = t2.c3) AND (t1.c2 = t2.c2) AND (t1.c1 = t2.c1)" cn.Execute strSQL End Sub
[table1] c1 c2 c3 c4 c5 あ い う ※ △ か き く ○ ◆ さ し す × ◇ [table2] c1 c2 c3 c4 c5 あ い う × ○ か き く を ■ さ し す ヾ 〒 た ち つ ‖ 〆 な に ぬ 仝 ± [table3] c1 c2 c3 c4 c5 [結果] c1 c2 c3 c4 c5 あ い う × ○ か き く を ■ さ し す ヾ 〒 INSERT INTO table3 SELECT table2.c1, table2.c2, table2.c3, table2.c4, table2.c5 FROM table1 INNER JOIN table2 ON (table1.c3 = table2.c3) AND (table1.c2 = table2.c2) AND (table1.c1 = table2.c1);