Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

  1. #1
    New Member
    Join Date
    Aug 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    I'm looking for some code or some detailed help as I have really little to no experience with visual basic.

    So here's what I'm trying to achieve:

    I have a spreadsheet of a test log which features test numbers in column A, and other important info such as test date, description etc. in the following columns. These test numbers are all 4 digit numbers, "1234." There's really no finite end to the amount of test numbers, the list is always growing.

    All of these test numbers have a scanned PDF of the test file in a certain folder. The PDF filenames are either the exact test number or the test number with some additional test details, so either "1234" or "1234 example test info."

    I want to automatically add hyperlinks of these PDF's to either one cell at a time or all cells in column A. However, since some filenames are not exact matches to the cell's in column A, the code should ignore the extra text. I feel in would maybe make the most sense if the code could some how only look at the first 4 digits of the filename as that is always going to be the test number so would match what value was in the cell(s).

  2. #2
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    Try this, if you are not aware of where to use this or how to execute it let me know, will try to help

    Code:
    Sub AddHypaerlinks()
    
    Dim lastRow As Long
    Dim myPath As String, fileName As String
    
    
    myPath = "C:\Users\dchaney\Documents\" 'SET TO WHERE THE FILES ARE LOCATED
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lastRow
    
        fileName = myPath & Range("A" & i).Value & "*.pdf"
        
        If Len(Dir(fileName)) <> 0 Then 'IF THE FILE EXISTS THEN
        
            ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)
        
        End If
        
    Next
    
    End Sub
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

  3. #3
    New Member
    Join Date
    Aug 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    Well this seems to be doing exactly what I wanted it to do, thank you! I had managed to scrounge up some code from some other threads and sites and pieced something together, but this absolutely works better.

    Thanks again, dchaney.

  4. #4
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    My pleasure
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

  5. #5
    New Member
    Join Date
    Aug 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    OK, so I have one more question actually, and I swear I fiddled around and got it to work yesterday afternoon, but I can't replicate it now.

    If I want to link a folder in the same manner, rather than a file, is it as simple as replacing "*.pdf" or something else entirely?

    Thanks.

  6. #6
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    If you want to link to a Folder and not a file then use the following... Highlighted in red are the changes made

    Code:
    Sub AddHypaerlinks()
    
    Dim lastRow As Long
    Dim myPath As String ', fileName As String - Comment Out, no longer required for folder link
    
    myPath = "C:\Users\dchaney\Documents\" 'SET TO WHERE THE FILES ARE LOCATED
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lastRow
    
       'fileName = myPath & Range("A" & i).Value & "*.pdf" - Comment Out, no longer required for folder link
        
        If Len(Dir(myPath)) <> 0 Then 'IF THE FILE EXISTS THEN (Changed from myFile to myPath for Folder Link)
        
            ActiveSheet.Hyperlinks.Add Range("A" & i), myPath '& Dir(fileName)  - Comment Out, no longer required for folder link
        
        End If
        
    Next
    
    End Sub
    Last edited by dchaney; Aug 11th, 2016 at 09:24 AM.
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

  7. #7
    New Member
    Join Date
    Aug 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    With that I'm getting all my entries hyperlinked to the directory I set as "myPath."

    Basically I have a folder in the directory where all my PDF's are that contains more folders with multiple PDF's in those folders that are assigned to one test number.

    So I'm looking to take that folder, say "C:\users\dchaney\documents\folder\" and hyperlink all the subfolders located in "\folder\". This way the user can open the folder containing the PDF's they want, without me having to manually adjust a bunch of filenames or change my spreadsheet layout. Again this all works the same way where the subfolders names are "1234" or start with "1234..." Does that make sense?

  8. #8
    New Member
    Join Date
    Aug 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    For the time being I managed to "link" to the folders by creating shortcuts to all my subfolders and changed the search for pdf's to the shortcut files ("*.pdf" to "*.lnk"). It's a little cluttered but at least the spreadsheet is working how I wanted it to. Still if there's another option where I don't have to come up with a workaround like creating shortcuts to the folders I would be very interested in learning it.

  9. #9
    Board Regular dchaney's Avatar
    Join Date
    Jun 2008
    Location
    Ohio
    Posts
    729
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    Here you are, try this... The first thing it will do is see if there is a Sub Folder with the name used in the Excel cell... for instance if you have a folder named 1234 it will link it to that... if you do not have a folder named 1234 but you have a file, it will link to that file... if both are present it will link to folder, not file...

    Code:
    Sub AddHypaerlinks()
    
    Dim i As Integer
    Dim lastRow As Long
    Dim myPath As String, fileName As String, subFolder As String
    
    myPath = "C:\Users\dchaney\Documents\" 'SET TO WHERE THE FILES ARE LOCATED
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lastRow
    
       fileName = myPath & Range("A" & i).Value & "*.pdf"
       subFolder = myPath & Range("A" & i).Value & "\"
        
        If Len(Dir(subFolder)) <> 0 Then
        
            ActiveSheet.Hyperlinks.Add Range("A" & i), subFolder
            
        ElseIf Len(Dir(fileName)) <> 0 Then
        
            ActiveSheet.Hyperlinks.Add Range("A" & i), myPath & Dir(fileName)
        
        End If
        
    Next
    
    End Sub
    **ALWAYS test code on a copy of your workbook, NOT the original**
    **ALWAYS USE CODE TAGS (# above in the edit bar of your reply/post) **

    “Courage is being scared to death, but saddling up anyway.” John Wayne
    Running Office 2010 on Win 7

  10. #10
    New Member
    Join Date
    Aug 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Auto Hyperlink a .pdf file from a folder based on cell value

    OK, so that's working, but it only works if the cell value is the exact same name as the folder name. I'm not sure if there's a workaround for that or if it has to be the exact same name when it comes to folders. If that is the case that's not a huge issue I can change the folder names. So I get the hyperlink if my cell is "1234" and my folder is "1234" but not if my cell is "1234" and my folder is "1234 pressure test" for example. And like I said if I'm not able to have it recognize "1234 pressure test" as the folder I want for my cell value of "1234" when there's no other sub-folder that starts with "1234..." then It's not a huge deal for me to change my sub-folder names. And thanks again for all your help, I really appreciate it.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •