Automate hyperlinks

[dhemlinger]

I would like to create a hyperlink to pdf files in a folder based on the matching name in a cell of a excel spreadsheet.

I have pdf files that have a document name: helpme1.pdf, helpme2.pdf, helpme3.pdf, for example, in the same folder as the spreadsheet.

The names are in a cell, lets say Column I, in an excel spreadsheet. I would like to automatically go through the column and create hyperlinks in column H to the pdf file when it finds a match.

The last thing is that if the files are moved to another server, everything still works.

 

[Jon von der Heyden]

Welcome to the board.

What do you finds a match? Do you mean if the file exists in the directory?

What excel version are you using?

EDIT: Crafty edit I don't think that bit is do-able. Still let me know your version cos it'll help me decide what method to use. Also, knowing your directory path could help

 

[dhemlinger]

Hi,

I am using Excel 2003.

The files names are in spreadsheet called report.xls and is on in my C drive in a folder called report. They do not have the pdf added to the name, but I thought I could concatenante that in a column easily.

The actual pdf files are in the report folder also. What happens is a program extracts the reports names. Another person saves the reports as pdf files. We need to have a hyperlink created in the excel file to the reports automatically. There are hundreds of these.

When I am finished I need to copy the excel file and pdf's to the network drive N.

 

[Jon von der Heyden]

Try this:


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MakeHyperLinks()<br><br><SPAN style="color:#00007F">Dim</SPAN> strLookinPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> strLinkPath<br><SPAN style="color:#00007F">Dim</SPAN> lngFilNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rngFilName <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> strFilName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#007F00">'// change paths to suit //</SPAN><br><SPAN style="color:#007F00">'// strLookinPath should refer to the directory that the files are stored in //</SPAN><br><SPAN style="color:#007F00">'// strLinkPath should refer to the directory that you are going to move the files to //</SPAN><br><br>strLookinPath = "C:\Reports\"<br>strLinkPath = "H:\Reports\"<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">With</SPAN> Application.FileSearch<br>    .NewSearch<br>    .LookIn = strLookinPath<br>    .SearchSubFolders = <SPAN style="color:#00007F">False</SPAN><br>    .FileType = msoFileTypePhotoDrawFiles<br>    .Execute<br>    <br>    <SPAN style="color:#00007F">For</SPAN> lngFilNum = 1 <SPAN style="color:#00007F">To</SPAN> .FoundFiles.Count<br>        strFilName = Mid(.FoundFiles(lngFilNum), InStrRev(.FoundFiles(lngFilNum), "\") + 1)<br>        <SPAN style="color:#00007F">Set</SPAN> rngFilName = Columns("I").Find(strFilName)<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngFilName <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> rngFilName.Offset(, -1)<br>                    .Hyperlinks.Add anchor:=Range(.Address), Address:=strLinkPath & rngFilName.Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lngFilNum<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[dhemlinger]

Jon,

Thank you. They are creating the PDF's and I will be able to try it very soon.

I have one other question. Can I use a button to go through the directory on click. I am guessing I could, but my coding in Excel is very limited.

Again thanks, I will post to let you know it goes.

Debbie