エクセルで外部ファイルから項目を引っ張り、一覧表(台帳)を作成したい

質問

エクセル形式の申請書を受領し、その一覧表をエクセルで作成したいと思っています。
申請書の形式は同じなので、名前、住所、性別などの項目を
１つ１つ一覧表にコピペすればよいのですが、
受領する度に１つ１つの項目を一覧表へ、貼り付けしなくてはなりません。

例：
申請書一覧表.xls

申請書01.xls
申請書02.xls
申請書03.xls

私が考えているやり方では、一覧表の例えばA1に申請書のファイル名(申請書01.xls)、
B1に申請書のワークシート名(sheet1)を入れれば、自動的に申請書の各項目を
一覧表の行(C1,D1,・・・)へ引っ張って来るような一覧表を作成したいと思っています。
これを実現するのに、一覧表のC1セルに、=INDIRECT("["&A1&"]"&B1&"!$C$3")
　・A1は一覧表上で申請書のファイル名(申請書01.xls)を記名したセル
　・B1は一覧表上で申請書のワークシート名(Sheet1)を記名したセル
　・$C$3は申請書01.xls内の参照項目の１つ(例：名前)
で、実現をやってみましたが、
indirect関数は申請書のファイルを開いていないと出来ず、困っています。
申請書は１日に30件程度来て、一覧表だけを見て、例えば田中さんからは何件来ている
とか、一覧表のみを見れば、申請書の全てが分かるように管理したいと思っています。
処理状況管理も一覧表で行いたいと思っています。

申請書一覧表.xlsは、申請書01.xlsよりも一段上のフォルダに置きたいと思っていますが、不可能なら、申請書と一緒のフォルダでも構いません。

また欲を言うと、一覧表から申請書をクリックで開けるようにできたら良いと
思っています。

良い実現方法がありましたら、教えてください。

回答No.3

abe_onesel

#1です。

＞下記の、
＞>="=["&A1&"]"&B1&"!$C$3"
＞>上記の式をD1に入力し、それをE1セルへ値コピー後、F2キー、Enterキー
＞>とすれば大丈夫かと思います。
＞を試してみたのですが、上記をそのまま入れると、
＞そのまま=[申請書一覧表.xls]Sheet1!$C$3とそのまま表示されるだけで、
＞申請書から引っぱって来ません。

普通に式を入力すれば、文字列としての=[申請書一覧表.xls]Sheet1!$C$3というのが表示されるのは問題無いです。
なので、それを隣のセルなどにコピーして、形式を選択して貼り付けから値で貼り付けます。
そうすると、セルの値も=[申請書一覧表.xls]Sheet1!$C$3というのに変わります。
ここまでは文字列とされてますので、数式としてExcelに認識させる為に、F2キーを押して編集してからエンターで確定させる事です。
F2キーでなくても、ダブルクリックしてセルを編集できるようにしてからのエンターでもOKです。
※表示形式が文字列になっている場合はできないので、標準に直して下さい。

下記はちょっとしてヒントになりますが、
まず、申請書01.xlsを開いておき、申請書一覧表.xlsのどこかのセルに=（半角イコール）を入力し、申請書01.xlsのどこか一つのセルを指定します。
そうすると、=[申請書01.xls]Sheet1!$C$3のような数式になると思います。
その後に申請書01.xlsを閉じると、
='C:\●●●●●\◇◇◇◇◇\[申請書01.xls]Sheet1'!$C$3
みたいになると思います。
これを参考にしてもらえればある程度対応できると思われます。
毎日ファイル名が変更されないのであれば、一回この方法で全部のセルを参照すれば翌日から自動で参照してくれると思います。

お礼

上記の件、ありがとうございました。
やり方としては、申請書のワークシートを
一覧表のワークシートへ移して、=indirect()を使うようにして、
対応することにしました。

色々とありがとうございました。

回答No.2

hige_082

VBAの一例です
申請書一覧表のsheet1の
A1に申請書のファイル名(申請書01.xlsなど)
B1に申請書のワークシート名(sheet1など)
申請書ファイルがあるフォルダ名を"C:\"に
myFolder = "C:\"
申請書から引っぱって来くる、セル
myData = Array("c1", "d1", "e1", "f1", "g1")
それぞれ書き換えてください

'標準モジュールへ
Sub test()
Dim myFolder As String
Dim myFile As String
Dim myWS As String
Dim myBook As Workbook
Dim myData As Variant
Dim 開いたブック As Workbook
Dim i As Integer
Set myBook = ActiveWorkbook
myFolder = "C:\"
myFile = myBook.Worksheets("sheet1").Range("a1").Value
myWS = myBook.Worksheets("sheet1").Range("b1").Value
myData = Array("c1", "d1", "e1", "f1", "g1")
Workbooks.Open filename:=myFolder & "\" & myFile
Set 開いたブック = Workbooks(myFile)
For i = 0 To UBound(myData)
myBook.Worksheets("sheet1").Range(myData(i)).Value _
= 開いたブック.Worksheets(myWS).Range(myData(i)).Value
Next
開いたブック.Close SaveChanges:=False
End Sub

試す場合はコピーを取ってね
意味がわからなければ無視してね

お礼

上記の件、ありがとうございました。
VBAに関して良く分からなくてすいません。
ALT+F11をして貼り付け・・・みたいなことをすると
思うのですが、分からなくてすいません。

やり方としては、申請書のワークシートを
一覧表のワークシートへ移して、=indirect()を使うようにして、
対応することにしました。
色々とありがとうございました。

回答No.1

abe_onesel

完全な処理をしようとすると、ＶＢＡを使わないとできないように思いますが、
ある程度であれば関数でもできると思います。

INDIRECT関数は対象のファイルを開いていないと無理なので、
='C:\[申請書.xls]Sheet1'!$C$3
のように直接参照すればファイルが閉じていても値が表示されるはずです。
毎回決まったフォルダで同じファイル名であれば、一度数式を入力しておくと大丈夫だと思いますが、そうでなければ

="=["&A1&"]"&B1&"!$C$3"
上記の式をD1に入力し、それをE1セルへ値コピー後、F2キー、Enterキーとすれば大丈夫かと思います。
これは数が多いと大変ですが、毎日ファイル名などが変わる場合はＶＢＡを使った方が楽です。

＞また欲を言うと、一覧表から申請書をクリックで開けるようにできたら良いと
＞思っています。
こちらについては、ハイパーリンクで対応ができると思います。
=HYPERLINK("C:\申請書.xls")
などとすればクリックしてファイルが開きます。

私であれば、ＶＢＡで対象フォルダのファイルをファイル名やフォルダ名や更新日などで全て検索し、
対象ファイルを順次開いて必要な値をコピーしてファイルを閉じます。
ただ、今回は申請書の形式が同じという事なのでそこまでしなくても大丈夫でしょうし、
ＶＢＡの指定が無いので詳細は割愛します。

お礼

上記の件、ありがとうございました。
やり方としては、申請書のワークシートを
一覧表のワークシートへ移して、=indirect()を使うようにして、
対応することにしました。

色々とありがとうございました。

補足

abe_oneselさん
早速のご回答、ありがとうございます。
詳細は、じっくりやってみますが、

下記の、
>="=["&A1&"]"&B1&"!$C$3"
>上記の式をD1に入力し、それをE1セルへ値コピー後、F2キー、Enterキー
>とすれば大丈夫かと思います。
を試してみたのですが、上記をそのまま入れると、
そのまま=[申請書一覧表.xls]Sheet1!$C$3とそのまま表示されるだけで、
申請書から引っぱって来ません。

ＶＢＡで実現できるのであれば、そうしたいですが、
知識が無いので、全然分かりません。
色々やって、また分からなかったらまた質問させて頂きます。