EXCEL でマクロを使用してのデータ統合方法について

質問

現在マクロを利用して２つの "ブック" の sheet1 に入力されているデータを統合しようとしています。
各ブックには以下のようなデータが入力されています。
book A [名前、住所、連絡先、名前ID]
book B [名前ID、所属部署、所属長]
これを、名前ID をキーとして、新規 book の sheet1 に統合したいと思っています。
新規 book [名前、住所、連絡先、名前ID、所属部署、所属長]

現在、ADO を使用して２つのファイルの統合までは完了したのですが、データが以下のように入ってしまっています。。

[名前、住所、連絡先、名前ID、所属部署、所属長]
aaa bbb ccc ddd --- --- ---
--- --- --- ddd aaa bbb ccc
*--- は、空セル

これをきっちり同列に統合したいのですが、有用なマクロサンプルはございませんでしょうか？もしくは、参考 URL など御座いましたらご教授願えますと助かります。

回答No.5

imogasi · ベストアンサー

bookAでは名前,book Bでは名前IDと名称が変わっているが、同じ氏名コードや生徒・学生番号のような、ユニークなコードですか。一方は
漢字氏名ではないでしょうね。そこが大事と思う。
同じコード体系のコードをそれぞれのファイルが持つなら
（１）アクセスで氏名IDでファイルを結合
（２）エクセルで、両ファイルを名前IDでソートし、バッチ作業の定石、ファイルレコード・マッチングのロジックで（エクセルVBAのプログラムを組んで）結合できます。
アクセスVBAやエクセルVBAの質問者の経験は？
＞これをきっちり同列に統合したいのですが
列というのはエクセルの概念で、シートの同列に並べるというよりも
統合しないといけない。
エクセルで、関数だとシートが式だらけになるが、VLOOKUP関数でも出来るのでは。レコード数の多少が心配ですが。VLOOKUPはVBAコードの中でも使えるのをご存知かな（VBAで使えば関数式はセルに設定されない）。
ーー
質問に概数の量を書かないのも不十分。
方法の選択にも影響する。
＞データが以下のように入ってしまっています。。
ADOの性ではないでしょう。やり方（コードの書き方）が不適当でしょう。この点ならその点に絞って質問したら。

補足

回答ありがとうございます。VLOOKUP ですよね。
ちょっと考えてみます。

概数は、３００件程度です。統合に掛かる時間は問われていません。
もともとは、名前ID が双方のデータで共通なので、それをキーとして並べ替えて、お互いに一致したものをコピペするというので事足りる話なのですが、どうもマクロにして一発でやるというのに作業の依頼者が拘っておられるので、分からない話になってしまいました。ご指摘ありがとうございました。

VBA経験は無いです。ADO のサンプルは、同じような質問をされていたほかの方の、以下のコードを検証に使いました。データフォルダにある book の内容ををひたすら INSERT していくという内容で、動かしたような動作になっています。。INSERT の部分を UPDATE とかにして、何とかしたいと思ったんですが、いかんせん組み方がうまく思いつきません。申し訳ないですが、何かお知恵をお貸しいただけると幸いです。

Sub Marge()
Dim cn
Dim rs
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & XLS & ";Extended Properties=Excel 8.0"
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
Dim folder
Set folder = fs.GetFolder(DIR)
Dim file
For Each file In folder.Files
cn.Execute "INSERT INTO [Sheet1$] SELECT * FROM [Excel 8.0;database=" & file & "].[Sheet1$]"
Next

回答No.6

n-jun

私なら双方に重複する”名前ID”をキーとしてDictionaryオブジェクトを使いますが、
”ＡＤＯ”が必須条件になっているのであればしょうがないですけど。

回答No.4

mitarashi

#1です。
>意図するところは、マクロを一回動作させれば one step で作業が官僚できるということにあります。
一度作成してしまえば、統合したデータの表上で右クリックして、「データの更新」を行えば、一発で最新化できますよ。
なんでしたら、こんなマクロにも出来ます。
Sub test()
Range("A2").QueryTable.Refresh BackgroundQuery:=False
End Sub
いずれにしてもお気に召さないと思いますので、現行のコードを開示されてはいかがでしょうか。

お礼

回答ありがとうございます。

もともとのデータは非常に単純で、双方のデータの 名前 ID には規則性があるので、お互いに出力されたデータを並べ替えてコピペしてしまえば終わるような内容なのですが、できるだけ何も触りたくないというマネジメント層のわがままからこのような形での作業を考えてます。おそらく、wizard を使用するならば、マネジメント層に wizard を使って作業してくれといわなければならないという政治的な話があります。

いろいろありがとうございます

回答No.3

hallo-2007

マクロ使わない方法ですが、私なら・・・
１、book A　の4列をコピィ　新規book　のSheet1に貼り付け
２、book B　の3列をコピィ　新規book　のSheet2に貼り付け
３、新規bookのSheet1の5列目より左にVLOOKUP関数　
=VLOOKUP(D2,Sheet2!A:C,2,FALSE) とか入れておきます。
マクロにする部分は１と２の部分位で宜しいのでは。

お礼

回答ありがとう御座います。
マクロが必須条件となっており、私も頭を抱えております。できれば、ご提示いただいたような方法でやるのがスマートだとは思っているのですが・・。

回答No.2

mitarashi

#1です。推敲が甘くて申し訳ありません（本日２件目．．．）
注意
１．ウィザードからは、ひとつのワークブックしか設定できないようなので、クエリのビューを表示した後で、テーブルの追加で別のワークブックを追加する必要があります。
の間違いです。読み替えてください。

お礼

回答ありがとう御座います。

実は、データ統合方法にマクロを使用しなければいけないというのが必須条件となっており、頭を抱えております。説明不足で申し訳ありませんでした。
意図するところは、マクロを一回動作させれば one step で作業が官僚できるということにあります。

回答No.1

mitarashi

データ／新しいデータベースクエリでやれば下記の様なSQLでできます。ADOでも二つのワークシートに接続して、同様にクエリが出来そうに思います。現在のコードを呈示されれば、識者のアドバイスが得られるでしょう。
SELECT `Sheet1$`.名前, `Sheet1$`.住所, `Sheet1$`.連絡先, `Sheet1$`.名前ID, `Sheet1$_1`.所属部署, `Sheet1$_1`.所属長
FROM `C:\Documents and Settings\?????\bookA`.`Sheet1$` `Sheet1$`, `C:\Documents and Settings\?????\bookB`.`Sheet1$` `Sheet1$_1`
WHERE `Sheet1$`.名前ID = `Sheet1$_1`.名前ID
注意
１．ウィザードからは、ひとつのワークシートしか設定できないようなので、クエリのビューを表示した後で、テーブルの追加で別のワークシートを追加する必要があります。このとき、ドロップダウンの幅が狭く、パスが長いとファイル名が確認できないという素晴らしさ．．．
２．テーブル追加後、名前IDでの結合は、フィールド名のD&Dで出来ますが、追加したワークシートのテーブルの他のフィールドは表示されないので、SQLアイコンでSQL表示にして、追加してやる必要がある様です。（機能を見つけてないだけかもしれません）
なお、Sheet1$_1は、msqueryが異なるワークシートのフィールドを区別するため、_1と自動的に付与したものです。