to automate cells with a hyperlink

[Guido]

Does anybody know how i can automate a process like:

i have a column with the following data:

D00101
D01200
D03219

and so on

I have a directory with pdf files they are called:

00101.pdf
01200.pdf
03219.pdf

Now i like to have that the column with D00101 a hyperlink has with the corresponding pdf file, in this case 00101.pdf and so on.

Is there a vba script for.

Greeting Guido

[Bob Phillips]

Public Sub ProcessData()
Dim iLastRow As Long
Dim i As Long
Dim sFile As String

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow 'iLastRow To 1 Step -1
sFile = Right(.Cells(i, "A").Value, Len(.Cells(i, "A")) - 1)
.Hyperlinks.Add Anchor:=.Cells(i, "A"), _
Address:=sFile & ".pdf", _
TextToDisplay:=.Cells(i, "A").Value
Next i

End With

End Sub

[Guido]

I'm a newby with vba, What does "A" mean, and where do i give the directory where all the pdf's reside

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== the column where the data resides, _
'change to suit
Const TARGET_DIR As String = "C:\MyDir\" '<=== the root directory for the pdf files, _
'change to suit
Dim iLastRow As Long
Dim i As Long
Dim sFile As String

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow To 1 Step -1
sFile = Right(.Cells(i, TEST_COLUMN).Value, Len(.Cells(i, TEST_COLUMN)) - 1)
.Hyperlinks.Add Anchor:=.Cells(i, TEST_COLUMN), _
Address:=TARGET_DIR & sFile & ".pdf", _
TextToDisplay:=.Cells(i, TEST_COLUMN).Value
Next i

End With

End Sub

[Guido]

I get an error on the following line:

sFile = Right(.Cells(i, TEST_COLUMN).Value, Len(.Cells(i, TEST_COLUMN)) - 1)

Error 5, not a valid procedure call or an invalid argument.

[Norie]

Guido

Which column is the the data located in?

Did you change this as xld suggested?

Const TEST_COLUMN = "A" As String

[Guido]

Yes de data is in column A