PDA

View Full Version : to automate cells with a hyperlink



Guido
06-03-2007, 05:48 AM
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
06-03-2007, 05:54 AM
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
06-03-2007, 06:17 AM
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
06-03-2007, 07:27 AM
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
06-03-2007, 09:03 AM
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
06-03-2007, 09:56 AM
Guido

Which column is the the data located in?

Did you change this as xld suggested?

Const TEST_COLUMN = "A" As String

Guido
06-03-2007, 10:08 AM
Yes de data is in column A