☰

ハイパーリンク内のテキストの追加または置換

tag-icon • tag-icon microsoft-excel macros find-and-replace vbscript
ハイパーリンク内のテキストの追加または置換

これをどうやって実行できるか調べてみましたが、とても混乱しています。とても簡単ですが、まだ解決策が見つかりません。

私が知りたいのは次のことです。3 つの別々のドキュメントがあり、それらはすべてある程度リンクされています。データはドキュメント (1. マスター入力) から入力され、3 つのスプレッドシートにまとめられます。

各ドキュメントは同じようにレイアウトされており、唯一の可変要素は日付であり、マスター入力には数値があります。

図で表すと次のようになります。

a. 1 マスター入力 --> (入力) 2、3 b. 2 --> (入力) 3 と 4 c. 3 --> (入力) 4

私が知りたいのは、文書 2、3、4 ではデ​​ータが毎年変わるということです。例として、スプレッドシートの「ハイパーリンク」が多数あります。

2015 =「\日次収益\2015[収益 2015.xls]年間概要'!$C$160」

2016 =「\日次収益\2016[収益 2016.xls]年間概要'!$C$160」

2017 =「\日次収益\2017[収益 2017.xls]年間概要'!$C$160」

上記から変更する必要がある唯一の変数は年ですが、それを置き換えるために使用できるコード/マクロなどはありますか？年ハイパーリンク内にファイルパスがいつも同じまま。

私は十分に認識しています検索と置換機能ですが、ユーザーフレンドリーで簡単なものにしたいと思っています。

助けてください、ありがとう！

答え1

まず、あなたはハイパーリンクについて語り続けています。あなたが言っているのは、他のファイルを参照する数式だと思います。それらはハイパーリンクではありません。あなたが本当にハイパーリンクについて言っているのであれば、解決策は次のとおりです。

実際の質問にお答えすると、はい、そのためのマクロがあります。もちろんです。知っておく必要があるのはオブジェクトだと思いますWorksheet.Hyperlinks。各ワークシートの各ハイパーリンクをループし、アドレスを任意のものに置き換えることができます。次にスニペットの例を示します。

Sub ChangeHyperlinks()
    Dim ws As Worksheet
    Dim hyp As Hyperlink
    For Each hyp In ws.Hyperlinks
        hyp.Address = Replace(hyp.Address, "2015", "2016")
    Next
End Sub

変更したくない場合は修正する必要があります毎のような条件文を追加することでハイパーリンクを作成If InStr(1, hyp.Address, "\Daily Revenue\2015") Thenできます。Workbook_Openファイルが開かれるたびにイベントを実行し、ファイル名に基づいて正しい年を取得し、ハイパーリンクの置換を実行できます。はい、これは実行されます毎ファイルが既に正しい場合でも、ファイルを開くときにエラーが発生します。ハイパーリンクの数によっては、これで問題ない場合もあれば、すべてが停止してしまう場合もあります。

実際に数式を意味している場合、ここにコード スニペットがあります。繰り返しますが、必要に応じてさらに調整する必要がありますが、これで開始できるはずです。

Private Sub Workbook_Open()
    Dim i As Integer
    Dim srcArray As Variant
    srcArray = ThisWorkbook.LinkSources(xlLinkTypeExcelLinks)
    For i = 1 To UBound(srcArray)
        Debug.Print srcArray(i)
        ThisWorkbook.ChangeLink srcArray(i), Replace(srcArray(i), "2015", "2016")
    Next
End Sub

また、数式についておっしゃっているのであれば、あなたの質問はスタックオーバーフロー。

答え2

質問には曖昧な点がいくつかあります。この回答では、ハイパーリンクではなく、例に示されているように他のワークブックのセル参照について話しているものと想定しています。また、タスクは、毎年すべての数式を書き換えるのではなく、参照を年ごとに調整可能に設定することであると想定しています。

まず、どこかに年参照が必要です。年をいつ、どのような方法で変更したいのかが明確ではありません (毎年 1 月 1 日に自動的に変更しますか? 選択した日付に? いつでも年を変更できるようにしたいですか?)。そこで、一般的なソリューションを基にします。年参照を保持するセルを選択します。私の例では、例のZ1セル参照を含むワークシートのセルを使用します。年を任意に変更したり、選択した日付に変更したりできるようにしたい場合は、Z14 桁の年を入力するだけです。1 月 1 日に年を自動的に変更したい場合は、を使用します=YEAR(TODAY())。

次に、各数式に年をハードコーディングする代わりに、参照を連結しZ1、INDIRECT 関数を使用してテキスト文字列をセル参照に変換します。例:

          '\Daily Revenue\2015[Revenue 2015.xls]Year Summary'!$C$160   becomes
INDIRECT("'\Daily Revenue\"&Z1&"[Revenue "&Z1&".xls]Year Summary'!$C$160")

既存のワークシートでは、セル参照を変更するために 1 回だけ変更を加える必要があります。その後は、エントリを変更することでいつでも年を変更できますZ1。

関連情報