VLOOKUPの別ブックを参照する。

Question

VLOOKUPで別ブックを参照する。

目的
「加工ブック.xlsm」で「参照ブック.xlsx」を参照する。

加工ブックにこのように入力しました。

A2:J101
=VLOOKUP(ROW()-1,[参照ブック.xlsx]Sheet1!$A$2:$J$101,COLUMN(),0)

両方のブックを開いた状態で、参照することは出来ました。
そして、参照シートを閉じると、
=VLOOKUP(ROW()-1,フルパスの[参照ブック.xlsx]Sheet1!$A$2:$J$101,COLUMN(),0)
と出ます。

その状態では「参照ブック」を参照してくれず、再度「加工ブック」だけ開きなおした時には、「更新してください」→
「参照ブック」を参照した全てのVLOOKUP関数は#N/Aエラーを吐きます。
また、両ブックが開いた状態なら機能します。

あらゆるサイトには「閉じた状態」でも問題なく動くらしいのですが、不思議と動かないので質問しました。
ご回答宜しくお願いします。

※追記
「シート」じゃなくて、「ブック」だから違うというご指摘を受けました。
失礼しました。別ブックの書き間違えです。よろしくお願いします

Comments

[tosi]

>あらゆるサイトには「閉じた状態」でも問題なく動くらしい･･･
初めて聞きました。閉じたBookに対してVLOOKUPしても駄目と思っていました。

[zidaraku]

フルパスで打てば問題ないらしいですが、なんで機能しないのでしょうか…？
https://excel-ubara.com/excel3/EXCEL026.html
https://bsj-k.com/excel-external-references/

[tosi]

https://bsj-k.com/excel-external-references/には確かに
>更新するブックさえ開いておけば、参照される側のブックは
>閉じたままでも構いません。
と記載あります。
ただ、私の認識と経験では閉じた状態では動かないと思っています。

Answer 1

test1.xlsxとtest2.xlsmでテストしてみました。

test2.xlsmにVLOOKUPを記述してみましたが、
test1.xlsxが閉じた状態でも問題なく参照できました。
※リンクを更新してもしなくてもOKでした

Microsoft Office Professional Plus 2016を使っています。

環境要因もあるかもしれないので、
シンプルな状況でテストされるのも良いかもしれません。
参考まで

Answer 2

別シートといいつつ。別Bookですので正常な挙動のような気もします。

Comments

[zidaraku]

誤字です。「別book」に修正しました。

[mako1972]

処理上のお話ですので、誤字とかは気にしていません。
この辺は、エクセル関数で別bookとのリンクで確認メッセージが出たりでなったりとするので
経験上、個人的にはあまりお勧めしません（配布して使うならなおさら信頼性に欠けるような・）。

最近のエクセルですと開いただけでメッセージがでることが多いですよね。
更新を有効しますか？でしたっけ（この変適当ですみません）？

エクセル関数の質問だと思いますので、この辺にしたいと思います。
（業務でしたら、VBAを使うなどしてこの変のエラーを回避したほうがいいという意見です）

閉じた状態とうのは、閉じているようにみえる状態という意味だと思います。

加工ブック.xlsmとしている時点で、マクロですね。。。
マクロ使うなら・・・・。

[tosi]

ミス消します

[zidaraku]

純粋になんで出来ないか？を気になってただけなので、マクロにしました。(笑)

Sub マスターデータ取込01() '選択したファイルを取り込み、別のファイルに貼り付ける。

Dim RC As Integer
Dim OpenFileName, fileName, Path, SetFile As String
Dim wbMoto, wbSaki As Workbook

Set wbMoto = ActiveWorkbook 'マスターデータ取り込み元をセット

Application.DisplayAlerts = False

RC = MsgBox("マスターデータ取込みますか？", vbYesNo + vbQuestion, "確認")

If RC = vbYes Then

OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
'ダイアログボックスを表示して、マスターデータファイルを指定します。

If OpenFileName <> "False" Then
SetFile = OpenFileName
Else
MsgBox "キャンセルされました"
Exit Sub 'マスターデータの取り込みをキャンセル
End If

Workbooks.Open fileName:=SetFile, ReadOnly:=True, UpdateLinks:=0
'ダイアログボックスで指定したマスターデータファイルを開きます。

Set wbSaki = Workbooks.Open(Path & SetFile)

'ワークブック間のシ―トの全てをコピー
wbSaki.Worksheets("sheet1").Range("A1:k1001").Copy
wbMoto.Worksheets("sheet1").Range("A1").PasteSpecial xlPasteFormulasAndNumberFormats

Application.CutCopyMode = False 'コピー切り取りを解除
wbSaki.Close False 'マスターデータ取り込み先のファイルを閉じる

Else

MsgBox "処理を中断します"

End If

Application.DisplayAlerts = True

End Sub

[zidaraku]

皆さまありがとうございました。

[mako1972]

VBAのコード素晴らしいですね
わたしにポイントがついてしまっています。
申し訳ありません。