Macro to create hyperlinks to files.

**dcgrove** · 02-01-2010, 03:32 PM

Hello, I need help creating a macro that will create a hyperlink to each file in a folder full of documents. The hyperlinks are going to be in column F of a database that catalogs some of the information in these files. In column A is a unique number that will match the first few digits of the file the row pertains to. for example, cell A1 contains the number 147 and the corresponding file begins with 147*********. The numbers will always match and they will never repeat. The path to the folder is c:\files. If the macro could start at the next empty cell in column F to prevent having to create the links that are already created that would be great.

Thanks!

**rylo** · 02-01-2010, 08:25 PM

Hi

So in c:\files you have multiple files of the format 147???.???, and you want a macro to create a hyperlink to those files, starting with the first link in F, then G, then H....

Is that correct?

rylo

**dcgrove** · 02-01-2010, 10:22 PM

the files in c:\files start at 147????.pdf and will increase in number sequentially. so the next one that is added to the directory will be 148????.pdf and so forth. the number in column A will always be the first in the beginning of the file name. so if a1 is 147 in f1 I would need a link to c:\files\147????.pdf. and in A2 there was "148", I would need the link in F2 to be 148?????.pdf. The numbers in column will not always be in sequential order depending on how the spreadsheet is sorted. If the macro required it to be in order, i could sort the sheet.

Does that explain it a little better?

**rylo** · 02-01-2010, 10:50 PM

Hi

Try this.

Sub aaa()
  ChDrive "c"
  ChDir "c:\files"
  
  For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    filee = Dir(ce.Value & "*.pdf")
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(ce.Row, "F"), Address:= _
        "C:\files\" & filee, TextToDisplay:= _
        "C:\files\" & filee
  Next ce
  
End Sub

rylo

**dcgrove** · 02-01-2010, 10:53 PM

I will give it a shot in the morning when I get back to work.

Thanks!

**dcgrove** · 02-02-2010, 10:24 AM

That worked great! is there anyway to prevent it from inserting the link if the file does not exist? the way my database is set up, Column A is numbered from 1-1000 and some of the files between 1 and 187 are missing so when I run the macro, it inserts a link for the missing files to the folder all the way down to 1000.

Also, is there any way to make it start on the next empty cell in row "F" so that it will skip the cells with a link already?

Thanks!

**JBeaucaire** · 02-02-2010, 11:04 AM

This version of the macro above will only create new hyperlinks in column F if there isn't something in that cell already. This will allow you to delete broken links anytime and the next time the macro runs it will always check that blank to see if there is a match now.

Option Explicit

Sub AddHyperlinks()
Dim fName As String, fPath As String
Dim LR As Long
Dim cell As Range, RNG As Range

fPath = "C:\Files\"
LR = Range("A" & Rows.Count).End(xlUp).Row      'Last row
Set RNG = Range("A1:A" & LR)

    For Each cell In RNG
        If Cells(cell.Row, "F") = "" Then   'only rows not done already
            fName = Dir(fPath & Cells(cell.Row, "A").Value & "*.pdf")
            If Len(fName) > 0 Then _
                ActiveSheet.Hyperlinks.Add Anchor:=Cells(cell.Row, "F"), Address:= _
                    fPath & fName, TextToDisplay:=fPath & fName
        End If
    Next cell
    
Range("F:F").Columns.AutoFit
End Sub

**dcgrove** · 02-02-2010, 02:05 PM

JBeaucaire, your macro worked for the most part. On rows where the numbers in column A are the similar it is inserting incorrect links. On row 3 for example, the number "3" is in cell A3 but it linked to the file that starts with "30". I have attached a worksheet with some examples. the highlighted examples are incorrect and are the results of running the macro.

Thanks!

**JBeaucaire** · 02-02-2010, 03:02 PM

The macro is working as requested, it finds a file in the specified directory that starts with the string in column A and creates a hyperlink.

So, based on the examples I see of your actual filenames, you'll need to resolve the logic you want the macro to use.

30 does match to 3*.
100 does match to "1*", "10*", "100*", "100 *", "100, " etc...

So you'll need to cleanup the matching method you want to employ and present it here so I can assist you in making the macro use that matching logic.

Be thorough. You can see your file names in total, so work out a match logic that resolves all the variances in your naming structure. Matching to "numbers" like this is always going to be troublesome, in some ways.

**lhodgeman** · 01-31-2012, 05:17 AM

Hi All,

Am a complete VBA newbie and have totally jumped into the deep end with a work project. I have a wokbook that records an individual id number for each job that i log in Column J. A copy of this is saved on my computer in the File "CAP TEST". What i need is a macro that will automatically search "CAP TEST" find the job id and create a hyperlink from that file to the workbook which is acting as a database.

I think that the code below will do what i want but i keep getting an error 52 'Bad Filename or number' and i'm not sure how to fix it. The job id id in the form ABC-DEF-HIJ-0123, could this be the issue?

Am happy to send sheet through if this will help?

Many Thanks

Option Explicit

Sub AddHyperlinks2()
Dim fName As String, fPath As String
Dim LR As Long
Dim cell As Range, RNG As Range

fPath = "C:\Users\Lauren\Documents\Work\CAP TEST\"
LR = Range("J" & Rows.Count).End(xlUp).Row      'Last row
Set RNG = Range("J2:J" & LR)

    For Each cell In RNG
        If Cells(cell.Row, "K") = "" Then   'only rows not done already
            fName = Dir(fPath & Cells(cell.Row, "J").Value & "*.xlsm")
            If Len(fName) > 0 Then _
                ActiveSheet.Hyperlinks.Add Anchor:=Cells(cell.Row, "K"), Address:= _
                    fPath & fName, TextToDisplay:=fName
        End If
    Next cell
    
Range("J:J").Columns.AutoFit
End Sub

**JBeaucaire** · 01-31-2012, 05:37 AM

Click on DEBUG, what line of code is highlighted?

In the CTRL-G Immediate Window, type this:

? fPath & fName

What is the exact result? Does it match exactly to the full path and filename? Check the 0s and the 1s and make sure they aren't Os and Is (vowels) and vice versa. Excel is telling you there is a naming error.