Excelで参照用の文字列を数式にする方法

質問

少々分かりにくいとは思いますが、困っているので質問させて頂きます。
状況、A.xlsというファイルに1から100のシートがある状態です。
各シートのA1には名前が入っているのですが、ここから別のB.xlsファイルに名前の一覧を作成したいというものです。
B.xlsに表示させたい内容(CSVで表示)
番号,名前
1,シート1のA1に記入された名前
2,シート2のA1に記入された名前

B.xlsのB2には、='[A.xls]1'!A1 と入力すれば良いのですが100個分を一つ一つコピーするのは非常に面倒ですし、住所等の項目が増えたり、数も増える可能性があります。この数式自体を文字列操作で作成することは簡単なのですが、作成した文字列を数式として実行させることができないかと思い投稿しました。
または、上記を実現する簡単な方法があればとも思います。
よろしくお願いします。

回答No.2

noname#52504 · ベストアンサー

ご質問の趣旨を取り違えていましたらすみません。
・シート名を得たい
のではなくて、
・シート名が連番であるブックＡの、各シートのA1セルの値を得たい
ということですよね。
-----------------------------------------------
●甲案：INDIRECTを使って参照を作る

INDIRECT関数を使えば、
「参照先を表す文字列」を使って参照することができます。

ブックＢのB2を
　=INDIRECT("'[A.xls]"&A2&"'!A1")
として下方にフィルすれば、A列の値(シート名)に応じて、
ブックAのシート1,2,3…のA1セルの値が順に返ります。

ただし、INDIRECT関数には、
　【閉じているブックは参照できない】
という欠点がありますから、ブックＢを使う際には、
常にブックＡも開いておく必要があります。
-----------------------------------------------
●乙案：「文字列操作で作成した数式」を数式に変換する

　０．ブックＡを開く

　１．文字列操作で「数式」を作成する
　　ブックＢのB2を
　　　="='[A.xls]"&A2&"'!A1"
　　として下方にフィル
　　※もちろん、この時点では参照先の値は返りません

　２．「数式」を値として確定する
　　B列をコピー＞編集＞形式を選択して貼り付け＞値

　３．文字列中のイコール"="を、同じくイコール"="で置換する
　　B列を選択＞編集＞置換
　　　検索する文字列：=
　　　置換後の文字列：=
　　＞すべて置換

この操作で、文字列の「数式」が数式と認識され、参照先の値を返すようになります。
普通にブックＡを参照する数式を入力した場合と同じ状態になりますから、
ブックＡを閉じてしまっても大丈夫です。

ただし、シート数が増えたりした場合には、
その都度０～３の操作を繰り返す必要があります。
-----------------------------------------------
どちらも一長一短ですが、ご参考まで。

お礼

回答ありがとうございます。
まさにご指摘の通りです。
甲案の方は、やはり使いづらいので乙案を試したところ、うまくいきました。当面この方法で対応できそうです。ありがとうございました。

回答No.1

suz83238

B.xlsが既存のなのか新規のブックなのか不明ですが、下記をやってみて下さい。シートが100あるブックの標準モジュールに下記を貼り付けて実行。
Sub xxx()
Workbooks.Add
ThisWorkbook.Activate
n = 1
For Each sh In ActiveWorkbook.Sheets
Workbooks(2).Activate
Cells(n, 1) = n & "," & sh.Name
n = n + 1
Next
End Sub

新規のブックを作って、シート名を転記します。あとは、これをB.xlsで保存すれば完了。

お礼

回答ありがとうございます。
やってみましたが、ちょっとこちらのやりたいこととは違うようです。
ただ、シート名がこれで取得できるのならこれをベースにマクロも
考えてみます。ありがとうございました。