VBA to read cells value and create hyperlink from directory

[shangzuo]

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!

 

[FormR]

Hi, welcome to the forum.

You could try something like this.

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

 

[shangzuo]

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?

 

[FormR]

Is it able to keep the cells unhyperlink if the PDF is not in the directory?

Hi, you can try like this:

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

 

[shangzuo]

Thanks! It works like a charm!!!!! MUCH MUCH MUCH APPRECIATES!!!

 

[shangzuo]

Hi, you can try like this:

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.

 

[FormR]

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.