Last active
February 11, 2018 22:45
-
-
Save wyfinger/68c6b4247d96348e67c40da803620da9 to your computer and use it in GitHub Desktop.
Excel sheet hyperlink check and edit
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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