VBAで、同一フォルダ内にある複数のExcelブックから、検索、特定のセルの文字列を別ファイルの特定

Question

VBAで、同一フォルダ内にある複数のExcelブックから、検索、特定のセルの文字列を別ファイルの特定の場所へコピーしたいです。

VBA初心者です。
色々試した結果頭がこんがらがって来たので、質問させて下さい。
個人情報の入っている複数のExcelブックがあり、その中からお客様番号の検索をかけ、完全に一致する方を探し出した上で、その方の住所、電話番号、などいくつかだけを一つの別のファイルの特定のシートに反映させたいのですが、こういったことは可能でしょうか？

いっぺんに作業を行うのではなく、お客様が来られたら検索をかけて、反映させる、という形になっています。本来だったら、自分でもっと勉強すべきなのは重々承知しておりますが、ご教授頂けると助かります。

今まで試したのは、"複数のブックから検索する方法""特定のセルを抽出"でした。検索をかける事は出来たのですが、別のブックから目的のブックへの反映が上手くいきません。
よろしくお願い致します。

WindFaller · Accepted Answer

エクセルですと、ODBCがありますが、 複数のデータですと勝手が悪いので、ADODBで、検索システムを作ってしまえばよいのではないでしょうが、複数でも、それほど手間は掛からないでしょう。論より証拠です。

私自身、相当長い間、手を付けたことがなかったので、私自身のためにやってみることにしました。
https://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html
(マクロの目的は郵便番号検索ですが、それが見つからなかった時に、事業所の郵便番号を探します。あくまでもサンプルです。実務では、この場合は、Web APIを使ったほうが楽です)

何かおかしな所があるかもしれませんが、一応、思ったように検索結果が出ました。

CSVをダウンロードして、
ファイル名
"ZipCode2019.xlsx" ; 一般の郵便番号
"JIGYOSYO2019.xlsx" ; 事業所の郵便番号
基本的には、タイトル行は2つが同じでないと、片方がエラーが出ます。
シート名：zip_all
タイトル行には
　郵便番号,都道府県, 住所
参照設定：Microsoft ActiveX Data Objects 2.7 Library (Versionは任意)
GetNewQueryの中のコメントアウト部分は、単独で検索した時の痕跡です。

'//
Dim Findflg As Boolean
Sub Main()
　Dim DBFname As String
　Dim SearchKeyWord As String
　Dim fPath As String
　Findflg = False
　SearchKeyWord = Application.InputBox("検索用の郵便番号を入れてください。", Type:=2)
　If SearchKeyWord = "False" Then Exit Sub
　If Not IsNumeric(SearchKeyWord) Then Exit Sub
　SearchKeyWord = Replace(SearchKeyWord, "-", "", , , vbTextCompare)
　 'データベースのソースの場所
　fPath = "D:\ZipCode\" 　'パス
　Call GetNewQuery(fPath & "ZipCode2019.xlsx", SearchKeyWord)
　If Findflg = False Then
　　Call GetNewQuery(fPath & "JIGYOSYO2019.xlsx", SearchKeyWord)
　End If
End Sub

Sub GetNewQuery(DBFname As String, SearchKeyWord As String)
　Dim myCon As ADODB.Connection　　'要参照設定
　Dim myConStr As String
　Dim mySQL As String
　Dim myField As ADODB.Field
　Set myCon = New ADODB.Connection　　'要参照設定
　'Dim DBFname As String
　Dim i As Long
　'**************************
　'ファイル名
　'DBFname = ""
　If Dir(DBFname) = "" Then
　　MsgBox "ファイルが見つかりません。"
　　Exit Sub
　End If
 
　'**************************
　myConStr = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
　　"Data Source=" & DBFname & ";" & _
　　"Extended Properties=""Excel 8.0;HDR=YES"""
　'************************
　'SQL
　mySQL = "SELECT 郵便番号,都道府県,住所 FROM `zip_all$`" & _
　　"WHERE 郵便番号 LIKE '" & SearchKeyWord & "%';"　
　　'シングルクォート(')とアクサン(`)の違いに気をつけること。
　'************************
　On Error GoTo ErrHandler
　myCon.Open myConStr
　On Error GoTo ConClose
　With New ADODB.Recordset
　　.Open mySQL, myCon
　　On Error GoTo RsClose
　　While Not .EOF
　　　For Each myField In .Fields
　　　　If i Mod 3 = 0 Then
　　　　　Cells(Int(i / 3) + 1, 1).Value = myField.Value
　　　　ElseIf i Mod 3 = 1 Then
　　　　　Cells(Int(i / 3) + 1, 2).Value = myField.Value
　　　　ElseIf i Mod 3 = 2 Then
　　　　　Cells(Int(i / 3) + 1, 3).Value = myField.Value
　　　　End If
　　　　i = i + 1
　　　　Findflg = True
　　　Next
　　　.MoveNext
　　Wend
　　If Findflg Then Beep
RsClose:
　　.Close
　End With
ConClose:
　myCon.Close
　Set myCon = Nothing
　' If Err().Number = 0 Then Exit Sub
ErrHandler:
　' MsgBox Err().Number & vbCrLf & Err().Description, vbExclamation
　Set myField = Nothing
End Sub

以下はデータベースとしてのエクセルのレイアウト(タイトル行に注意)

fujillin · Answer

No1です。

＞ひとつのブックにしてしまうと開かなくなるんです
それだけのデータ量を複数ブックで処理しようとすると、時間がかかってしまう可能性が大です。

＞お客様が来られたら検索をかけて、反映させる～
どれだけの速度を必要とするのかわかりませんが、ほぼリアルタイムに結果が欲しいような状態であるならなおさらです。

ひとつのブックでは開けないほどのデータ量になっているのなら、もはやエクセルの検索で扱う範囲を超えているように思われます。
データベースソフト（アクセスなど）を利用なさった方が良いのではないでしょうか。

fujillin · Answer

こんにちは

疑問なのは、なぜ複数のブックなのか。（処理上では別に問題にはなりませんが）
顧客リストから検索するようなイメージなのでしょうけれど、データベース的に利用するのならブックがバラバラに存在すること自体が合理的には思えません。

＞こういったことは可能でしょうか？
十分に可能でしょう。
ヒットする情報が一つなのか複数なのかによって若干処理は変わりますが、基本的には
　1）ブック内を検索して
　2）見つかったらデータを特定シートにコピペ
　3）見つからない場合は次の処理（または終了）
という流れの処理コードを作成できればよいと思われます。
複数のブックを対象とする場合は、上記をループしながら各ブックを順に検索してゆけば良いはずです。

まずは、対象ブックを一つとして作成してみて、うまくいったら、それをループさせるか、あるいは、サブルーチン化しておいて、ブック名を引数にして繰り返し呼び出すような考えにする方がわかりやすいかもしれません。

＞別のブックから目的のブックへの反映が上手くいきません。
セルの値のコピペと同様の処理でできるはずです。
元のセルに関数式が設定されているような場合は、単純にコピペすると（数式がコピーされるので）ペースト先のセルに表示される値がおかしなことになる場合があります。
そのような場合は、「値をペースト」するようにすればよろしいかと。
または、
　book1.sheet1.range1.Value = book2.sheet2.range2.Value
のように値を直接代入する方法をとれば、結果的に、値がコピーされることになります。

VBAで、同一フォルダ内にある複数のExcelブックから、検索、特定のセルの文字列を別ファイルの特定

エクセルですと、ODBCがありますが、 複数のデータですと勝手が悪いので、ADODBで、検索システムを作ってしまえばよいのではないでしょうが、複数でも、それほど手間は掛からないでしょう。

No1です。

こんにちは

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

エクセルですと、ODBCがありますが、複数のデータですと勝手が悪いので、ADODBで、検索システムを作ってしまえばよいのではないでしょうが、複数でも、それほど手間は掛からないでしょう。