macro to hyperlink excel cells to pdfs

[2977cc]

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]

record a macro while you are manually inserting hyperlinks. then paste the macro here so we can review it.

[2977cc]

record a macro while you are manually inserting hyperlinks. then paste the macro here so we can review it.

[2977cc]

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]

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]

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]

you are welcome.

so you can modify the code i posted?

[2977cc]

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]

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]

\\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]

?

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

[mancubus]

duplicate post

[mancubus]

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\"