Hyperlinks to pdf files in a folder

sknight0980

New Member
Joined
Nov 2, 2017
Messages
7
Hello,

I have a folder with scanned documents in pdf format. All files are named by number for example 1234. I have them logged in a spreadsheet and the number is in column C. Is it possible to have a hyperlink to all the pdf files? Ideally I would like the number in column C to be the hyperlink to the pdf of the same name in my folder.

Any help would be greatly received.

Thanks
Ste
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Do the values in col C have the file extension?
Also what is the complete file path to the folder?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
This assumes that the values in C do not have the file extension.
Change Pth to suit
VBA Code:
Sub sknight()
   Dim Cl As Range
   Dim Pth As String
   
   Pth = "C:\Users\username\Documents\"
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
      Cl.Hyperlinks.Add Cl, Pth & Cl.Value & ".pdf"
   Next Cl
End Sub
 
Solution

sknight0980

New Member
Joined
Nov 2, 2017
Messages
7
This assumes that the values in C do not have the file extension.
Change Pth to suit
VBA Code:
Sub sknight()
   Dim Cl As Range
   Dim Pth As String
  
   Pth = "C:\Users\username\Documents\"
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
      Cl.Hyperlinks.Add Cl, Pth & Cl.Value & ".pdf"
   Next Cl
End Sub
thank you the path to the folder is L:\Terminal Services Shared - Scunthorpe\CorrBoard\Collection Scans
The values in C are just numbers like 2662. I have named the corresponding adobe files as the same name.

I have put this code into the sheet and it has changed the values in C to hyperlinks but when i click on them I get the message "cannot open the specified file"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did you change the path in the code?
 

sknight0980

New Member
Joined
Nov 2, 2017
Messages
7
Did you change the path in the code?
Yes, this is what I inserted -

VBA Code:
Sub sknight()
   Dim Cl As Range
   Dim Pth As String
 
   Pth = "L:\Terminal Services Shared - Scunthorpe\CorrBoard\Collection Scans"
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
      Cl.Hyperlinks.Add Cl, Pth & Cl.Value & ".pdf"
   Next Cl
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're missing the final \ at the end of the path.
 

sknight0980

New Member
Joined
Nov 2, 2017
Messages
7
You're missing the final \ at the end of the path.
Thank you. I have amended it (as below) and i have got the error message in the attached image

VBA Code:
Sub sknight()
   Dim Cl As Range
   Dim Pth As String
  
   Pth = "L:\Terminal Services Shared - Scunthorpe\CorrBoard\Collection Scans\"
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
      Cl.Hyperlinks.Add Cl, Pth & Cl.Value & ".pdf"
   Next Cl
End Sub
 

Attachments

  • image_2020-11-17_120029.png
    image_2020-11-17_120029.png
    23.4 KB · Views: 17

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
I've no idea why you would get that error. Try closing & restarting Excel.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

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
Top