コメントありがとうございます！ ファイル名がバラバラな場合は、「Dir関数」を使って、 フォルダ内のすべてのファイル名を取得する方法を使うといいかと思います。 本動画の最後のVBAコードで、「Dir関数」を使って、 フォルダ内のすべてのファイルからデータを参照するVBAコードは、 次のようになります。 Sub TEST8()
'配列を作成 Dim Arr ReDim Arr(1 To 100, 1 To 3)
Dim A, B, C, D A = ThisWorkbook.Path & "\TEST" 'フォルダパス C = "Sheet1" 'シート名
'ファイル名を取得 Dim FileName FileName = Dir(A & "\*")
i = 0 Do While FileName "" i = i + 1 B = FileName '別ブック名 '数式を配列に入力 For j = 1 To 3 D = Cells(1, j).Address 'セル範囲 '数式を配列に格納 Arr(i, j) = "='" & A & "\[" & B & "]" & C & " '!" & D Next FileName = Dir() Loop
最後の配列に参照セルの数式を入れるのはすごくいい方法ですね。やってみたらとても速かったです。あと質問ですが、シートに書き出す前に値を取得して別の配列にすることはできますか？必要なものをDictionaryに取り込んでから書き出したいのですが。
こんにちは 毎回仕事で使える内容で楽しく拝見しています。
別ブックの値を参照する時に参照先のシート名がバラバラ（一番目のシートなのは共通）の場合はどうしたら良いのでしょうか？教えて頂けると嬉しいです。
コメントありがとうございます！
ファイル名がバラバラな場合は、「Dir関数」を使って、
フォルダ内のすべてのファイル名を取得する方法を使うといいかと思います。
本動画の最後のVBAコードで、「Dir関数」を使って、
フォルダ内のすべてのファイルからデータを参照するVBAコードは、
次のようになります。
Sub TEST8()
'配列を作成
Dim Arr
ReDim Arr(1 To 100, 1 To 3)
Dim A, B, C, D
A = ThisWorkbook.Path & "\TEST" 'フォルダパス
C = "Sheet1" 'シート名
'ファイル名を取得
Dim FileName
FileName = Dir(A & "\*")
i = 0
Do While FileName ""
i = i + 1
B = FileName '別ブック名
'数式を配列に入力
For j = 1 To 3
D = Cells(1, j).Address 'セル範囲
'数式を配列に格納
Arr(i, j) = "='" & A & "\[" & B & "]" & C & " '!" & D
Next
FileName = Dir()
Loop
'配列をセルに入力
Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr
'数式を値に変換
Range("A1").CurrentRegion.Value = Range("A1").CurrentRegion.Value
End Sub
上記のように「Dir関数」を使うと、ファイル名がバラバラの場合でも、
フォルダ内のファイルをすべて参照して、データを取得することができます。
「Dir関数」を使う手順としては、次のような流れになります。
・最初にフォルダ内から1つのファイル名を取得
・Do While文を使って「Dir関数」をループ
・「Dir()」を、変数に入力することで次のファイル名を取得
Dir関数を使って、フォルダ内のすべてのファイル名を取得する方法については、
下記の動画にて解説しております。
ruclips.net/video/_D5uRI9opB0/видео.html
参考になればと思います（^^）
@@IT-gx8sf 返信ありがとうございます。規則性のないファイルでも出来るのですね。この場合、シート名が違ってもエラーにならないのでしょうか？
規則性のないファイルでも「Dir関数」を使えば、ファイル名を取得することができます。
シート名が違う場合は、数式を使った「参照」する方法では、エラーとなってしまうかと思います。
シート名がファイルごとに代わってしまう場合は、ちょっと遅くなってしまうんですけど、「Workbooks.Open」の方法を使うと取得することができます。
@@IT-gx8sf ありがとうございます。別ブックの参照は仕事で必要なので大変参考になりました。これからも動画を楽しみにしています！
@@IT-gx8sf ありがとうございます。別ブックの参照は仕事で必要なので大変参考になりました。これからも動画を楽しみにしています！
初めまして。
今回は有益な動画ありがとうございました。
大変恐縮ですが、ご質問させてください
別ブックを開いた時に、
任意で取得したいシートを選択する方法は、ありますでしょうか？
コメントありがとうございます！
ブックを開いた方法ですと「Workbooks.Open」を使う方法になるかと思います。
ブックを開いている状態で、任意のシートを選択する場合は、
次のように入力することで、選択することができます。
「Workbooks("ブック名").Worksheets("任意のシート名").Select」
例えば、ブック名が「TEST1.xlsx」で、シート名が「Sheet2」の場合は、次のようになります。
「Workbooks("TEST1.xlsx").Worksheets("Sheet2").Select」
参考になればと思います（^^）
こんにちは 数式だとnullが０で帰ってくる様に思うのですが、解決方法ご存じですか？
コメントありがとうございます！
取得データがnullの場合には「0」ではなく、「空欄」にしたい場合は、次のようにIF文を使うといいかと思います。
=IF([TEST.xlsx]Sheet1!A1="","",[TEST.xlsx]Sheet1!A1)
空欄の場合は空欄を入力して、それ以外の場合は値を取得するという感じです。
参考になればと思います（^^）
コメント失礼いたします🙇♀️
このif関数は、VBAのどこに入力すればいいのでしょうか？
初歩的なところで申し訳ないです。。
数式を使って、別ブックを参照する際にシートを選択するウィンドウが表示されてしまうのですが、
これは設定で出力されないようにしているのでしょうか。
それとも別の要因があるのでしょうか。。。
コメントありがとうございます！
ファイル選択用のダイアログが表示される場合は、別ブックを参照する数式に誤りがあるかもしれません。
数式に入力した参照先が見つからないので、ファイルを指定するダイアログが表示されているかと思います。
入力した数式を確認して、ファイル名やシート名が正しいかを確認するとうまくいくかと思います。
参考になればと思います。
@@IT-gx8sf 度々申し訳ありません。セルの列行幅を保持したまま参照することはできますか？自分なりに色々試しましたが出来ませんでした。素人で構文など理解できてないので、テンプレ頂けるとありがたいです。
コメントありがとうございます！
別ブックを参照する方法では、列幅や行の高さを保持したまま参照することは難しいです（^^;）
別ブックの値だけでなく、「列幅」まで取得したい場合は、「Workbooks」を使って別ブックを開いてから、別ブックの値を取得するといいです。
例えば、「TEST2.xlsx」というファイルを作業しているマクロファイルと同じフォルダに保存して、「TEST2.xlsx」の列幅を変更しておきます。
そして、次のVBAコードを実行すると、列幅も含めて値を取得することができます。
Sub TEST1_2()
'別ブックを開く
Workbooks.Open Filename:=ThisWorkbook.Path & "\TEST2.xlsx"
'別ブックの値をコピー
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion.Copy
'「値」の貼り付け
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
'「列幅」の貼り付け
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).PasteSpecial Paste:=xlPasteColumnWidths
ActiveWorkbook.Close False '別ブックを閉じる
End Sub
手順としては、
・別ブックを開く
・別ブックの値をコピー状態にする
・別ブックの「値のみ」を作業しているブックに貼り付け
・別ブックの「列幅」を作業しているブックに貼り付け
・別ブックを閉じる
という流れになります。
さらに、行の高さについても取得したい場合は、「.RowHeight」を使って、行をループして取得します。
別ブックには、3行分だけ値を入力していますので、3行分だけループして、行の高さを取得していきます。
VBAコードは、次のようになります。
Sub TEST1_2()
'別ブックを開く
Workbooks.Open Filename:=ThisWorkbook.Path & "\TEST2.xlsx"
'別ブックの値をコピー
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion.Copy
'「値」の貼り付け
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
'「列幅」の貼り付け
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).PasteSpecial Paste:=xlPasteColumnWidths
'行の高さを取得
For i = 1 To 3
ThisWorkbook.Worksheets("Sheet1").Rows(i).RowHeight = ActiveWorkbook.Worksheets("Sheet1").Rows(i).RowHeight
Next
ActiveWorkbook.Close False '別ブックを閉じる
End Sub
別ブックの値を取得する方法については、次の動画が参考になるかと思います。
↓別ブックの値を取得
ruclips.net/video/LGbU78cc-NM/видео.html
参考になればと思います（^^）
残念ながら業務では使えませんね
100ファイルでも1行しかないのに127秒はかかり過ぎ
業務では30ファイルで1ファイルが4万行以上あります
そんなやり方が有ると言う知識は得られるが、ただ、それだけですね。
参照の場合も20万行に関数を埋め込んで置いたら開くだけでも時間がかかると思いますね
あなたは理解できるから否定のような言い方してると思うんですけど、わからない人のために丁寧に一個ずつ解説されてるじゃないですか
例として
東京から大阪に移動したいとなった時、いろいろな移動手段ありますよね
徒歩、車、新幹線、飛行機 等
人間の手作業のようなコードが徒歩ですよ
時間かかりますけど、こんなコードもありますよという例えじゃないですか
最終的にエクセルに搭載されている機能をフル活用すれば、飛行機で早く行けるように高速化できますよって紹介されてますよね
徒歩のようなコードでもいいから、少しずつ車、新幹線、飛行機と高速化できるようになっていけばいいじゃないですか
なぜ見下すような言い方なのか私には理解できません