VBAで、同一フォルダ内にある複数のExcelブックから、検索、特定のセルの文字列を別ファイルの特定の場所へコピーしたいです。
VBA初心者です。
色々試した結果頭がこんがらがって来たので、質問させて下さい。
個人情報の入っている複数のExcelブックがあり、その中からお客様番号の検索をかけ、完全に一致する方を探し出した上で、その方の住所、電話番号、などいくつかだけを一つの別のファイルの特定のシートに反映させたいのですが、こういったことは可能でしょうか?
いっぺんに作業を行うのではなく、お客様が来られたら検索をかけて、反映させる、という形になっています。本来だったら、自分でもっと勉強すべきなのは重々承知しておりますが、ご教授頂けると助かります。
今まで試したのは、"複数のブックから検索する方法""特定のセルを抽出"でした。検索をかける事は出来たのですが、別のブックから目的のブックへの反映が上手くいきません。
よろしくお願い致します。
No.1
- 回答日時:
こんにちは
疑問なのは、なぜ複数のブックなのか。(処理上では別に問題にはなりませんが)
顧客リストから検索するようなイメージなのでしょうけれど、データベース的に利用するのならブックがバラバラに存在すること自体が合理的には思えません。
>こういったことは可能でしょうか?
十分に可能でしょう。
ヒットする情報が一つなのか複数なのかによって若干処理は変わりますが、基本的には
1)ブック内を検索して
2)見つかったらデータを特定シートにコピペ
3)見つからない場合は次の処理(または終了)
という流れの処理コードを作成できればよいと思われます。
複数のブックを対象とする場合は、上記をループしながら各ブックを順に検索してゆけば良いはずです。
まずは、対象ブックを一つとして作成してみて、うまくいったら、それをループさせるか、あるいは、サブルーチン化しておいて、ブック名を引数にして繰り返し呼び出すような考えにする方がわかりやすいかもしれません。
>別のブックから目的のブックへの反映が上手くいきません。
セルの値のコピペと同様の処理でできるはずです。
元のセルに関数式が設定されているような場合は、単純にコピペすると(数式がコピーされるので)ペースト先のセルに表示される値がおかしなことになる場合があります。
そのような場合は、「値をペースト」するようにすればよろしいかと。
または、
book1.sheet1.range1.Value = book2.sheet2.range2.Value
のように値を直接代入する方法をとれば、結果的に、値がコピーされることになります。
ご返答ありがとうございます。
複数ブックの理由は会社側の理由で、ひとつのブックにしてしまうと開かなくなるんです…。随時お客様の数が増えていき、入力を行ってるので今回分けて制作した次第です。
なるほど、考え方がわかっただけでもとても頭がすっきりしました。対象を最初から複数で考えていたので、一つでやってみます、ありがとうございます。
直接代入もやってみますね!ありがとうございます。
No.2
- 回答日時:
No1です。
>ひとつのブックにしてしまうと開かなくなるんです
それだけのデータ量を複数ブックで処理しようとすると、時間がかかってしまう可能性が大です。
>お客様が来られたら検索をかけて、反映させる~
どれだけの速度を必要とするのかわかりませんが、ほぼリアルタイムに結果が欲しいような状態であるならなおさらです。
ひとつのブックでは開けないほどのデータ量になっているのなら、もはやエクセルの検索で扱う範囲を超えているように思われます。
データベースソフト(アクセスなど)を利用なさった方が良いのではないでしょうか。
そうですよね、私もそう思います。
詳しい事は何も言えないので、これ以上の情報の開示は出来ないのですが、可能だとわかっただけでもありがたいので、自分でなんとかVBAを作ってみたいと思います。お力添えありがとうございました。
No.3ベストアンサー
- 回答日時:
エクセルですと、ODBCがありますが、 複数のデータですと勝手が悪いので、ADODBで、検索システムを作ってしまえばよいのではないでしょうが、複数でも、それほど手間は掛からないでしょう。
論より証拠です。私自身、相当長い間、手を付けたことがなかったので、私自身のためにやってみることにしました。
https://www.post.japanpost.jp/zipcode/dl/kogaki- …
(マクロの目的は郵便番号検索ですが、それが見つからなかった時に、事業所の郵便番号を探します。あくまでもサンプルです。実務では、この場合は、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
以下はデータベースとしてのエクセルのレイアウト(タイトル行に注意)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) EXCEL VBA 単語置き換え について質問です ブック名 ぶぶぶ シート名 ししし セル V3〜 3 2023/03/08 01:41
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Excel(エクセル) 【Excel】指定のセル内容を基に別シートのセルを検索して選択する【VBA】 1 2022/06/16 16:16
- Excel(エクセル) エクセルのマクロについて教えてください。 1 2023/02/21 09:28
- Excel(エクセル) 指定値をマクロで検索&シート移動 2 2022/04/27 23:29
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Excel(エクセル) マクロVBAのフォルダ階層別で検索の方法 4 2022/04/03 23:23
- Excel(エクセル) エクセルのマクロについて教えてください。 2 2023/02/21 13:29
- Excel(エクセル) マクロVBA別Excelブックにデータ転記 2 2022/07/10 23:35
- Visual Basic(VBA) エクセルのマクロについて教えてください。 4 2023/05/24 08:33
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBAでブックを非表示で開いて処...
-
エクセルの関数 ENTERを押...
-
複数ファイルから特定シートの...
-
エクセルを共有するとPCによっ...
-
フォルダ内の複数ファイルから...
-
WorkBooksをオープンさせずにシ...
-
指定ファィルの指定シートをシ...
-
Excel起動時に特定のワークシー...
-
エクセル2016です。「ブッ...
-
フォルダ内の複数ファイルから...
-
MSアクセスのインポート手法(パ...
-
Excel(2010)のフィルターが保...
-
ブックの保護ができないんです...
-
Excelで複数ブックの同一セルに...
-
エクセルファイルを開かずにpdf...
-
エクセルで「ディスクがいっぱ...
-
外部ブック参照が#REF!になって...
-
エクセルVBAでブックを相対パス...
-
エクセルで参照しているデータ...
-
エクセルの関数について教えて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルを共有するとPCによっ...
-
エクセルの関数 ENTERを押...
-
VBAでブックを非表示で開いて処...
-
WorkBooksをオープンさせずにシ...
-
Excelでブックの共有を掛けると...
-
エクセルで参照しているデータ...
-
Excel(2010)のフィルターが保...
-
Excelで複数ブックの同一セルに...
-
VBA バックグラウンドで別ブッ...
-
エクセルで50行ごとに区切った...
-
エクセルで「ディスクがいっぱ...
-
エクセルにおける,「ブック」...
-
エクセルファイルを開かずにpdf...
-
フォルダ内の複数ファイルから...
-
ブックのピボットを別ブックに...
-
エクセルシートの一部を送りたい
-
エクセル2016です。「ブッ...
-
エクセルで別ブックをバックグ...
-
フォルダ内の複数ファイルから...
-
複数ファイルから特定シートの...
おすすめ情報