VBA code for Hyperlinking to files in excel

[Talat]

Hi,

I have a file

File1- which has in column A Invoice numbers

AND

and a directory with subdirectories which holds files whose names corresponds to the data
iheld in teh rows of Column A from File one. Both files grow as data is added to each.

I would like to run a script which will create a hyperlink for each columA Row data and the corresponding file in the directories. For subsequent runs of teh code, teh code should check if there is alraedy a hyperlink and skip. Is this feasible, or am I being too ambitious?

Thanks for any advice and help.

Talât

 

[pedie]

Not sure if i understood you correcty but...
below is adding hyperlink example....
All the best

Sub Macro7()
Dim Xstr As String
Dim Ystr As String
Dim i As Long
Dim lr As Long
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Ystr = Range("C" & i).Text
Xstr = Ystr & Range("B" & i).Text
Range("C" & i).Hyperlinks.Add Anchor:=Range("C" & i), Address:=Xstr, TextToDisplay:=Range("A" & i).Text
Next i
End Sub

<TABLE style="WIDTH: 390pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=519><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6802" span=2 width=186><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16 width=64></TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl65 width=83>A</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 140pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl76 width=186>B</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 140pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=186>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl65 height=17>1</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl65>FILE NAME</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl76>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>Hyperlink</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=16>2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68 height=15>3</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=15>4</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68 height=15>5</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=15>6</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68 height=15>7</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=15>8</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68 height=15>9</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=15>10</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68 height=15>11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=15>12</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68 height=15>13</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=15>14</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68 height=15>15</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl68>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl71>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=15>16</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl67>Book1.xlsm</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl70>FODLER PATH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>Book1.xlsm</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl69 height=15>17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl69>Book1.xlsm</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: white 0.5pt solid" class=xl72>FODLER PATH</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>Book1.xlsm</TD></TR></TBODY></TABLE>

 

[Talat]

Hi Pedie,

Thanks , but not quite what I was looking for.

File 1 which has the invoice no. that should be hyperlinked has the following structure and has a different path to the file it should be linked to.

Inv. No Invoice Date Account Code Section Name

P 001113 18-Jul-94 30001 AI Germany
P 001114 21-Jul-94 22510 AI USA (NY)
P 001115 05-Sep-94 23220 AI Japan Section
P 001116 05-Sep-94 24130 AI Austrian Section


Column A rows has file name ie P001113.xls which should be linked.

The target file will be in a directory with a different patch and the file may be in one of the many different subdirectories.... eg

C:\INVOICES\INVOICE ARCHIVE\SS2245
C:\INVOICES\INVOICE ARCHIVE\SX5021
C:\INVOICES\INVOICE ARCHIVE\SS4450
C:\INVOICES\INVOICE ARCHIVE\SX1203 etc
and within each subdirectory there will be many different files

Hope this makes a bit more sense.

Thanks.

 

[Talat]

OK.

I have made some progress with this. The code below now hyperlinks filenames from one file to the corresponding files in another directory.

Option Explicit

Sub Hyperlink()
Dim lastRow As Long
Dim folderPath As String
Dim cell As Range
Dim i As Long

    folderPath = "C:\Users\Talât\Desktop\INVARCHIVE\" 'enter server path here
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
    For i = 1 To lastRow
    Range("A" & i).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=folderPath & Cells(i, 1) & ".xls"
        Selection.Font.Bold = True
    Selection.Font.Underline = xlUnderlineStyleNone
    Selection.Font.ColorIndex = 5
    
    Next i
    MsgBox "Linking is complete."
    End Sub

However, the files being linked are in different directories withing one parent directory and I need the code to search through all teh directories until it find the right file to link to.

It would also be good if it failed to find the right file to link to if the code would show a message and not create a false link as it now does.

I am not sure how I can attached the simple work sheets to show what I mean, but I hope the above is clear enough.

Thanks for any help I you can give me on this.

Talât