二つのシートで同一IDのものをまとめる方法2

質問

• エクセルで作成された二つの製品表を一つにする作業にてこずっています。
• 製品表１には、同一のＩＤが二つ以上存在しません。
• 製品表２には、同一のＩＤが１～３存在します。

回答No.2

OrangeCup150

初歩的なことですが

Dim ID1, ID2, Code, EAN, VL, PU As String

ではなく、

Dim ID1 As String, ID2 As String, Code As String, EAN As String, VL As String, PU As String

と書きます。 ID1, ID2, Code, EAN, VL は型指定を省略したとみなされ Variant 型になります。
次に、

Dim row1, row2, c1, c2 As Integer

ではなく、

Dim row1 As Long, row2 As Long, c1 As Long, c2 As Long

と書きます。 Integer 型は -32768 から 32767 の範囲しか表現できません。つまり、 32767 行までしか処理できません。
Long 型を使用して下さい。 -2147483648 から 2147483647 の範囲を表現できるのですべての行を処理できます。
要約すると VB は Pascal ではありません。 VB の言語仕様（わりとひどい。といっても古い言語で古いＰＣ（１６ビット時代）との互換性だからしかたない）を確認して下さい。（とは言っても、公式資料も何年も修正されていない間違いがあるので注意が必要なんですが・・・。まあ、マイクロソフトが .NET を押しているからしかたないですね。 「VBA なんて使わず .NET 使ってね」な感じですからね。）

あと、用語が一般的でないのが気になりますが・・・。
一般的には row = 行 で、段ではあまり数えません。 Word の場合には paragraph = 段 とするのが適当ですが・・・。

本題ですが Excel DB 状態なので、データ接続（ＳＱＬ）を使うのが一般的だと思います。
以下の手順の説明はＥｘｃｅｌ２００７です。

まず、製品表１（Items シート）に ITEM_TABLE と名前を定義します。
手順は、製品表１の範囲を選択して（列名を含めて選択します）、数式タブの定義された名前グループの名前の定義をクリックします。新しい名前ダイアログで名前欄に「ITEM_TABLE」と入力してＯＫで名前を定義します。
次に、製品表２（uom シート）に UOM_TABLE と名前を定義します。
手順は同様です。
ファイルを保存します。（仮に W:\Excel DB.xlsb とします。バイナリ形式です。）

データタブの外部データの取り込みグループのその他のデータソースをクリックします。データ接続ウィザードをクリックします。
「ODBC DSN」を選択し次へ、「Excel Files」を選択し次へ、このファイルを選択します。
次へ、完了。
データのインポートダイアログでプロパティをクリック
接続のプロパティダイアログで「使用」タブの「バックグラウンドで更新する」のチェックを外します。
「定義」タブの「コマンド文字列」にＳＱＬを指定します。

製品表１（ITEM_TABLE）は ID と NAME の２つの列を持ちます。以下、テストデータ
ID NAME
000128 product1
0KV502 product2

製品表２（UOM_TABLE）は ID, CODE, QTY, EAN, VL, WEIGHT, PU と PP の８つの列を持ちます。以下、テストデータ
ID CODE QTY EAN VL WEIGHT PU PP
000128 UNIT 000128 U QTY 000128 U EAN 000128 U VL 000128 U WEIGHT 000128 U PU 000128 U PP
000128 BOX 000128 B QTY 000128 B EAN 000128 B VL 000128 B WEIGHT 000128 B PU 000128 B PP
000128 PALLET 000128 P QTY 000128 P EAN 000128 P VL 000128 P WEIGHT 000128 P PU 000128 P PP
0KV502 UNIT 0KV502 U QTY 0KV502 U EAN 0KV502 U VL 0KV502 U WEIGHT 0KV502 U PU 0KV502 U PP
0KV502 PALLET 0KV502 P QTY 0KV502 P EAN 0KV502 P VL 0KV502 P WEIGHT 0KV502 P PU 0KV502 P PP

上記の構成に対して下のＳＱＬ文をコマンド文字列に指定します。

SELECT i.ID AS ITEM_ID, i.NAME AS ITEM_NAME,
MAX(IIF(u.CODE = 'UNIT', u.CODE, null)) As Unit_Code,
MAX(IIF(u.CODE = 'UNIT', u.EAN, null)) As Unit_EAN,
MAX(IIF(u.CODE = 'UNIT', u.VL, null)) As Unit_VL,
MAX(IIF(u.CODE = 'UNIT', u.PU, null)) As Unit_PU,
MAX(IIF(u.CODE = 'UNIT', u.QTY, null)) As Unit_QTY,
MAX(IIF(u.CODE = 'UNIT', u.WEIGHT, null)) As Unit_WEIGHT,
MAX(IIF(u.CODE = 'UNIT', u.PP, null)) As Unit_PP,
MAX(IIF(u.CODE = 'BOX', u.CODE, null)) As Box_Code,
MAX(IIF(u.CODE = 'BOX', u.EAN, null)) As Box_EAN,
MAX(IIF(u.CODE = 'BOX', u.VL, null)) As Box_VL,
MAX(IIF(u.CODE = 'BOX', u.PU, null)) As Box_PU,
MAX(IIF(u.CODE = 'BOX', u.QTY, null)) As Box_QTY,
MAX(IIF(u.CODE = 'BOX', u.WEIGHT, null)) As Box_WEIGHT,
MAX(IIF(u.CODE = 'BOX', u.PP, null)) As Box_PP,
MAX(IIF(u.CODE = 'PALLET', u.CODE, null)) As Pallet_Code,
MAX(IIF(u.CODE = 'PALLET', u.EAN, null)) As Pallet_EAN,
MAX(IIF(u.CODE = 'PALLET', u.VL, null)) As Pallet_VL,
MAX(IIF(u.CODE = 'PALLET', u.PU, null)) As Pallet_PU,
MAX(IIF(u.CODE = 'PALLET', u.QTY, null)) As Pallet_QTY,
MAX(IIF(u.CODE = 'PALLET', u.WEIGHT, null)) As Pallet_WEIGHT,
MAX(IIF(u.CODE = 'PALLET', u.PP, null)) As Pallet_PP
FROM `W:\Excel DB.xlsb`.ITEM_TABLE i, `W:\Excel DB.xlsb`.UOM_TABLE u
WHERE i.ID = u.ID
GROUP BY i.ID, i.NAME

「このブックの接続先は～」が表示されたら「はい」をクリックします。
出力先はとりあえず新規シートにでもしておいて下さい。
備考　ＸＭＬ形式よりもバイナリ形式の方がパフォーマンスがいいはずです。
ＳＱＬ文のファイルパスを指定（変更）する箇所が２カ所なので注意して下さい。

ここまで、解説しておいてなんですが、Ｅｘｃｅｌファイル（ＡＤＯ、ＯＤＢＣ）に対してＳＱＬ文を実行するのではなく大本のデータベースに対してＳＱＬ文を実行してＥｘｃｅｌにデータを返すようにしてください。
まあ、セキュリティの都合でＥｘｃｅｌファイルにダウンロードしたデータベースのデータしか操作できないなら別ですが・・・。