Skip to content

Instantly share code, notes, and snippets.

@wyfinger
Last active February 11, 2018 22:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save wyfinger/68c6b4247d96348e67c40da803620da9 to your computer and use it in GitHub Desktop.
Save wyfinger/68c6b4247d96348e67c40da803620da9 to your computer and use it in GitHub Desktop.
Excel sheet hyperlink check and edit
VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} HyperlinkCheckForm
Caption = "Hyperlink"
ClientHeight = 1260
ClientLeft = 120
ClientTop = 450
ClientWidth = 17070
OleObjectBlob = "HyperlinkCheckForm.frx":0000
StartUpPosition = 2 'CenterScreen
End
Attribute VB_Name = "HyperlinkCheckForm"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub CommandButton1_Click()
rez.Caption = 1
Me.Hide
End Sub
Private Sub CommandButton2_Click()
rez.Caption = 0
Me.Hide
End Sub
Private Sub CommandButton3_Click()
rez.Caption = -1
Me.Hide
End Sub
Private Sub TextBox1_Change()
If CheckFileExists(TextBox1.Text) Then
Me.Label1.Caption = "ФАЙЛ СУЩЕСТВУЕТ"
Me.Label1.ForeColor = RGB(0, 255, 0)
Else
Me.Label1.Caption = "ФАЙЛ НЕ СУЩЕСТВУЕТ"
Me.Label1.ForeColor = RGB(255, 0, 0)
End If
End Sub
Private Sub UserForm_Click()
End Sub
Attribute VB_Name = "HyperlinkCheckModule"
Public Declare PtrSafe Function PathFileExists Lib "shlwapi.dll" Alias "PathFileExistsA" (ByVal pszPath As String) As Long
Function CheckFileExists(filename As String) As Boolean
If PathFileExists(filename) Then
CheckFileExists = True
ElseIf PathFileExists(ActiveWorkbook.Path & "\" & filename) Then
CheckFileExists = True
Else
CheckFileExists = False
End If
End Function
Sub HyperlinkCheck()
'
' Проходим гипперссылкам листа и проверяем все
Dim addr As String
Dim exists As String
Dim countChanged As Integer
countChanged = 0
For Each h In ActiveSheet.Hyperlinks
addr = h.Address
If CheckFileExists(addr) Then
Else
h.Range.Select
HyperlinkCheckForm.Label1.Caption = "ФАЙЛ НЕ СУЩЕСТВУЕТ"
HyperlinkCheckForm.TextBox1.Text = addr
HyperlinkCheckForm.rez.Caption = 0
HyperlinkCheckForm.Show
If HyperlinkCheckForm.rez.Caption = 1 Then
h.Address = addr
countChanged = countChanged + 1
ElseIf HyperlinkCheckForm.rez.Caption = -1 Then
GoTo break
End If
End If
Next h
break:
MsgBox ("Работа окончена, всего ссылок " & ActiveSheet.Hyperlinks.count & ", изменено " & countChanged)
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment