Check a file exists for hyperlink?

[brisou]

I am currently writing a Excel macro to automatically create an hyperlink

The spreadsheet is as follow
Column 1 = file name
Column 2 = level1
Column 3 = level3
Column 4 = level4
Where level 1/2/3 are the folders and subfolders
Ex a file can be located in
level1/level2/level3/filename
The purpose of the macro is to create an hyperlink for all the file names listed in column 1 (literally hundreds of them)

Building the past is simple and it works. The problem is that the macro never finds the file. Level1 is always located in the same folder as the spreadsheet with the macro is.
I copied the code below

I hope the problem is clear

Thanks

Regards

JB
-----------------------------------------------------------
Sub CmB_hyperlink_Click()

Dim row As Integer
Dim path, starting_dir, file_name, level1, level2, level3, level4 As String
Dim MyPath

starting_dir = ActiveWorkbook.path

row = 9 'row number where the file name column starts
Sheets("Sheet1&quot.Select
Cells(row, 1).Select

Cells(5, 1).Value = starting_dir

Do Until ActiveCell = ""
file_name = Cells(row, 1).Value
level1 = Cells(row, 4).Value
level2 = Cells(row, 5).Value
level3 = CStr(Cells(row, 6).Value)
level4 = Cells(row, 7).Value

If level1 = "" Then
Cells(row, 9).Value = "Level 1 is not specified"
GoTo level1missing
ElseIf level2 = "" Then
path = starting_dir + "\" + level1 + "\" + file_name
ElseIf level3 = "" Then
path = starting_dir + "\" + level1 + "\" + level2 + "\" + file_name
ElseIf level4 = "" Then
path = starting_dir + "\" + level1 + "\" + level2 + "\" + level3 + "\" + file_name
Else
path = starting_dir + "\" + level1 + "\" + level2 + "\" + level3 + "\" + level4 + "\" + file_name
End If

If check_file_exist(CStr(path)) Then
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=path, TextToDisplay:=file_name
Else
Cells(row, 9).Value = "file not found"
End If

level1missing:
row = row + 1
Cells(row, 1).Select
Loop

End Sub

Function check_file_exist(path As String)

Dim file_lenght As Long
file_exist = False
On Error GoTo ErrorHandler
file_lenght = FileLen(path)
If file_lenght > 2 Then file_exist = True
ErrorHandler:

End Function

 

[TopJack]

You appear to have two issues here.

Your function is not returning the required value although its evaluating correctly. Your "file_exist" variable needs to be "check_file_exist" to return the values correctly.

Also you don't need the "TextToDisplay" on the end of the insert hyperlink statement because its already there.

To save you time I have pasted your code here with the amendments :-

Sub CmB_hyperlink_Click()

Dim row As Integer
Dim path, starting_dir, file_name, level1, level2, level3, level4 As String
Dim MyPath

starting_dir = ActiveWorkbook.path

row = 9 'row number where the file name column starts
Sheets("Sheet1&quot.Select
Cells(row, 1).Select

Cells(5, 1).Value = starting_dir

Do Until ActiveCell = ""
file_name = Cells(row, 1).Value
level1 = Cells(row, 4).Value
level2 = Cells(row, 5).Value
level3 = CStr(Cells(row, 6).Value)
level4 = Cells(row, 7).Value

If level1 = "" Then
Cells(row, 9).Value = "Level 1 is not specified"
GoTo level1missing
ElseIf level2 = "" Then
path = starting_dir + "\" + level1 + "\" + file_name
ElseIf level3 = "" Then
path = starting_dir + "\" + level1 + "\" + level2 + "\" + file_name
ElseIf level4 = "" Then
path = starting_dir + "\" + level1 + "\" + level2 + "\" + level3 + "\" + file_name
Else
path = starting_dir + "\" + level1 + "\" + level2 + "\" + level3 + "\" + level4 + "\" + file_name
End If

If check_file_exist(CStr(path)) Then
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=path
Else
Cells(row, 9).Value = "file not found"
End If

level1missing:
row = row + 1
Cells(row, 1).Select
Loop

End Sub

Function check_file_exist(path As String)

Dim file_lenght As Long
check_file_exist = False
On Error GoTo ErrorHandler
file_lenght = FileLen(path)
If file_lenght > 2 Then check_file_exist = True
ErrorHandler:

End Function

Good luck,
TopJack.