ハイパーリンクされているファイルの更新日付を取得するユーザー関数

質問

ハイパーリンクされているファイルの更新日付を取得するユーザー関数

環境：WindowsXpSp3
MS-Excel2003Sp2

ハイパーリンクされているファイルの更新日付を取得するユーザー関数を以下の様に作成しました。
これを、標準モジュールへ登録し、添付画像の様にセルから呼び出すようにしています。

２つ質問があります。

１．リンク先のファイルから更新日付の情報を取ってきているので、
　　エクセルを自動計算にしていても自動計算されず、"CTRL+SHIFT+ALT+F9"を押さないと
　　反映されません。
　　これを、自動計算に対応させる方法は無いでしょうか。

２．１度ハイパーリンクの設定をしたセルに対して、ハイパーリンクの変更
　　（別のファイルをリンクする）しても、変更後のファイルに対しての日付取得ができません。
　　試しに、LinkFileName関数で"Hyperlinks(1).Address"を表示してみると、
　　セルへは変更後のファイル名が表示されているのですが、"Hyperlinks(1).Address"の内容は
　　変更前のままで変わっていませんでした。
　　変更後のリンクアドレスを取得する方法は無いでしょうか。
　　（ユーザー定義関数の実行タイミングの問題なのでしょうか。）

よろしくお願いします。

-----------------------------------------------------------------------------
リンク先の更新日付を取得する関数
-----------------------------------------------------------------------------
Function LastModifiedTime(targetRange As Range) As Double
Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
LastModifiedTime = FSO.GetFile(targetRange.Hyperlinks(1).Address).DateLastModified
Set FSO = Nothing
End Function
-----------------------------------------------------------------------------
リンク先のファイル名（パス名）を取得する関数
-----------------------------------------------------------------------------
Function LinkFileName(targetRange As Range) As String
LinkFileName = targetRange.Hyperlinks(1).Address
End Function
-----------------------------------------------------------------------------

回答No.3

end-u · ベストアンサー

[ハイパーリンク]クリックイベントを捉まえる方式で、

'ThisWorkbookModule
Option Explicit

Private WithEvents cLink1 As Office.CommandBarButton
Private WithEvents cLink2 As Office.CommandBarButton

Private Sub cLink1_Click(ByVal Ctrl As Office.CommandBarButton, _
　　　　　　　　　　　　 CancelDefault As Boolean)
　　Application.OnTime Now, "fCalculate"
End Sub

Private Sub cLink2_Click(ByVal Ctrl As Office.CommandBarButton, _
　　　　　　　　　　　　 CancelDefault As Boolean)
　　Application.OnTime Now, "fCalculate"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　Set cLink1 = Nothing
　　Set cLink2 = Nothing
End Sub

Private Sub Workbook_Open()
　　Set cLink1 = Application.CommandBars.FindControl(ID:=1576)
　　Set cLink2 = Application.CommandBars.FindControl(ID:=1577)
End Sub
'-----------------------------------------------------------
'標準Module
Option Explicit

Sub fCalculate()
　　ThisWorkbook.Sheets("sheet1").Range("B1:B2").Dirty
End Sub

Sub kLink()
　　Dim x As Long

　　If ActiveCell.Hyperlinks.Count = 0 Then
　　　　x = 1576
　　Else
　　　　x = 1577
　　End If
　　With Application
　　　　.OnTime Now, "fCalculate"
　　　　.CommandBars.FindControl(ID:=x).accDoDefaultAction
　　End With
End Sub

こんな感じでいけるかもしれません。
ショートカットキー[Ctrl]+[k]対策は Sub kLink にキーを割り当てます。
手作業もしくは
Application.MacroOptions Macro:="kLink", ShortcutKey:="k"
これを１回実行です。

ただし、
>MS-Excel2003Sp2
の環境までです。
2007の[挿入]-[リンク]-[ハイパーリンク]には反応しません。

お礼

回答、ありがとうございます。

遅くなってしまい申し訳ありません。

この方法を採用させていただきました。

回答No.2

nag0720

＞回答にあるように、シートモジュールへWorksheet_Changeを追加してみたのですが、
＞Changeイベントが発生しません。

失礼しました。
セルの文字列が空白でハイパーリンクだけが設定されているとChangeイベントは発生しますが、両方ある場合はChangeイベントは発生していないようです。

SelectionChangeを使う場合は、Target変数を利用して、移動前のセルがハイパーリンクのセルかどうかを判定する方法があります。
ただし、この方法はハイパーリンクを変更したあとにセルを移動させないとイベントが発生しないのでご注意を。

Public SRow As Integer
Public SCol As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If SRow = 1 And SCol = 1 Then
Range("B1").Dirty
ElseIf SRow = 2 And SCol = 1 Then
Range("B2").Dirty
End If
SRow = Target.Row
SCol = Target.Column
End Sub

別の方法として、ハイパーリンクのファイル名が変わったかどうかを判定する方法もあります。

Public Filename1 As String
Public Filename2 As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Hyperlinks(1).Address <> Filename1 Then
Range("B1").Dirty
Filename1 = Range("A1").Hyperlinks(1).Address
ElseIf Range("A2").Hyperlinks(1).Address <> Filename2 Then
Range("B2").Dirty
Filename2 = Range("A2").Hyperlinks(1).Address
End If
End Sub

ハイパーリンクを設定している場所がたくさんあったり、場所が不定だったりした場合は、Filename1,Filename2の代わりに別シートを利用する方法もあります。

お礼

返事が遅くなってしまい、申し訳ありません。

たびたびありがとうございます。

回答No.1

nag0720

＞ユーザー定義関数の実行タイミングの問題なのでしょうか。

ハイパーリンクの変更の場合、リンク先を変える前に自動計算しているようです。
そのため、LastModifiedTimeやLinkFileNameで表示される内容は、変更前のリンク先の内容になっています。

ハイパーリンクの変更でもChangeイベントは発生するので、シートモジュールにChangeイベントを設定し、RangeのDirtyメソッドで再計算すれば大丈夫のようです。

Private Sub Worksheet_Change(ByVal Target As Range)
Range("B1:B2").Dirty
End Sub

Rangeの範囲は必要に応じて変えてください。

お礼

補足の追記です。

Worksheet_SelectionChangeだと大丈夫なのですが、セルを移動するたびにイベントが発生して、目的の結果を得ることができます。
当然といえば当然なのですが。

ただ、セルを移動するたびに再計算してしまうのもどうかと思っています。

これ以外に方法は無いでしょうか。

補足

回答、ありがとうございます。

回答にあるように、シートモジュールへWorksheet_Changeを追加してみたのですが、
Changeイベントが発生しません。

Worksheet_Changeへブレークポイントを設定して試してみました。
「ハイパーリンクの編集」ダイアログを開いて、アドレスのみ変更した場合、このブレークポイントにはヒットしません。
しかし、同ダイアログの「表示文字列」を変更すると、ブレークポイントにヒットします。
（数台のＰＣで試しましたが、同じ結果でした。）

原因がわかりますでしょうか。
または、別の方法がありますでしょうか。