Populating an excel document with hyperlinks to relevant files

Question

I currently use a spreadsheet where I need to manually hyperlink 4 separate files in each row which include important information for referencing (3 are PDFs and 1 is an excel spreadsheet). Each row is full of information for a specific order number, which is conveniently part of each file that I need to hyperlink. Each of the 4 types of documents I would like to hyperlink into my excel sheet are also all packed into 4 folders path1, path2, path3, and path4, and they have their own column in the spreadsheet. Each folder will only contain one file with the order number.

I tried to automate this in excel only, using these formulas in the 4 columns:

K2 =IF(ISBLANK(C2)," ",HYPERLINK("J:path1\"&C2&".pdf",C2))
L2 =IF(ISBLANK(C2)," ",HYPERLINK("J:path2\"&C2&".pdf",C2))
M2 =IF(ISBLANK(C2)," ",HYPERLINK("J:path3\"&C2&".xlsx",C2))
N2 =IF(ISBLANK(C2)," ",HYPERLINK("J:path4\"&C2&".pdf",C2))

The formula references cell C2 which is the order number, and it fills the path with that number, which works great for the files which are consistently named. It also leaves the cell blank if there is no order number, because it happens sometimes and I need the function to not freak out when there is nothing there. The problem comes when I run into the file names that have other things tacked on the end such as a date. My formula is incapable of hyperlinking a file unless I give it the exact path to begin with.

I am wondering if anyone knows if excel is even capable of finding a file in a folder when only given part of the file name.

If there is not a way to do this in excel, I was hoping there may be a way to do this with VBA. I did some searching and found the Application.FileSearch feature in VBA, but it says "Object doesn't support this action." when I try to call it. (Which from a simple google search that seems to be the error due to Application.FileSearch not existing in excel 2007, but I am running 2013, so I'm not sure why this is happening)

I have a very novice understanding of VBA, so I am trying to slowly learn on the side. If anyone could help me come up with a code that would allow me to reference a cell, and find files containing that name so that I could print that path to a different cell, I would greatly appreciate your help.

So long as the filename will ALWAYS be *at least partially* known, you can use [this method](http://stackoverflow.com/questions/2860797/vba-how-do-i-open-a-file-if-i-only-know-part-of-the-file-name) to find it. — Tim, May 23 '16 at 16:33

score 0 · Accepted Answer · answered May 23 '16 at 16:54

0

Something like this should work (or at least point you down a path to investigate).

Sub HyperlinkFiles()
    Dim strFile As String

    strFile = Dir$("J:\path1\*" & Sheet1.Range("C2") & "*.pdf")
    If (Len(strFile) > 1) Then
        Sheet1.Range("k2").Hyperlinks.Add Sheet1.Range("k2"), strFile
    Else
        'No file was found that match so do nothing
        'However, you could link to the folder to make manually searching easier
        'Sheet1.Range("k2").Hyperlinks.Add Sheet1.Range("k2"), "J:\Path1"
    End If

    strFile = Dir$("J:\path2\*" & Sheet1.Range("C2") & "*.pdf")
    If (Len(strFile) > 1) Then
        Sheet1.Range("L2").Hyperlinks.Add Sheet1.Range("L2"), strFile
    Else
        'No file was found that match so do nothing
    End If

    strFile = Dir$("J:\path3\*" & Sheet1.Range("C2") & "*.xlsx")
    If (Len(strFile) > 1) Then
        Sheet1.Range("M2").Hyperlinks.Add Sheet1.Range("M2"), strFile
    Else
        'No file was found that match so do nothing
    End If

End Sub

The caveat with this code is when there are 2 or more files that match the search pattern. For example, suppose cell C2 contains Stack and you have 2 files named stackoverflow.pdf and stackexchange.pdf. Which "stack" file do you want?

answered May 23 '16 at 16:54

Tim

2,701
3
26
47

I tried adding the paths like this: `Dim path1 As String` `path1 = "J:\blah\"` But then when I run the code, nothing happens. Then if I replace path1 with "J:\blah\" directly in your code, it does seem to work. However, when I try to follow the hyperlink, nothing is found. I noticed that when I manually hyperlink the files, it will put them in the sheet as FolderTheyAreIn\FileName.PDF. I'm not sure if this is due to the excel sheet being in the same folder as the 4 folders I am referencing (I don't see why that should be a problem) Either way we are so close! @Tim – Adam H May 23 '16 at 18:53
Actually, upon further inspection, it turns out this code is leaving out the last folder in the path when it prints it into the cell. I don't know why it would do that, or how to make it stop. I will do some searching at let you know if I can find anything. @Tim – Adam H May 23 '16 at 19:01
There might be some funny business going on with Dir. It might be searching sub directories when it shouldn't...I'll look at this in the morning. What if you put a `MsgBox strFile` in there to see what the value of strFile is? Does it have all the folders? – Tim May 23 '16 at 22:45
When I use MsgBox on my COMS search it just returns the order number from C2 .PDF (exactly the file name). But when I hover over them it shows a link to the entire folder, which I put in my last comment, though this may just be indicating the directory where the sheet is now that I think about it... But in my column for packing lists, it is actually finding the bizarre names that we put on packing lists, not just linking the OrderNumber.xlsx (confirmed with MsgBox). So it does appear strFile is the file name only; Is there a way to record path rather than file name? @Tim – Adam H May 24 '16 at 13:47
Or I suppose an easy solution could just be to tell the stupid thing to print the entire directory again followed by strFile. – Adam H May 24 '16 at 13:48
If I enter the entire directory in before strFile, it seems to be working correctly. There may be a way to make it simpler, but for now, this is doing everything I wanted it to do, so thank you! – Adam H May 24 '16 at 14:33
Sorry for such a late response. Had a surprise equipment audit this morning. I'm glad you figured this out. Yes, the `DIR` function *only* searches in that directory. It will not search subdirectories, so you will have to include the complete path to the folder that contains the files you will want to find. If those files could be in a bunch of different directories, then you will want to use a `FileSystemObject`. – Tim May 24 '16 at 16:33
I know this has been dormant for quite a few months now, but I have recently added a new column to my spreadsheet. I want my program to do the exact same thing *-search a folder and report something based on part of the name-* but now I am searching for an entire folder, and not just a file. I can't seem to figure out how to find a folder within a folder... it seems that `DIR` only wants to search for files. Any tips on what I should be using to continue this same idea but for folders? @Tim – Adam H Sep 07 '16 at 21:52
@AdamH Look at this thread: http://stackoverflow.com/questions/20788288/vba-to-find-multiple-files/37872635#37872635 You can create a WScript shell and run the DOS `DIR` command which *does* allow you to search for folders. – Tim Sep 08 '16 at 03:29
When I used that function I was able to search the correct drive and return a list of the files inside it, but if I tried to specify my path to anything more than j:\ the program would run and just print nothing into my Immediate window. I tried all different lengths of the path to see if there was a certain folder that was breaking the search, but it seems that all of them return nothing. I also played with most of the different file_attributes in the `DIR` command and still no luck. – Adam H Sep 08 '16 at 15:25
@AdamH I would suggest posting a question with the code you are using then. The comment fields of a related question are not really the best places for troubleshooting. :) – Tim Sep 08 '16 at 18:09

Populating an excel document with hyperlinks to relevant files

1 Answers1