【会員アンケートご協力のお願い】抽選で計5名様に役立つ書籍をプレゼント!

Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
他ブックのリンクの更新
投稿日時: 24/08/03 10:06:04
投稿者: tako552101

テストwin10 2010、本番win10 2016
 
お世話になります。
ノートPC1台で別途モニタを使用し表示拡張して、手元のノートのBook1.xlsmでデータを入力、別モニタBook5.xlsmでBook1.xlsmの情報を計算式で得て簡易速報版のような使い方をしています。
 
テスト環境は2010で、ネット情報など見よう見まねでBook5.xlsmに以下のようなコードでリンク更新できています。
ちなみにBook1.xlsm、Book5.xlsmは同一フォルダ内にあります。

Sub リンク更新()
    
Dim ファイルパス As String
Dim ブック名 As String
Dim 参照値 As String

    ファイルパス = ThisWorkbook.Path
    ブック名 = "Book1.xlsm"
    
    参照値 = ファイルパス & "\" & ブック名
    
    ActiveWorkbook.UpdateLink Name:= _
        参照値, Type:=xlExcelLinks

End Sub

 
2010では@データ入力後Book1.xlsmを保存、ABook5.xlsmでリンク更新(Book1.xlsmから実行)と
2ステップの処理を1つにまとめてうまくいっていました。
 
ところが本番の2016では上記マクロがエラーとなってしまい困っていたものの、その後、入力したデータが即刻更新されるのに気づきました。自動更新?
 
それはそれで現場では問題なかったのですが、例えば、入力したデータを溜めてから更新したい場合もありますので、入力後自動更新させず、Book1.xlsmからの処理で実行させるにはどのようにしたら良いのでしょうか。ご指導いただきますようお願いします。
 
 
 
 
 

回答
投稿日時: 24/08/03 12:34:17
投稿者: simple

「データ」の「ブックのリンク」に「更新の設定」という項目があります。
その内容は把握、確認されていますか?

回答
投稿日時: 24/08/03 13:25:48
投稿者: mattuwan44

確認
 
>Book5.xlsmでBook1.xlsmの情報を計算式で得て
 
というのは、
 
=[Book1.xlsx]Sheet1!A1
というような数式ということですか?
 
運用方法としては、
1)Book5は常に開いておき、表示用に使う
2)同じPCで、必要な時に随時、Book1を開いて、編集後、保存して閉じる。
3)Book1編集時はBook5の表示は変えない
4)任意のタイミングでマクロを使ってBook5の表示を更新する
 
ということでしょうか?
 
そういうことなら、あえてブックに参照式を入力しなくても、
マクロでその都度コピペでもよさそう。。。?

投稿日時: 24/08/03 13:30:38
投稿者: tako552101

データのリンクについては2010と2016で異なるんだろうな、位でこの辺りが把握できてれば、前に進めそうなので教えていただければと思います。

投稿日時: 24/08/03 13:38:29
投稿者: tako552101

mattuwan44さま、ありがとうございます。
 
絶対パスも入れている(双方開いてれば入れる必要ない?)ので、おっしゃる以上に煩雑な式で見にくいので、名前定義をマクロで作成して、その名前で数式を入れています。
 
テスト環境は2010、本番は2016でデータリンクの処置がVBA上でも異なるのか調べていますが、時間がかかりそうなので質問させていただきました。
 
ご回答の「3)Book1編集時はBook5の表示は変えない」「任意のタイミングでマクロを使ってBook5の表示を更新する」は具体的にはどのような処理になるでしょうか。

回答
投稿日時: 24/08/03 13:52:15
投稿者: mattuwan44

2010と2016で仕様に変更が有ったかは、手元に2010がないので確認できませんが、
わたしの勘でいうと、Book1とBook5を同じパソコンで開くと、
リンクの設定によらず値が更新されてしまうかと思います。
 
逆にBook1を閉じている状態でBook5を更新した場合、
バックグラウンドでBook1が開いて再計算されているっぽいです。
 
それを避けるには、数式の再計算を自動から手動に変更すると避けられますが、
手動の設定にすると他の作業の時に不都合がでることがあるので、
好ましくないのかなぁと。
 
そうなると、
Book5側に、
1)画面の更新を止める
2)Book1を開く
3)必要な情報をコピペ
4)Book1を閉じる
5)画面の更新を再開
というようなマクロを用意しておいても、
結果は同じなんじゃないかなぁと思いました。
 
 

回答
投稿日時: 24/08/03 13:54:51
投稿者: mattuwan44

結果は同じというか、
欲しい結果になるのではと思いました。

投稿日時: 24/08/03 14:35:02
投稿者: tako552101

引用:
わたしの勘でいうと、Book1とBook5を同じパソコンで開くと、
リンクの設定によらず値が更新されてしまうかと思います。

おっしゃるとおり、リンクは未設定のまま値が更新されます。
 
前述したとおり、2010で作成して2016で動かした時にはBook1のリンクが既にできていた?ので、今回、一旦すべて削除し、Book1のセル範囲を定義している名前をあらたに作成するマクロを実行。データのリンクは作成されないまま、値の更新が自動的に行われています。
 
教えていただいた方法で運用できそうか、テストしてみます。
 
他にいいアイディアがないかもう少し開けておきます。

回答
投稿日時: 24/08/03 15:19:24
投稿者: mattuwan44

イメージはこんな感じ。。。
 

Option Explicit

Sub test()
    Const sTargetPath As String = "\\Ls220d89c\ls220\元データ.xlsx"
    If ChkFile(sTargetPath) = False Then Exit Sub
    
    Application.ScreenUpdating = False
    ThisWorkbook.Worksheets(1).UsedRange.Clear
    With Workbook.Open(sTargetPath)
        .Worksheets(1).UsedRange.Copy
        ThisWorkbook.Worksheets(1).Range("A1").PasteSpecial xlPasteValues
        .Close False
    End With
    Application.ScreenUpdating = True
End Sub

Private Function ChkFile(ByVal s As String) As Boolean
    If Dir(s) <> "" Then
        ChkFile = True
    Else
        MsgBox "指定のファイルが見つかりません。"
    End If
End Function

 
リンクをいちいち定義しなおすのとどっちが快適かは不明です。

投稿日時: 24/08/04 10:47:22
投稿者: tako552101

mattuwan44さん、概ねやりたいことはできそうです。ありがとうございます。
 
しかしながら、他ブックへのリンク(セル参照)についてその挙動がわからないままなので、テストを行ってました。
 
結論は出ていませんがわかったことは、ver2016(以降?)だと…
 
●名前定義で他ブックのセル参照を行うと、ブックのリンクに「リンクされたBookはない」となるが、実際にはセル参照されていて、しかも自動更新。
 
●他ブックセルの値を直接貼り付け(または入力)すると、ブックのリンクに該当Book名が表示。設定から自動更新するか、更新しないか選べるようになるが、この辺りの一連の処理については少なくても「マクロの記録」では所得できず、VBAとブックのリンクについてWeb検索しても情報が乏しい。
 
いずれ、2010ととでは処理を分ける必要がありそうです。
ブックのリンクはVBAで操作可能なのか?この辺りの仕様をどなたか教えていただけないでしょうか。

投稿日時: 24/09/12 09:04:37
投稿者: tako552101

閉じ忘れました。
みなさん、ありがとうございました。