ハイパーリンクされているファイルの更新日付を取得するユーザー関数
環境:WindowsXpSp3
MS-Excel2003Sp2
ハイパーリンクされているファイルの更新日付を取得するユーザー関数を以下の様に作成しました。
これを、標準モジュールへ登録し、添付画像の様にセルから呼び出すようにしています。
2つ質問があります。
1.リンク先のファイルから更新日付の情報を取ってきているので、
エクセルを自動計算にしていても自動計算されず、"CTRL+SHIFT+ALT+F9"を押さないと
反映されません。
これを、自動計算に対応させる方法は無いでしょうか。
2.1度ハイパーリンクの設定をしたセルに対して、ハイパーリンクの変更
(別のファイルをリンクする)しても、変更後のファイルに対しての日付取得ができません。
試しに、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ベストアンサー
- 回答日時:
[ハイパーリンク]クリックイベントを捉まえる方式で、
'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"
これを1回実行です。
ただし、
>MS-Excel2003Sp2
の環境までです。
2007の[挿入]-[リンク]-[ハイパーリンク]には反応しません。
No.2
- 回答日時:
>回答にあるように、シートモジュールへ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
- 回答日時:
>ユーザー定義関数の実行タイミングの問題なのでしょうか。
ハイパーリンクの変更の場合、リンク先を変える前に自動計算しているようです。
そのため、LastModifiedTimeやLinkFileNameで表示される内容は、変更前のリンク先の内容になっています。
ハイパーリンクの変更でもChangeイベントは発生するので、シートモジュールにChangeイベントを設定し、RangeのDirtyメソッドで再計算すれば大丈夫のようです。
Private Sub Worksheet_Change(ByVal Target As Range)
Range("B1:B2").Dirty
End Sub
Rangeの範囲は必要に応じて変えてください。
この回答への補足
回答、ありがとうございます。
回答にあるように、シートモジュールへWorksheet_Changeを追加してみたのですが、
Changeイベントが発生しません。
Worksheet_Changeへブレークポイントを設定して試してみました。
「ハイパーリンクの編集」ダイアログを開いて、アドレスのみ変更した場合、このブレークポイントにはヒットしません。
しかし、同ダイアログの「表示文字列」を変更すると、ブレークポイントにヒットします。
(数台のPCで試しましたが、同じ結果でした。)
原因がわかりますでしょうか。
または、別の方法がありますでしょうか。
補足の追記です。
Worksheet_SelectionChangeだと大丈夫なのですが、セルを移動するたびにイベントが発生して、目的の結果を得ることができます。
当然といえば当然なのですが。
ただ、セルを移動するたびに再計算してしまうのもどうかと思っています。
これ以外に方法は無いでしょうか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ExcelのVBAコードについて教えてください。 1 2023/08/09 10:33
- Excel(エクセル) Excel VBAどこが間違ってますか? 4 2023/07/17 10:04
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- Visual Basic(VBA) 複数のcsvファイルをExcelに一括変換したい 2 2023/03/03 12:44
- Visual Basic(VBA) このVBAでExcelアプリケーションを作成は必要ですか? 3 2023/07/19 21:13
- Excel(エクセル) VBA フォルダ見える化のコードについて 2 2023/06/19 15:04
- Visual Basic(VBA) エクセルのマクロについて教えてください。 4 2023/07/03 09:11
- Visual Basic(VBA) エクセルのマクロについて教えてください。 7 2023/07/04 09:18
- Excel(エクセル) ユーザー関数の自動計算(excel2003) 1 2023/02/06 06:46
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
このQ&Aを見た人はこんなQ&Aも見ています
-
風水の観点で選ぶ観葉植物とは?置き場所や上げたい運気ごとの注意点を紹介!
観葉植物で運気をアップするコツを、風水デザイン1級建築士の福島昌彦さんに伺った。
-
特定セルの内容を更新したら、その更新日を自動的に表示する方法について
Excel(エクセル)
-
Excel ハイパーリンク先のセルを自動で変更したい
Excel(エクセル)
-
【エクセル】行の最終更新日を、あるセルに自動で入力させる方法。
Excel(エクセル)
-
-
4
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
5
メッセージボックスのOKボタンをVBAでクリックさせたい
Visual Basic(VBA)
-
6
ユーザーフォームに入力したデータを保持する方法
Visual Basic(VBA)
-
7
リンク先のファイルを開かなくても、値が読み込めるようにできますか?(SUMIFSを使ってます)
Excel(エクセル)
-
8
エクセルファイルを開いた回数をカウントし表示させる方法
Excel(エクセル)
-
9
リンク元の日付が空白の時リンク先セルも空白にしたい
Excel(エクセル)
-
10
エクセルVBAでセルに入力したパスでブックを開く
Excel(エクセル)
-
11
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
データが入力されている隣のセ...
-
VBAについて教えてください。 E...
-
エクセルVBA(実行時エラー438...
-
Jtableの特定のセルの背景色や...
-
エクセルテキストボックスの文...
-
メッセージを1度だけ表示したい。
-
エクセルVBA セル選択後にカレ...
-
Excelのセル内にある図形を削除...
-
Windowsで動くVBAがmacOSで動か...
-
EXCELマクロ 保護されているシ...
-
複数の画像をVBAでエクセルに貼...
-
エクセルでの計算式で求められ...
-
エクセル: セルの枠を超えて表示
-
日付だけを変更して印刷(Excel)
-
エクセルのセル内に全角数字を...
-
EXCELで特定のセルに表示...
-
エクセルのセル内の余白の設定...
-
エクセルの2ページ目の作り方
-
エクセルのアポストロフィを一...
-
Excel にて条件付き書式の色に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCELマクロ 保護されているシ...
-
VBAで、貼り付け禁止命令を実現...
-
エクセルテキストボックスの文...
-
データが入力されている隣のセ...
-
VBAで丸をつけたいです。
-
ダブルクリックすると現在の時...
-
メッセージを1度だけ表示したい。
-
エクセルVBA セル選択後にカレ...
-
ハイパーリンクされているファ...
-
Excelについて
-
エクセルVBA(実行時エラー438...
-
Windowsで動くVBAがmacOSで動か...
-
Jtableの特定のセルの背景色や...
-
(VBA)アクティブセルを起点に...
-
VBAについて教えてください。 E...
-
VBA セルに合わせて移動するが...
-
セル色を5秒間隔で変える
-
Excel VBA ダブルクリックで入...
-
Excelのセル内にある図形を削除...
-
Excel VBA セルを指定個数ラン...
おすすめ情報