-
to automate cells with a hyperlink
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
-
[vba]
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
[/vba]
-
question
I'm a newby with vba, What does "A" mean, and where do i give the directory where all the pdf's reside
-
[vba]
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
[/vba]
-
Error during execute
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.
-
Guido
Which column is the the data located in?
Did you change this as xld suggested?
[vba]
Const TEST_COLUMN = "A" As String
[/vba]
-
Yes de data is in column A
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules