VBA シート内ハイパーリンク置換がうまくいかない

質問

お世話になります。
今、エクセルシートのセルにあるハイパーリンクの置換をVBAで行おうとしています。　ファイルサーバの移設に伴い、ファイルサーバ内に保存されているエクセルファイル内のハイパーリンクの一部置換を実施すべく、下記のとおり作ってみましたがうまく置換してくれません。。
置換したいエクセルファイルはlist_TM.txtにフルパスでリスト化しており、ファイルを開く→ハイパーリンクがあれば置換→上書き保存して閉じる　の繰り返しを実行しています。
下記を実行したあと、実際にファイルを開いて置換された結果を確認すると、\\server1\tantou\Datasave\sample.xls　→　C:\Datasave\sample.xlsとなってしまっており、当方が所望する\\server2\kyoyu\tantou\Share\Datasave\sample.xlsになっていませんでした。原因がよくわからず質問させて頂いた次第です。
なお途中でTargetRowとTargetColumnを挟んでいますが、この2行はハイパーリンクがある行と列をちゃんと拾ってくれているかどうか確認するために入れたので、無視してい頂いていいかと思います。
お力添えの程、宜しくお願い致します。

Sub Link_OKIKAE()
Dim i As Long
Dim buf As String
Dim AWBN As String
Dim HL As Hyperlink
Open "C:\temp\test\list_TM.txt" For Input As #1
Do Until EOF(1)
Line Input #1, buf
Workbooks.Open buf
AWBN = ActiveWorkbook.Name
For i = 1 To Worksheets.Count
Sheets(i).Activate
For Each HL In ActiveSheet.Hyperlinks
TargetRow = HL.Range.Row
TargetColumn = HL.Range.Column
HL.Address = Replace(HL.Address, "\\server1\tantou", "\\server2\kyoyu\tantou\Share")
Next HL
Next i
Workbooks(AWBN).Save
Workbooks(AWBN).Close
Loop
Close #1
End Sub

回答No.2

kkkkkm · ベストアンサー

Win10 エクセル2013ですが
質問のコードで(list_TM.txtには1個のファイルだけ記載しています)
\\server1\tantou\Datasave\sample.xls
が
\\server2\kyoyu\tantou\Share\Datasave\sample.xls
に変換されました。
実際にリンクされているわけではないので、単にハイパーリンクでリンク先指定の文字列が変換できたということですが、C:\Datasave\sample.xlsとなり変換ができないということにはなりませんでした。なのでコード自体に問題があるのではないかもしれません。環境依存とか。
適当な話ですが、HL.Addressの変換あたりを変えてみたらどうなるんだろうかなとか思いました。HL.Addressを文字列にしてそれを変換してHL.Addressに戻すとか。
あと、個々のファイルを開くのにちょっと時間をあけるとか…すみません適当です。

お礼

kkkkkmさん
ご回答頂きありがとうございました。結局原因がわからず環境依存を疑い、他のPC（win7、Office2010、32bit版）で実行したら置換してくれました。置換できなかったPCはwin10、Office2013、64bit版でした。64bit版Officeでマクロが動かない問題が取り沙汰されている中で、今回の真因かどうかは分かりませんが、もしかしたらこれが原因かも・・・と思った次第です。　またHyperLinkを予め文字列変換した上で、Cells(TargetRow,TargetColumn)に直接Addしてみたり・・・と、いろいろと試してみましたが結局のところよくわかりませんでした。　環境依存を疑う方向へのヒントを頂けたkkkkkmさんのご回答をベストアンサーにさせて頂こうと思います。ありがとうございました。

回答No.1

nishi6

ファイルサーバーとかの環境はないので、ハイパーリンク先のフォルダーを書き換えるVBA（質問にあるReplace）を作ってみました。

ハイパーリンクが含まれているBookの設定ですが、

ファイルタブ
　＞情報
　　＞プロパティ
　　　＞詳細プロパティ
　　　　＞Book1プロパティダイアログ
　　　　　＞ファイルの概要タブ
　　　　　　＞ハイパーリンクの起点　で、

ハイパーリンクの起点が「未入力」の場合、置き換えできず。
ハイパーリンクの起点が「￥」（半角円マーク）の場合、置き換えできました。

「￥」（半角円マーク）の場合にはもう少し説明が必要で、
おなじBookで、起点が「未入力」のときに作ったハイパーリンクは、置き換えできず、起点を「￥」に変更した後に設定したハイパーリンクは変更できました。起点の有無が何か関係があるかもしれません。「￥」（半角円マーク）入力後のハイパーリンクのAddressプロパティにはリンク先Bookのフルパスが確認できました。

当方、Win10、Excel2010です。何分、環境が違うので、参考程度にしてください。見当違いかもしれませんので。解決すればいいですね。

お礼

nishi6さん
ご回答頂きありがとうございました。結局のところ原因はわからずじまいでした。ハイパーリンクの起点という設定の存在を初めて知りました。　ご教示頂いた設定方法から考えReplaceする文字列の最初の2文字「¥¥」を省き、「server1・・」、「server2・・」と書いてみたり、他にもReplaceする前段階で「\」を「¥」にReplaceしてみたり・・と、いろいろ試してみましたがうまくいきませんでした。もう少し探ってみます。ありがとうございました。