Sub ハイパーリンク一括抽出()
Dim rootPath As String
Dim oldCalc As XlCalculation
Dim oldSecurity As Long
Dim oldEvents As Boolean
Dim oldScreen As Boolean
Dim resultName As String
'設定シートのB1セルから取得
rootPath = Trim(CStr( _
ThisWorkbook.Worksheets("設定").Range("B1").Value))
'入力チェック
If rootPath = "" Then
MsgBox "B1セルにフォルダーパスを入力してください。", _
vbExclamation
Exit Sub
End If
Set fso = CreateObject("Scripting.FileSystemObject")
'検索対象フォルダーの確認
If Not fso.FolderExists(rootPath) Then
MsgBox "指定フォルダーが見つかりません。" & _
vbCrLf & rootPath, vbExclamation
Exit Sub
End If
oldCalc = Application.Calculation
oldSecurity = Application.AutomationSecurity
oldEvents = Application.EnableEvents
oldScreen = Application.ScreenUpdating
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.AutomationSecurity = 3
'結果シート作成
resultName = "リンク一覧_" & Format(Now, "hhmmss")
Set wsResult = ThisWorkbook.Worksheets.Add
wsResult.Name = resultName
wsResult.Range("A1:G1").Value = Array( _
"フォルダーパス", _
"ファイル名", _
"シート名", _
"セル位置", _
"表示内容", _
"リンク先", _
"有効なパスか")
outputRow = 2
scannedFiles = 0
foundLinks = 0
'サブフォルダーを含めて検索
SearchFolder fso.GetFolder(rootPath)
'結果シートの整形
With wsResult
.Rows(1).Font.Bold = True
.Rows(1).AutoFilter
.Columns("A:G").AutoFit
End With
MsgBox "抽出完了" & vbCrLf & _
"検索ファイル数:" & scannedFiles & vbCrLf & _
"抽出リンク数:" & foundLinks, vbInformation
CleanUp:
Application.ScreenUpdating = oldScreen
Application.EnableEvents = oldEvents
Application.Calculation = oldCalc
Application.AutomationSecurity = oldSecurity
Exit Sub
ErrorHandler:
MsgBox "エラー:" & Err.Description, vbExclamation
Resume CleanUp
End Sub
Register as a new user and use Qiita more conveniently
- You get articles that match your needs
- You can efficiently read back useful information
- You can use dark theme