お世話になっております。
同じフォームのファイルが60個以上あり、各々のファイルを開くことで、数値を下記計算式が入っているエクセルに落とし込んいきたいと思っています。(最終的にピボットテーブルにしてまとめます)
=INDEX(INDIRECT("["&B$2&"]田 中!$C$1:$AB$2000"),M5,N5)
B2には参照先のファイル名が入っています。(例:JAPAN.xlxs)
ただ、参照先のファイルが全てシート名にスペースが入っているため、参照エラーになってしまいます。
スペースを消す方法、もしくはスペースがあったままでも参照できる方法があれば、教えていただきたいです。
参照先のファイルにはパスワードがかかっていて、作成者は教えられないと言っています。
データの集計にコピペしかできず、しかもセル自体にもロックがかかっているので、コピペできる範囲も少なく、とても時間がかかっています。
””を入れるなどやってみましたが、エラーです・・・・。
わかりづらいかと思いますが、ご教示いただけますようよろしくお願いいたします。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
#4の訂正
>シートの選択の時に、トーチの左から、1,2,3
トーチとはなんだろう?たぶんシートのことだと思います。(^^;
Sheet1, Sheet2, Sheet3
1 2 3 ←このこと
No.4
- 回答日時:
こんばんは。
#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
- 回答日時:
こんにちは。
昨日から、試行錯誤を繰り返しました。
マクロを作りながら、ひとつずつ検証してみたのです。簡単にと思っていたのにも関わらず、マクロがどんどん肥大して、考え方を改めて、本日、また挑戦しました。そこで、目的のブックのシート一覧を作ることにしました。
>参照先のファイルにはパスワードがかかっていて、作成者は教えられないと言っています。
最初に、バカなことを聞くようで恐縮なのですが、そのファイルは開けられますでしょうか?
たぶん、「読み取り専用」になるのだと思います。ブックを開くためのパスワードが必要な場合は、一応、掲示板で教えるのは無理です。
なお、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関数の使い方をわかっておらず申し訳ないですが、ご指導くださいますと幸いです。
No.2
- 回答日時:
こんばんは!
お役に立てるかどうか判りませんが・・・
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様のマクロを実施してみていますので、またその後試させていただきます。
親切にアドバイスいただき、本当にありがとうございます。
No.1
- 回答日時:
こんにちは。
=INDEX(INDIRECT("["&B$2&"]田 中!$C$1:$AB$2000"),M5,N5)
試してみましたが、まず、この数式はあっていますでしょうか?
通常、Excelでは、
=INDEX(INDIRECT("'["&B2&"]田 中'!$C$1:$AB$2000"),M5,N5)
のように、'[ブック]シート'! は、シングルクォーテーション(')で括ってあげないといけないはずです。違いを比べてみてください。
それでも、うまくなければ、ごくごく簡単なマクロも考えましょう。
>参照先のファイルが全てシート名にスペースが入っているため、参照エラーになってしまいます。
>スペースを消す方法、もしくはスペースがあったままでも参照できる方法があれば、教えていただきたいです。
そのような便利な方法はありませんが、私自身は、マクロでそのシート名をいくつか用意してヒットさせる方法を使う時があります。特に、数値の半角や全角は、まったく区別がつかないからです。今回の場合は、空白の全角と半角の違いです。また、マクロでは、左側から、順番で番号がついていますから、シート番号を使うこともあります。
WindFaller様
ご回答いただき、ありがとうございます。
この式はシート名が変更できるファイルでデータ集約したものから取ってきているのですが、その際はシングルクオテーションなしでも参照できていました。
スペースがない場合、シングルクオテーションも入れても問題なくできました。
通常は入れるものなのですね、以後そのようにいたします。
スペースがあるシート名のファイルでシングルクオテーションを入れてやってみましたが、やはり参照エラーです。
おっしゃるように、マクロでシート名の候補を挙げるやり方がよいのでしょうか。
私には少々難しそうです。
パスワードを教えてくれれば、解決なのですが、駄目なようで。データ集計に時間がかかり困っています。
差し出がましいお願いで恐縮ですが、マクロの式の例など教えていただくことできないでしょうか?
ご親切にご回答いただきありがとうございます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
別ファイルを開かず、INDIRECT関数を使用せずに、別ファイルのデータを求めたい
Excel(エクセル)
-
エクセルのINDIRECT関数の#REFエラーの対応について
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
今まで文字化けなく開けていたc...
-
スプレッドシートで指定された...
-
ExcelでASCを使って全角を半角...
-
マクロの処理が遅くなった
-
Excel関数について教えてくださ...
-
作成した数式を値として表示し...
-
エクセルの質問です。 F列からL...
-
エクセルでファイルの最終更新...
-
EXCELの散布図で日付が1900年に...
-
【マクロ】VLOOKUPにて参照元に...
-
エクセルのセル内に分数などの...
-
Excelの警告について
-
Microsoft 365Excelの見開きペ...
-
Excel関数について教えてくださ...
-
シートの情報を別のシートへま...
-
Excelマクロで空白セルを詰めて...
-
【マクロ】フォルダ内にあるPDF...
-
index関数の説明をお願いします。
-
重複しない値を取り出したい
-
エクセルの数式バーのフォント...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報