VBA to read cells value and create hyperlink from directory

shangzuo

New Member
Joined
Jan 15, 2018
Messages
4
Hi there,

First of everything thanks for spending time to read my post. I'm a amateur to VBA, but I need something complex to help in my work.

Scenario:

1) Cells A5 contain "Instruction A" , I would like to have VBA to able to read the cell and create hyperlink to my directory example: C:/Instruction A.pdf which I already placed in the directory.
2) The VBA able to skip empty cells and will not create Hyperlinks
3) The VBA only works from certain rows in a column, example, Column A5 to A100

Much appreciated!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi, welcome to the forum.

You could try something like this.

Code:
Sub AddHLs()
Dim c As Range
For Each c In Range("A5:A100").SpecialCells(xlCellTypeConstants)
  c.Hyperlinks.Add c, "C:\" & c.Value & ".pdf"
Next c
End Sub
 
Upvote 0
Thank you so much! However, I realized that even if I never put the PDF in the directory, it still hyperlink the cells which have value. Is it able to keep the cells unhyperlink if the PDF is not in the directory?
 
Upvote 0
Is it able to keep the cells unhyperlink if the PDF is not in the directory?

Hi, you can try like this:

Code:
Sub AddHLs()
Dim c As Range
Const sPath As String = "C:\" 'Amend the path here..
For Each c In Range("A5:A100").SpecialCells(xlCellTypeConstants)
  If Len(Dir(sPath & c.Value & ".pdf")) Then c.Hyperlinks.Add c, sPath & c.Value & ".pdf"
Next c
End Sub
 
Upvote 0
Hi, you can try like this:

Code:
Sub AddHLs()
Dim c As Range
Const sPath As String = "C:\" 'Amend the path here..
For Each c In Range("A5:A100").SpecialCells(xlCellTypeConstants)
  If Len(Dir(sPath & c.Value & ".pdf")) Then c.Hyperlinks.Add c, sPath & c.Value & ".pdf"
Next c
End Sub


Hi, sorry but is there anyway for the code to loop through the sub folders searching for the files? Current scenario is only able to search on the particular folder indicate on the sPath.
 
Upvote 0
Hi, this has moved away from the original request quite a bit - what you have attempted so far for the new scenario? Maybe if you can post your attempted code we can try and help you to debug if you are having problems with it.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top