Hyperlinks to pdf files in a folder

sknight0980

New Member
Joined
Nov 2, 2017
Messages
8
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Do the values in col C have the file extension?
Also what is the complete file path to the folder?
 
Upvote 0
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
 
Upvote 0
Solution
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"
 
Upvote 0
Did you change the path in the code?
 
Upvote 0
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
 
Upvote 0
You're missing the final \ at the end of the path.
 
Upvote 0
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: 24
Upvote 0
I've no idea why you would get that error. Try closing & restarting Excel.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
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