INDIRECT関数で開いている他ファイルを参照したいが、シート名にスペースが入っている

お世話になっております。
同じフォームのファイルが60個以上あり、各々のファイルを開くことで、数値を下記計算式が入っているエクセルに落とし込んいきたいと思っています。（最終的にピボットテーブルにしてまとめます）

=INDEX(INDIRECT("["&B$2&"]田　中!$C$1:$AB$2000"),M5,N5)

B2には参照先のファイル名が入っています。（例：JAPAN.xlxs)
ただ、参照先のファイルが全てシート名にスペースが入っているため、参照エラーになってしまいます。
スペースを消す方法、もしくはスペースがあったままでも参照できる方法があれば、教えていただきたいです。

参照先のファイルにはパスワードがかかっていて、作成者は教えられないと言っています。

データの集計にコピペしかできず、しかもセル自体にもロックがかかっているので、コピペできる範囲も少なく、とても時間がかかっています。


””を入れるなどやってみましたが、エラーです・・・・。

わかりづらいかと思いますが、ご教示いただけますようよろしくお願いいたします。

No.5 回答者： WindFaller 回答日時： 2015/02/03 01:26

#4の訂正

>シートの選択の時に、トーチの左から、1,2,3
トーチとはなんだろう？たぶんシートのことだと思います。（^^;
Sheet1, Sheet2, Sheet3
1　　　　2 　　　3 ←このこと

No.4 回答者： WindFaller 回答日時： 2015/02/03 00:39

こんばんは。

#3の回答者です。

そもそもというのかな、どのみち、おっしゃるエラーは、今の状態のままでは、どうあがいても、INDIRECT では、エラーは免れません。

ご質問の趣旨とは違う方向になってしまいましたことを、お詫びします。

もう、この回答は終点に近いのですから、最初から、VBAをトライしてみたら、と私は考えてしまいました。今回のマクロは、ある意味では禁断なのかもしれません。いろいろなものを考えてみましたが、結局、「必要なものを全部コピーしてしまえば？」という趣旨です。

INDEX関数も、コピー側で使えばよいです。このマクロで気をつける点は、シートの選択の時に、トーチの左から、1,2,3……と数えることです。該当シートをシート名ではなく、数字で入れるということです。

なお、このマクロは、私の知る範囲では、違法ではありません!、念のため。なお、逆の対策も考えないといけません。

'//
Sub GetProtectedData()
　'リードオンリーBookに限る(for the book with setting "Read Only")
　Dim rng As Range
　Dim rng2 As Range
　Dim fn As Variant
　Dim wb As Workbook
　Dim ShName As String
　Dim buf As Variant
　Dim sh As Worksheet
　Dim i As Long
　'********
　'予め設定してください。目的のブックのシートの中の範囲です。
　'もし、ここを　=""　の状態にすると、シートで使われている部分を全部コピーします。
　Const RANGEAREA As String = "A1:D30"
　'********
　
　fn = Application.GetOpenFilename("Excel (*.xls?),*.xls?")
　If VarType(fn) = vbBoolean Then Exit Sub
　Set wb = Workbooks.Open(fn, False, True)
　With wb
　　buf = Application.InputBox("該当シートを左から数えた数字を入れてください。", Type:=2)
　　If VarType(buf) = vbBoolean Then
　　　.Close
　　　Exit Sub
　　Else
　　　i = Val(buf)
　　　ShName = .Sheets(i).Name
　　End If
　　If IsNumeric(i) And .Sheets.Count >= i Then
　　　If RANGEAREA <> "" Then
　　　　Set rng = .Sheets(i).Range(RANGEAREA)　'セルに値のある所は全部範囲にする
　　　Else
　　　　Set rng = .Sheets(i).UsedRange
　　　End If
　　Else
　　　MsgBox "シート番号の取得に失敗しました。終了します。", 16
　　　.Close
　　　Exit Sub
　　End If
　End With
　With ThisWorkbook
　　Set sh = .Worksheets.Add(After:=.Sheets(Sheets.Count))　'マクロのブックに1つシートが加わります
　　buf = Dir(fn)
　　sh.Range("A1").Value = buf & "." & ShName
　　rng.Copy sh.Range("A2") '通常はこれで大丈夫。もし取れない時は、代わりに次の2行のコードを使う
　　'rng.Copy
　　'sh.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'シートが数式だけで、論理的値のみの時
　　wb.Close
　End With
End Sub
'//

No.3 回答者： WindFaller 回答日時： 2015/01/30 10:27

こんにちは。

昨日から、試行錯誤を繰り返しました。
マクロを作りながら、ひとつずつ検証してみたのです。簡単にと思っていたのにも関わらず、マクロがどんどん肥大して、考え方を改めて、本日、また挑戦しました。そこで、目的のブックのシート一覧を作ることにしました。

>参照先のファイルにはパスワードがかかっていて、作成者は教えられないと言っています。
最初に、バカなことを聞くようで恐縮なのですが、そのファイルは開けられますでしょうか？
たぶん、「読み取り専用」になるのだと思います。ブックを開くためのパスワードが必要な場合は、一応、掲示板で教えるのは無理です。

なお、INDIRECT関数では、ファイルを開いていないとエラーになります。それはお分かりなっているかと思います。本来、INDEXで、ある範囲を抜き出す目的だと思いますが、それは、今回の問題が解決してからにしてください。

マクロの設定の説明
'開発－挿入－フォームコントロール－□ボタン　でクリック
'シートの任意の場所にクリックで、四角を描くとボタンが出来ます。

'そのボタンを右クリック－マクロの登録－(ダイアログ)－新規作成
'VBエディタ画面がでていますから、[Sub ボタン1_Click()]という文字があれば、
'2行目以下に、下のコード貼り付けます。

'貼り付けたら、Excelの表の画面に戻り、何も書かれていない列にアクティブセルを
'置き、ボタンをクリックすれば、ファイルの選択から始まり、それを選ぶと一覧が
'出てきます。読取り専用でも、問題はでませんでした。無論、そのまま数式も使えます。

'-----------------------------------------
'シートの一覧を作るマクロ　'2015/01/30
　Dim FileName
　Dim fn As String
　Dim FilePath As String
　Dim pat As String
　Dim wkb As Workbook
　Dim mysh As Worksheet
　Dim i As Long
　Dim flg As Boolean
　Set mysh = ActiveSheet
　If WorksheetFunction.CountA(ActiveCell.EntireColumn) > 0 Then
　　MsgBox "任意の空の列に、セルを置いてください。", 48
　　Exit Sub
　End If
　
　FileName = Application.GetOpenFilename("Excel(*.xls?),*.xls?")
　If VarType(FileName) = vbBoolean Then Exit Sub
　fn = Dir(FileName)
　FilePath = Replace(FileName, fn, "")
　For i = 1 To Workbooks.Count
　　If StrConv(Workbooks(i).Name, vbNarrow) Like fn Then
　　　MsgBox fn & "は、すでに開いていますが、続行します。", 48
　　　Set wkb = Workbooks(i)
　　　flg = True
　　　Exit For
　　End If
　Next i
　If wkb Is Nothing Then
　　Set wkb = Workbooks.Open(FileName)
　End If
　mysh.Activate
　If flg Then
　　pat = "'[" & fn & "]"
　Else
　　pat = "'" & FilePath & "[" & fn & "]"
　End If
　ActiveCell.Value = fn & "のシート名一覧"
　For i = 1 To wkb.Sheets.Count
　　ActiveCell.Offset(i).Value = pat & wkb.Sheets(i).Name & "'!"
　Next i
　If MsgBox(fn & "を閉じますか？", vbOKCancel) = vbOK Then
　　Workbooks(fn).Close False
　End If
　Set mysh = Nothing
　Set wkb = Nothing
'-----------------------------------------

この回答へのお礼

WindFaller様

お世話になっております。
丁寧にご回答いただきありがとうございました。
また、先ほどtom04さんにお礼した際に、誤ってwindfall様とかいてしまいました。申し訳ありませんでした。
マクロですが、エクセルで試させていただきました！すごいです。ファイル名を選択すると、シート名までアドレス(フルパス？）が出てきました。

ただ、申し訳ないですが、このシート名をどうINDIRECT、INDEX関数に落とし込めばよいのかわからず・・。
例えば、下記のようにシート名一覧がA16セルにあったとして、
'C:\Users\xxxxx\Desktop\[Book1.xlsx]Sheet1'!

INDIRECT(A16&"C5")としても#REF!です。

私がINDIRECTとINDEX関数の使い方をわかっておらず申し訳ないですが、ご指導くださいますと幸いです。

お礼日時：2015/02/02 13:36

No.2 回答者： tom04 回答日時： 2015/01/29 20:18

こんばんは！

お役に立てるかどうか判りませんが・・・
VBAで参照先ファイルのSheet名の空白を消去してはどうでしょうか？

参照先のファイルのSheet名は変更可能だという前提です。
お手元のB2セルにファイル名が入っているSheetを開く　→　Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sheet名空白消去() 'この行から
Dim k As Long, str As String, fN As String, wB As Workbook
fN = Range("B2")
Set wB = Workbooks(fN)
For k = 1 To wB.Worksheets.Count
'▼　全角スペースを　→　半角スーペースに　→　スペースを削除
str = Replace(Replace(wB.Worksheets(k).Name, "　", " "), " ", "") '★
wB.Worksheets(k).Name = str
Next k
End Sub 'この行まで

※　コード内の「★」の部分のスペースは順に「全角・半角・半角・スペースなし」となっています。
※　参照先ファイルを開いた状態でマクロを実行してみてください。
おそらく参照先ファイルのSheet名の空白はすべて消去されるはずです。

※　参照先Sheet名の変更がダメの場合は
Sheet名を取得することは可能だと思います。m(_ _)m

この回答へのお礼

tom04様

お世話になっております。
ご回答いただきありがとうございます。

VBAのコードを教えていただきありがとうございます！
参照先のコードは変更ができない（ロックがかかっている）ので、難しいかもしれません。
私の説明不足で申し訳ありません。
一番初めにご回答いただいたwindfall様のマクロを実施してみていますので、またその後試させていただきます。
親切にアドバイスいただき、本当にありがとうございます。

お礼日時：2015/02/02 11:45

No.1 回答者： WindFaller 回答日時： 2015/01/29 17:11

こんにちは。

=INDEX(INDIRECT("["&B$2&"]田　中!$C$1:$AB$2000"),M5,N5)
試してみましたが、まず、この数式はあっていますでしょうか？

通常、Excelでは、
=INDEX(INDIRECT("'["&B2&"]田　中'!$C$1:$AB$2000"),M5,N5)

のように、'[ブック]シート'! は、シングルクォーテーション(')で括ってあげないといけないはずです。違いを比べてみてください。

それでも、うまくなければ、ごくごく簡単なマクロも考えましょう。

>参照先のファイルが全てシート名にスペースが入っているため、参照エラーになってしまいます。
>スペースを消す方法、もしくはスペースがあったままでも参照できる方法があれば、教えていただきたいです。

そのような便利な方法はありませんが、私自身は、マクロでそのシート名をいくつか用意してヒットさせる方法を使う時があります。特に、数値の半角や全角は、まったく区別がつかないからです。今回の場合は、空白の全角と半角の違いです。また、マクロでは、左側から、順番で番号がついていますから、シート番号を使うこともあります。

この回答へのお礼

WindFaller様
ご回答いただき、ありがとうございます。

この式はシート名が変更できるファイルでデータ集約したものから取ってきているのですが、その際はシングルクオテーションなしでも参照できていました。
スペースがない場合、シングルクオテーションも入れても問題なくできました。
通常は入れるものなのですね、以後そのようにいたします。

スペースがあるシート名のファイルでシングルクオテーションを入れてやってみましたが、やはり参照エラーです。
おっしゃるように、マクロでシート名の候補を挙げるやり方がよいのでしょうか。
私には少々難しそうです。
パスワードを教えてくれれば、解決なのですが、駄目なようで。データ集計に時間がかかり困っています。

差し出がましいお願いで恐縮ですが、マクロの式の例など教えていただくことできないでしょうか？


ご親切にご回答いただきありがとうございます。

お礼日時：2015/01/29 18:29