PDA

View Full Version : [SOLVED] macro to hyperlink excel cells to pdfs



2977cc
02-18-2016, 07:58 AM
Hi all,

Good day, I would like to ask if there's a thread similar to this one, since I'd tried searching but I found complicated ones.

My question is, if there's a macro to hyperlink the Excel Cells like range from A1- A20. The text inside the cells are alphanumeric which is exactly the SAME file name of the pdf. My Excel Workbook and the PDF Folder, in one folder in our local company database. For example, the text in A1 is Doc-123 and so on, then using the macro range A1-A20 will be hyperlinked to my PDF Folder. It's like linking the text in a cell to a PDF manually. The single column for the alphanumeric text will be filled everyday as well as the PDF Folder.

Any suggestion will be appreciated. Thanks in Advance.

mancubus
02-18-2016, 10:06 AM
record a macro while you are manually inserting hyperlinks. then paste the macro here so we can review it.

2977cc
02-20-2016, 02:30 AM
record a macro while you are manually inserting hyperlinks. then paste the macro here so we can review it.

2977cc
02-20-2016, 02:32 AM
record a macro while you are manually inserting hyperlinks. then paste the macro here so we can review it.

Thanks for the reply jim. Below is the code, I've post half of it.

""Sub Macro1()
'
' Macro1 Macro
'

'
Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"RFI%20PDF\RFI%20-%20001.pdf", TextToDisplay:="RFI-001"
Range("A2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"RFI%20PDF\RFI%20-%20002.pdf", TextToDisplay:="RFI-002"
Range("A3").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"RFI%20PDF\RFI%20-%20003.pdf", TextToDisplay:="RFI-003"
Range("A4").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"RFI%20PDF\RFI%20-%20004.pdf", TextToDisplay:="RFI-004"
Range("A5").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"RFI%20PDF\RFI%20-%20005.pdf", TextToDisplay:="RFI-005"
End Sub
""

mancubus
02-20-2016, 06:31 AM
you are welcome.

please don't quote previous messages.




James Douglas "Jim" Morrison (December 8, 1943 – July 3, 1971) was an American singer, songwriter and poet best remembered as the lead singer of The Doors.
https://en.wikipedia.org/wiki/Jim_Morrison
and 'This is the End' is one of the greatest songs they have produced.

mancubus is fine. it's taken from id software's famous game series, Doom. :)



after recording the macro did you test it. i mean when you clicked the hyperlink in a cell did it open the linked file?

%20's mostly stand for space character.

from the recorded macro, i understand that
A1 = RFI PDF\RFI - 001.pdf
A2 = RFI PDF\RFI - 002.pdf
...
A20 = RFI PDF\RFI - 020.pdf

and you want cells' hyperlinks displayed as RFI-001 (spaces removed) and not RFI - 001.pdf.

if this is the case, try



Sub vbax_55200_Insert_Hyperlinks_Cells_Contain_File_Names_With_Path()

Dim Text2Display As String

With ActiveSheet
For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
Text2Display = Replace(CreateObject("Scripting.FileSystemObject").GetBaseName(.Range("A" & i).Value), " ", "")
.Hyperlinks.Add Anchor:=.Range("A" & i), Address:=PdfFilesPath & .Range("A" & i).Value, TextToDisplay:="RFI-001"
Next
End With

End Sub


if cells contain only file names, like
A1 = RFI - 001.pdf
A2 = RFI - 002.pdf
...
A20 = RFI - 020.pdf

then try


Sub vbax_55200_Insert_Hyperlinks_Cells_Contain_File_Names_Only()

Dim PdfFilesPath As String, Text2Display As String

PdfFilesPath = "RFI PDF\"
'PdfFilesPath = "RFI%20PDF\"

With ActiveSheet
For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
Text2Display = Replace(CreateObject("Scripting.FileSystemObject").GetBaseName(.Range("A" & i).Value), " ", "")
.Hyperlinks.Add Anchor:=.Range("A" & i), Address:=PdfFilesPath & .Range("A" & i).Value, TextToDisplay:=Text2Display
Next
End With

End Sub

2977cc
02-20-2016, 08:10 AM
Thanks for the info, wasn't around that time yet.

Yes, in my sample I check and worked.

The second code is what I want, but partially linked the cells. After running it the checked the link, it has the missing .pdf at the end. It was RFI%20PDF\RFI-001.The correct link should be:RFI%20PDF\RFI-001.pdf.

mancubus
02-22-2016, 12:38 AM
you are welcome.

so you can modify the code i posted?

2977cc
02-22-2016, 01:21 AM
I tried to modify the code, the pdf names and the link. But still with my limited knowledge of vba I did not came up with the correct one. Ive been doin it the whole day yesterday.:-) tryin to search more on the web.

mancubus
02-22-2016, 01:57 AM
i tested the codes before posting here and they worked.

make sure you input the path name correctly.

open Windows explorerer.
select the folder that contains the pdf files.
select a pdf file, right click on it, click "properties". on "general" tab you can see the "location" info of the file.

then assign it to PdfFilesPath variable like:

PdfFilesPath = "WhatYouSeeInGeneralTab\"

2977cc
02-22-2016, 02:16 AM
\\sayerpfs01\Departments\AMT-Offshore\AMT-QC\08 RFI\RFI PDF this is the pdf properties on the general tab.

Sub vbax_55200_Insert_Hyperlinks_Cells_Contain_File_Names_Only()

Dim PdfFilesPath As String, Text2Display As String

PdfFilesPath = "\\sayerpfs01\Departments\AMT-Offshore\AMT-QC\08 RFI\RFI PDF\"
'PdfFilesPath = "RFI%20PDF\"

With ActiveSheet
For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
Text2Display = Replace(CreateObject("Scripting.FileSystemObject").GetBaseName(.Range("A" & i).Value), " ", "")
.Hyperlinks.Add Anchor:=.Range("A" & i), Address:=PdfFilesPath & .Range("A" & i).Value, TextToDisplay:=Text2Display
Next
End With

End Sub


still cannot open file.

mancubus
02-22-2016, 03:07 AM
?

PdfFilesPath = \\sayerpfs01\Departments\AMT-Offshore\AMT-QC\08%20RFI\RFI%20PDF\ (file://\\sayerpfs01\Departments\AMT-Offshore\AMT-QC\08%20RFI\RFI%20PDF\)

mancubus
02-22-2016, 03:11 AM
duplicate post

mancubus
02-22-2016, 03:36 AM
another option:


if it is a mapped drive you can get its drive letter by


Sub Network_Drives_List()
With CreateObject("WScript.Network").EnumNetworkDrives
For i = 0 To .Count - 1 Step 2
Debug.Print .Item(i) & " is " & .Item(i + 1)
Next
End With
End Sub


open VBE, paste the code in a module then run it.
it will list the drives in immediate window. (to open immediate window, press Ctrl + G)

if the letter for your drive is T, for example, you can use it like:

PdfFilesPath = "T\Departments\AMT-Offshore\AMT-QC\08 RFI\RFI PDF\"