Macro hyperlink folder and file long list

[Anny Hahn]

Each week I download a dashboard with all my contracts, 500 contracts. I will like to add a hyperlink using a Macro to open the folder on C:\temp\ based on a contract listed on cell A1, then open the excel file with the same name on cell A1 and so on until I reach a blank cell.
example dashboard
cell contract
A1 Test_1
A2 1257
A3 M123/234
A4 P1234 00
A5 P1234 01
A6 P1234 02
etc

example of C:\temp\
folder excel name.xlxs
Test_1 Test_1.xlxs
1257 1257.xlxs
M123_234 M123_234.xlsx
P1234 P1234 00.xlsx
P1234 P1234 01.xlsx
P1234 P1234 02.xlsx


Second part sometimes the contract have "/" and the folder and the excel file.xlsx can not be saved with the back-slash and a _ is added in the "/"'s place or sometimes a "space" or just omitted all together. It all depends who is saving the spreadsheets

Third part sometimes contracts have orders but they are saved on the same folder. The folder will NOT have the order part. If we can just open the folder will be good enough.

I have used the hyperlink formula but contracts change from week to week. i read all the forums to see if anyone else had the same problem but nothing.

Thank you so much for any direction you can give me. I'm just a beginner creating macros.

 

[Anny Hahn]

Sub AddHyperlink()
'declare variables

'Insert a new row
'Sheets("ZANALYSIS_PATTERN1").Range("c1").Select
ActiveCell.EntireColumn.Insert shift:=xlDown

'Copy path

ActiveCell.EntireRow.Cells(1, "c") = "c:\temp\" & Cells(1, "b")

Range("c2:C500").Value = Range("b2:b500").Value

'create hyperlinks
Range("b2").Select
For i = 2 To 500

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="c:\temp\"

ActiveCell.Offset(1, 0).Select
Next i
End Sub

----------------------------------------------------------
That is how far I got on the code but it only opens the C drive and not the excel (.xlxs) spreadsheet.
and I have tried everything I know and nothing works