Excelでハイパーリンクの一括置換（指定範囲）

Question

現在、Excel2002を使用しておりますが、ハイパーリンクの一括置換用マクロ作成でつまづいておりますので、詳しい方お願いします。

【Excelデータ】
　　Ａ　Ｂ　Ｃ　Ｄ　……
１　★　★　★　★　……
２　★　★　★　★　……
３　★　★　★　★　……
：　：　：　：　：

★マークの箇所のデータにURLの途中まで同じハイパーリンクが貼られています。
リンクはhttp://aaa.bbb.ccc/ddd/eee/…

ここで、Ｃ列の１行目から最終行(約１万行)までのハイパーリンクのみ
ddd→FFFとしてhttp://aaa.bbb.ccc/FFF/eee/…
としたかったので、こちらのサイト検索をしてみると

Sub ハイパーリンクの置きかえ()
　Dim H As Hyperlink
　For Each H In Range("B2:B101").Hyperlinks
　　H.Address = Replace(H.Address, "\abc", "\def")
　Next
End Sub

という回答を見つけることが出来ました。

そこで、それを参考にして

Sub macro1()
　Dim H As Hyperlink
　For Each H In Range("C1:C50000").Hyperlinks
　　H.Address = Replace(H.Address, "ddd", "FFF")
　Next
End Sub
※Ｃ列は約１万行なので５万行まで指定すればよいかと思い、この値にしてます。

とマクロを作成したのですが、

いざ実行してみると、Ｃ列だけではなく、他の列まで全部一括置換してしまいます。

私はＣ列だけを置換したいので、何か良い方法をご存知の方はお願い致します。

Ｃ列だけ他のシートにコピーして一括置換して元の場所に貼り付ける方法も考えたのですが、その方法ですと、Ｄ列を別の文字列に一括置換とかＥ列を更に別の文字列に置換…　とした場合の応用がききませんので…

Wendy02 · Accepted Answer

こんばんは。

私は、Excel2000 だけで試してみていたので、分からなかったのですがご指摘のように、下位バージョンで作ったものは、そのままでは、上位バージョンで行っても同じような現象が得られますね。保存しなおしたら、発生しなくなりました。

>列のコピー直後の実行がＮＧということなのでしょうか。

列のコピーではなくて、Hyperlink　自体のコピーではないでしょうか。コピーすると、オブジェクト自身が、配列を作るので、実際のセルとは一致しなくなっています。

Excel 2002 のHyperlinksの配列状態では、セル位置とは連動していないので、そのままでではマクロでの置換は不可能だという結果に至りました。

以下は、Excel2000 で、試験したマクロです。

このループのラインは複線になっていて、一応、セルの上は走るのですが、Hyperlinks が配列になっていると、セルから、配列の上を走るようになっていて、それに連動して、セルの行の情報(i)を下げているという仕組みになっています。

上位バージョンがあれば、別に試す必要はありませんが、以下のマクロで、ローカルウィンドウで、そのズレを見つけました。もちろん、これで可能なら、直せるはずですが、そうは行きませんでしたね。以下でいうと、bbとdd が、アドレスです。何かの参考になればと思い、ポストしておきます。

'---------------------------------------
Sub Test1()
　　Dim hp As Hyperlink
　　Dim h As Variant
　　Dim rng As Rang
　　Dim i As Integer, k As Integer, j As Long
　　Dim aa As String
　　Dim bb As String
　　Dim cc As String '配列側のHyperlink のアドレス
　　Dim dd As String '　''
　　With ActiveSheet
　　　　Set rng = .Range("C1", .Range("C65536").End(xlUp))
　　　　For i = 1 To rng.Rows.Count
　　　　　　If rng.Cells(i).Hyperlinks.Count > 0 Then
　　　　　　　　Set hp = rng.Cells(i).Hyperlinks(1)
　　　　　　　　On Error Resume Next
　　　　　　　　k = 0
　　　　　　　　k = UBound(hp.Range.Formula)
　　　　　　　　Err.Clear
　　　　　　　　On Error GoTo 0
　　　　　　　　If k = 0 Then
　　　　　　　　　　aa = rng.Cells(i).Address
　　　　　　　　　　bb = hp.Range.Hyperlinks(1).Address
　　　　　　　　Else
　　　　　　　　　　For Each h In hp.Range '←こちらが配列構造になっている
　　　　　　　　　　 j = j + 1　'j は、配列の添え字
　　　　　　　　　　cc = hp.Range.Cells(j).Address & " index: " & j
　　　　　　　　　　dd = h.Hyperlinks(h.Hyperlinks.Count).Address
　　　　　　　　　　Stop '---ストップ(ローカルウィンドウで確認
　　　　　　　　　　Next
　　　　　　　　　　i = i + j
　　　　　　　　　　j = 0
　　　　　　　　End If
　　　　　　　　Stop　'---ストップ　(ローカルウィンドウで確認）
　　　　　　　　 aa = ""
　　　　　　　　 bb = ""
　　　　　　　　 cc = ""　'セルのアドレス
　　　　　　　　 dd = ""　'ハイパーリンクの内容
　　　　　　End If
　　　　Next
　　　　End With
End Sub

Wendy02 · Answer

こんにちは。

このサンプルは、Excel 2003 の仕様だと思います。

　For Each H In Range("B2:B101").Hyperlinks
　　H.Address = Replace(H.Address, "\abc", "\def")
　Next

Excel2002がありませんので、その下位バージョンのExcel2000で試してみました。
かなり何度も繰り返しながら、いろいろ検討してみましたが、結論からすると、マクロからでは不可能でした。もちろん、キーロガーのUSWC などのツールで、ワークシートの表から直すことは可能だとは思いますが、あまりにも効率が悪すぎます。

それは、たぶん、そのハイパーリンクはコピーなどで作られたものだとは思いますが、Hyperlinks オブジェクトとセルとが、マクロのオブジェクトでは連動していないのです。

こちらで作ったサンプルでは、例えば、C3 のハイパーリンク自体が出てこなかったり、セル上にないものが、ハイパーリンクの検査マクロでは、出てきたりしています。これでは、不可能です。

そこで、私のアイデアですが、C列の範囲だけを、Webページとして発行し、それをテキストエディタで、ソースの中でうまく置換して、その　htm(または、html)ファイルを、IEなどで立ち上げ、その範囲を、コピー＆ペーストで、Excelに戻す方法というのはどうでしょうか。

上位バージョンでは、そのようなことはないものの、今のバージョンでは、そうした方法しか思いつきません。

Excelでハイパーリンクの一括置換（指定範囲）

こんばんは。

この回答への補足

こんにちは。

この回答への補足

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング