エクセルファイルから指定したデータを抜き出して、別のエクセルファイルを作る方法

Question

エクセルファイルから指定したデータを抜き出して、別のエクセルファイルを作るうまい方法教えてください。

以下の記事で、マクロを使えばできそうなことは承知しています。
Excelマクロの使い方入門（初心者向け）！作成〜保存方法まで
https://office-hack.com/excel/macro/

エクセルのマクロ作成法！業務を加速させる超便利な機能とは
https://udemy.benesse.co.jp/office-enhance/microsoft-office/excel-macro.html

やりたいことは、以下の通りです。
アンケート、申請書などのような決められた書式に必要事項が記載されたエクセルファイルが多数あります。
これら多数のエクセルファイルから、当方が必要とする項目のデータを取り出して、
新しいエクセルファイルの１行に、もとの１枚のエクセルファイルから取り出したデータを埋め込みたいです。
例えば、２００ファイルあれば、２００行のエクセルファイルが出来上がるイメージです。

当然、手作業では可能ですが、自動化して、作業依頼したいので質問しています。
感じとしては、
　元ファイルと新ファイルのエクセルファイルを開いて、どのデータを新ファイルにコピーするかを１回実行する。
　２番め以降のファイルを指定して、「さっきと同じことをせよ」というと、２行目以降にそれらのファイルからデータがコピーされる
ということがやりたいです。
　願わくば、「１回目の操作」をどっかに保存しておいて、別の日に新しくアンケート結果が届いた時点で、
　　アンケートファイル、「１回目の操作」を記憶したファイル、集計結果をしまうファイル
を指定して、「実行」すれば、集計結果ファイルにデータが追加できたら嬉しいです。
よろしくお願いします。

はじめに書いたように、マクロを使えばできそうなことは承知しています。
でも、
　具体的にマクロを使う場合の手順、
　アンケートファイル、集計ファイルの指定方法、
　２番目以降の多数のアンケートファイルの指定の仕方、
　フォルダを指定すればそのフォルダ内の全てのファイルを対象とするなど、
といったことがわからないので知りたいのです。

もちろん、エクセルのマクロ以外での解決法の回答も大歓迎です

なお、具体的な解決に結びつかない回答は不要ですので、ご遠慮ください。

rukaandkaito · Accepted Answer

面白そうだったのでコード作ってみました
前提条件
・対象のブックにはSheetが３つあります
　　Sheet1(位置)
　　Sheet2(設定)
　　Sheet3(出力)
・設定シートには４つのボタン(ActiveX)があります
　　cmdF07(開始)
　　cmdF08(終了)
　　cmdF09(場所)
　　cmdF10(実行)
では３つの場所にそれぞれコードを記載します

＜Sheet2(設定)＞
Private Sub cmdF07_Click()
　Call psubClear
　gblnSwitch = True
End Sub

Private Sub cmdF08_Click()
　gblnSwitch = False
End Sub

Private Sub cmdF09_Click()
　Call psubFolder
End Sub

Private Sub cmdF10_Click()
　Call psubDatGet
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　If gblnSwitch = True Then
　　Call psubSwtSet(Target)
　End If
End Sub

＜ThisWorkbook＞
Private Sub Workbook_Open()
　gintS = 1
　Do Until Sheets("位置").Cells(gintS, 1).Value = ""
　　gintS = gintS + 1
　Loop
　gintO = 1
　Do Until Sheets("出力").Cells(gintS, 1).Value = ""
　　gintO = gintO + 1
　Loop
End Sub

＜標準モジュール＞
Public gblnSwitch As Boolean
Public gintS      As Integer
Public gstrPath   As String
Public gintO      As Integer

Public Sub psubClear()
　gintS = 0
　Sheets("位置").Cells.ClearContents
　gintO = 0
　Sheets("出力").Cells.ClearContents
End Sub

Public Sub psubSwtSet(Target As Range)
　gintS = gintS + 1
　Sheets("位置").Cells(gintS, 1).Value = Target.Row
　Sheets("位置").Cells(gintS, 2).Value = Target.Column
End Sub

Public Sub psubFolder()
　With Application.FileDialog(msoFileDialogFolderPicker)
　　If .Show = True Then
　　　gstrPath = .SelectedItems(1)
　　End If
　End With
End Sub

Public Sub psubDatGet()
　Dim strRes As String
　Dim objBok As Workbook
　Dim intL   As Integer
　Dim intR   As Integer
　Dim intC   As Integer
　
　Application.DisplayAlerts = False
　Application.ScreenUpdating = False
　
　strRes = Dir(gstrPath & "\*.xlsx")
　
　Do While strRes <> ""
　　gintO = gintO + 1
　　Set objBok = Workbooks.Open(gstrPath & "\" & strRes)
　　For intL = 1 To gintS
　　　intR = ThisWorkbook.Sheets("位置").Cells(intL, 1).Value
　　　intC = ThisWorkbook.Sheets("位置").Cells(intL, 2).Value
　　　ThisWorkbook.Sheets("出力").Cells(gintO, intL).Value = objBok.Sheets(1).Cells(intR, intC).Value
　　Next intL
　　objBok.Close SaveChanges:=False
　　strRes = Dir()
　Loop
　
　Application.ScreenUpdating = True
　Application.DisplayAlerts = True
End Sub

開始ボタンを押して任意のセルを選択していくと場所を記憶していきます
終了ボタンが押されると記憶を終了します
場所ボタンで対象フォルダを指定します
実行ボタンで対象フォルダ配下の『.xlsx』ファイルを全て開き、記憶した位置のデータを順番に出力シートに転記していきます
記憶位置についてはファイルを閉じても大丈夫です。場所シートに座標を記録しています

こんな感じでいかがでしょうかｗ

rukaandkaito · Answer

No2です
なるほど、VBA初心者ということですね
ボタンは４つ全て配置していますか？
開発タブ（表示されていない場合には、ファイル→オプション→リボンのユーザー設定より、右側の開発にチェックを入れてください）のデザインモードがＯＮになっていませんか？
次にコード（Visual Basic）の再生(▶)、一時停止(イコールの縦向き)、終了(■)のいずれかが押せない状態になっている場合プログラムが一時停止していますので、すぐ右の停止（■）を押して再度実行してください
開始ボタンを押したとき、実は画面上なにも変化がありません。
動作を明確にしたい場合には開始ボタン(Ｆ７)のコードの先頭にメッセージボックスを表示させてみては如何でしょうか
　MsgBox "位置記録を開始します"
また終了ボタン(Ｆ８)には最後に
　MsgBox "位置記録を終了しました"
という風にすればきちんと動いていることが確認できます
＞開発 - 挿入 - ActiveX　のボタンですね。
＞作ったボタンのプロパティを開いて、cmdF07に名前を変えて、表示を「開発」にするということですね。
ここが少し間違っています。
開発タブ(前述参照)→コントロールグループ内の挿入(プルダウン：▼)→下部ActiveXコントロール左上のボタンを選択
その後プロパティのNameとCaptionを変更する部分は合っていますが、Captionは"開始"ですよ。加えて４つのボタン全てを同様に設定してくださいね

開始ボタン～終了ボタンの動作確認については、複数個所の選択が終了した後『設定シート』を見てみてください。先ほど選択したセル位置が行ごとに記載されているはずです。
コードに関しては全て丸ごとコピーしていただければ動作確認済みですので問題ないはずですよ♪

ママチャリ · Answer

質問者さんのイメージとはちょっと違いますが、こんなのはどうでしょう。
添付画像のようなシートを用意して、次のVBAを実行してください。

Sub sample()
    Dim fp As Variant
    Dim fn As String
    Dim r As Long
    Dim c As Long
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show = True Then
            r = 1
            For Each fp In .SelectedItems
                r = r + 1
                fn = Right(fp, Len(fp) - InStrRev(fp, "\"))
                fp = Left(fp, InStrRev(fp, "\"))
                For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
                    Cells(r, c).Formula = "='" & fp & "[" & fn & "]Sheet1'!" & Cells(1, c)
                Next c
            Next fp
        End If
    End With
End Sub

実行するとファイルを選択するウィンドウが開くので、対象ファイルを選択します。フォルダ内の全ファイルを選択する場合は、Ctrl+aで選択できるはずです。
そうすると、添付画像の1行目で指定したセルの値が取ってこれます。
ちなみに、このマクロは「対象ファイルのセルを参照する数式」を自動で設定しているので、参照先のセルの値が空白の場合、ゼロが表示されてしまいます。ゼロでは困る場合は、ご自分で数式を改良してみて下さい。

fujillin · Answer

こんにちは

ご質問内容がいろいろある上に、かつ、抽象的なので、具体的な回答はつきにくいと思いますが・・・

＞マクロを使えばできそうなことは承知しています。
ある程度のことは可能ですが
＞「１回目の操作」をどっかに保存しておいて、～
に関しては、単なる「操作の記録」なら工夫次第でできるでしょうが、「操作の意味の記録」となると一筋縄ではいかないと思われます。
いくつかの操作を汎用化しておいて、パラメータやオプションを指定するような形式等であれば、比較的簡単にできそうな気がします。
一方で、「単なる記録」で良い場合であれば、エクセルにはすでに「マクロの記録」の機能があるので、そちらを利用することで実現できそうに思います。

＞やりたいことは、以下の通りです。
＞　～～～～
雰囲気的には以下のようなことなのかなと…
https://qiita.com/takahiro_itazuri/items/08754db91c6ab72deb03

＞具体的にマクロを使う場合の手順、
＞アンケートファイル、集計ファイルの指定方法、
こんなことがわかればよいということでしょうか？
http://officetanaka.net/excel/vba/file/file02.htm
https://www.sejuku.net/blog/71709
直接プログラム内から指定するのであれば（↑）は不要です。

＞２番目以降の多数のアンケートファイルの指定の仕方、
？？
方法としては、1番目と同じ方法で選択すればよいのでは？

＞フォルダを指定すればそのフォルダ内の全てのファイルを対象とするなど、
例えば、こんな感じ。
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_110_120.html

エクセルファイルから指定したデータを抜き出して、別のエクセルファイルを作る方法

面白そうだったのでコード作ってみました

No2です

質問者さんのイメージとはちょっと違いますが、こんなのはどうでしょう。

こんにちは

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

このQ&Aを見た人はこんなQ&Aも見ています

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

このQ&Aを見た人がよく見るQ&A

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

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