- ベストアンサー
Excelでハイパーリンクの一括置換方法を教えてください
- Excel2002を使用している際、ハイパーリンクの一括置換用マクロ作成で問題が発生しました。
- 具体的には、Ｃ列の１行目から最終行までのハイパーリンクのみを置き換えたかったのですが、他の列まで一括置換してしまう問題が発生しました。
- Ｃ列だけを一括置換する方法について、ご教示いただけないでしょうか。
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
こんばんは。 私は、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
その他の回答 (1)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 このサンプルは、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に戻す方法というのはどうでしょうか。 上位バージョンでは、そのようなことはないものの、今のバージョンでは、そうした方法しか思いつきません。
補足
こんにちは。 ありがとうございます。 Excel2003の環境は職場にありましたので、５列×５行のデータで再チャレンジしてみました。 Ａ列にハイパーリンク付きテキストを５行用意して、そのＡ列をＢ～Ｅ列へとコピーし、その後 For Each H In Range("C:C").Hyperlinks H.Address = Replace(H.Address, "旧文字列", "新文字列") Next のマクロを実行すると、自宅のExcel2002で困っていた現象と同様にＡ～Ｅ列まで全部置換されてしまったのですが、マクロ実行直前のブックを一旦保存し、再び開いてマクロを実行したら、Ｃ列だけ置換してくれました。 今、自宅のExcel2002で確認しましたが、Excel2003と同様の現象でした。 ということは、列のコピー直後の実行がＮＧということなのでしょうか。 マクロの実行とは無関係のような気がしますが…
お礼
こんにちは、 ありがとうございます。 >列のコピーではなくて、Hyperlink 自体のコピーではないでしょうか。 >コピーすると、オブジェクト自身が、配列を作るので、実際のセルとは >一致しなくなっています。 > >Excel 2002 のHyperlinksの配列状態では、セル位置とは連動していないので、 >そのままでではマクロでの置換は不可能だという結果に至りました。 この『実際のセルとは一致しない』というところでつまづいていたのですね。 そうすると、私の頭の中を変えないとダメなようです。 Ａ～Ｅ列×５行でデータを作成したマクロチェック用の環境で、Ａ列をＢ～Ｅ列にコピーしてからファイルを保存して再度開くとマクロで置換が出来るところまでは先の補足のとおりですが、それでは若干面倒ですので、別起動のブック・別シートの活用など、もうちょっと頑張ってみます。
補足
補足では無いですが、解決策を記す為にこの欄を使用させて戴きます。 sheet1のＡ列にハイパーリンク付きテキストがあるとして マクロで ・sheet1へ移動～Ａ列のコピー～sheet2へ移動～Ａ列へコピー ・sheet1へ移動～Ａ列のコピー～sheet2へ移動～Ｂ列へコピー ・sheet1へ移動～Ａ列のコピー～sheet2へ移動～Ｃ列へコピー ・sheet1へ移動～Ａ列のコピー～sheet2へ移動～Ｄ列へコピー ・sheet1へ移動～Ａ列のコピー～sheet2へ移動～Ｅ列へコピー として、sheet2のＣ列に対してハイパーリンクの一括置換を行うと、他の列に影響せず、Ｃ列だけ置換してくれました。 同じシート内のコピーはＮＧでしたが、他のシートへのコピーでは期待通りの結果になってくれました。