[[20250820154851]]　『参照先変更』（ろろ）　ページの最後に飛ぶ

『参照先変更』（ろろ）

初めまして
ブックの参照先を自動で変更できるマクロコードがないか質問です。
サンプルは1-0というブック名と2-0というブック名でそこから1-1,1-2,1-3…と複数ブックがあります。
2-0のサンプルブックに1-0からxlookupで数字を参照していますが2-1,2-2,2-3…と「1-〇」と同じ数のブックを作成しなければなりません。
この参照先を「2-〇」のA1に数字を入れたらその数字のブックに自動で変わらないかなあと思っています。
A1に「1」と入っていたら1-０を1-1に変更してその後参照は消して数字の値のみになります。
置換にして参照になっている場所をコピー→値貼付になると思いますが2のブックも複数シートがあり、数も多いので全シート該当箇所をコピーしてというのが時間を取られていて…
マクロでも関数でも何かいい案をお持ちでしたら教えていただけると助かります。
よろしくお願いいたします。

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

 ちょっとよくわからないのですが「何かいい案を」とのことなので、ちょっとだけ。

 ＞参照になっている場所をコピー→値貼付になると思いますが・・・数も多いので

 数式の参照先が別のブックであれば、リンクの解除をすることによってすべて数式から数値に変わります。
　
（純丸） 2025/08/20(水) 20:19:53

 私もよくわかりませんけど、
 「データ」　‐　「ブックのリンク」　‐　「ソースの変更」で
 参照しているブックを変更できますよ。
 別にセル単位の変更は一切不要で、変更後のブックを指定するだけです。

 もちろんマクロに仕立てることもできるでしょう。
 マクロ記録を取れば、ChangeLinkメソッドを使ったコードが得られるはずです。

（xyz） 2025/08/20(水) 21:32:41

純丸さん、xyzさあん、分かりづらい文章ですみません
サンプルブックから参照先を変えただけのブックが複数必要で参照先変更→値のみ貼り付けの作業だけの作業ですが作成するブックが多いので任意のセル（今回はA1）に番号を入れてマクロ実行をしたら参照先が入力した数字のブックに変わって値貼り付け迄ならないかなと手抜きを考えてしまいました。
xyzさんと純丸さんの組み合わせでも作業はだいぶ減りそうですのでやってみます。
ありがとうございました。
（ろろ） 2025/08/21(木) 05:59:27

話がよく判りませんが、任意のセル（今回はA1）にブック名を書いておき、マクロで

 1. そのブックを開く
 2. セル（範囲）をコピーする
 3. 値貼り付けする

ということは可能です。
数式の参照ブック名を変えたいのか、値貼り付けするときのデータもとブックを切り替えたいのか、どちらでしょうか？

（もこな２） 2025/08/21(木) 12:51:35

 | サンプルは1-0というブック名と2-0というブック名でそこから1-1,1-2,1-3…と複数ブックがあります。
 | 2-0のサンプルブックに1-0からxlookupで数字を参照していますが2-1,2-2,2-3…と「1-〇」と同じ数のブックを作成しなければなりません。
 実行したいことは以下のようなことなんですか？

 2-0.xlsx はそのなかで1-0.xlsxを参照しています。
 参照先ブックを1-1.xlsxに変更したものを 2-1.xlsxという名前で保存し、
 参照先ブックを1-2.xlsxに変更したものを 2-2.xlsxという名前で保存し、
 参照先ブックを1-3.xlsxに変更したものを 2-3.xlsxという名前で保存し
 ・・・
 ということなんですか？
 単なる確認ですが。

（xyz） 2025/08/21(木) 15:30:26

 ＞ xyzさんと純丸さんの組み合わせでも作業はだいぶ減りそうですのでやってみます。

 書いてみました。
 リンクの変更の際に時間がかかるようだとだめかもしれません。

 Sub test()
    Const lnkPrefix As String = "1-"
    Const savePrefix As String = "2-"
    Dim lnkCurrent As String, lnkUpdate As String
    Dim lnkFolder As String
    Dim saveFolder As String: saveFolder = ThisWorkbook.Path & "\"
    Dim e, fileName As String, n As Long

    For Each e In ThisWorkbook.LinkSources(xlExcelLinks)
        If e Like "*\" & lnkPrefix & "*.xlsx" Then
            lnkCurrent = e
            lnkFolder = Left(e, InStrRev(e, "\"))
            Exit For
        End If
    Next

    fileName = Dir(lnkFolder & lnkPrefix & "*.xlsx")
    Do While fileName <> ""
        n = Val(Split(fileName, "-")(1))
        If n <> 0 Then
            lnkUpdate = lnkFolder & fileName
            ThisWorkbook.Worksheets.Copy
            With ActiveWorkbook
                .ChangeLink lnkCurrent, lnkUpdate
                .BreakLink lnkUpdate, xlExcelLinks
                .SaveAs saveFolder & savePrefix & n, xlOpenXMLWorkbook
                .Close False
            End With
        End If
        fileName = Dir()
    Loop

 End Sub
（マナ） 2025/08/23(土) 13:56:08

 ↑マクロは、2-0.xlsm　に保存する前提です。
（マナ） 2025/08/23(土) 14:07:17

コメント返信：

[ 一覧(最新更新順) ]

[[20250820154851]] 『参照先変更』（ろろ） ページの最後に飛ぶ

[[20250820154851]]　『参照先変更』（ろろ）　ページの最後に飛ぶ