Macro Hyperlinking Many Files in a Directory

[Naz1405]

Hello

I have thousands of reports that I wish the user to be able to access via hyper link in an excel sheet.

In the sheet, the report reference number is found in B3: the end of the sheet. I wish the hyper links to be created in C3 onwards.

The reports themselves feature a standard naming convention - "BA #XX".

I want the macro to search in a directory for the part of the text in B3 (wildcard?), and then hyperlink to the file that it finds the match to.

I have started working on this - and came up with this.

Sub update_links()

Dim start_path As String
Dim file_to_find As String
Dim iRow As Integer
Dim iCol As Integer

iRow = 3 'change to 3 if there are headers
iCol = 2 'Column B

start_path = "P:\Engineering\Lab Analysis\Analysis Records" & "\"
analysis_number = ActiveSheet.Range("B" & iRow).Text

file_to_find = analysis_number & "*"

DirFile = Dir(start_path & file_to_find, vbDirectory)

Do While ActiveSheet.Cells(iRow, iCol).Value <> ""

analysis_path = start_path & DirFile

ActiveSheet.Range("B" & iRow).Hyperlinks.Add Anchor:=ActiveSheets.Range("C" & iRow), _
Address:=analysis_path, TextToDisplay:="Link"

'move to the next row
iRow = iRow + 1
Loop

End Sub



I am still not very proficient with VBA, but I am trying to learn. I keep receving error 424 "Object Required" - I can't see what the problem is with the code.

Any insight would be great.

Thanks.

PS I am not exactly sure on the DirFile function - i read another piece of code as inspiration however I dont know if i implemented it correctly.

Thanks.

[Naz1405]

Anyone, would really appreciate it