0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Excel ハイパーリンク抽出マクロ

0
Last updated at Posted at 2026-10-20

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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?