Hyperlink question

[WithaY]

Let me start by saying I am your basic Excel user. Basic. I have a project that involves hyperlinks to other documents. Here is the scenario:
I have about 6000 individual pdf documents in a folder. They are each titled something different. I then have my main spreadsheet where I have all of my information that I need to link to these 6000 individual documents. Example: ABC-008234 will need to be linked to that exact named document among the 6000. Next would be ABC-008235 to be linked to another document in that 6000. I have over 6000 ABC numbers to link. The only way I have found so far to enter the hyperlink is to go through all the steps for each ABC number (Insert Hyperlink, go clicking through all the folders, find the document, etc. for the address). I know I can at least attach the "base" I guess by going to the Menu, Prepare, Properties and putting the base link to at least that main folder in that location. Is there any type of global wildcard I can enter where it goes to the folder and magically finds that named document??

Thank you to all for their patience reading this and trying to help a lost soul!

 

[Tom Urtis]

Just to be clear, you said you have a list of file names, such as ABC-008234. Two questions:

(1)
Really, would those file names be ABC-008234.PDF but in the list on your worksheet, they look like ABC-008234 without the ".PDF" extension?

(2)
Where exactly is this list of 6000 file names...in cell A1:A6000? And what is the name of the worksheet they are on?

 

[WithaY]

I apologize for not being clear. I knew I wasn't saying anything right!

I have created a spreadsheet where in the cells it lists:

(A:1) ABC-001234
(B:1) Manual Instructions
(C:1) 12/1/09

(A:2) ABC-001236
(B:2) Diagram
(C:2) 10/2/09

(A:3) ABC-012429
(B:3) Photographs
(C:3) 1/1/09

For cell A:1 - the ABC-001234 that has to link to a file that contains 6000 separate pdf documents. One of those documents is actually named ABC-001234, then there is another document named ABC-001236 (for A:2), etc. I just don't see any way to link to those individual documents in that entire folder in light of them all being different names.

Does that make it any clearer?? I'm so sorry!!

 

[Tom Urtis]

I was sort of with you up to to this point when you wrote:

For cell A:1 - the ABC-001234 that has to link to a file that contains 6000 separate pdf documents.

I don't understand how a file can contain 6000 files.

Do you mean folder instead of file

or

Do you mean a list of 6000 files.

Still not getting all this.

 

[WithaY]

Tom --

I am so sorry - again!

You are right, it is a folder. The ABC-001234 is just one document in this folder of 6,000 titled documents. The folder would have documents titled ABC-001234 and the next document could be ABC-001235, next ABC-001255, ABC-001267.

I do appreciate your patience in trying to help me!

 

[Tom Urtis]

This macro will do what you say you want, based on how you say your worksheet is set up with file names in column A. You did not say what the file path is for those PDFs, so whatever it is, substitute
C:\YourFilePath\
with that actual path.

I tested this and know it works when it is run from the worksheet that contains the list of 6000 files, so if you have trouble, post back. Be patient and let the macro run, as it will be evaluating 6000 cells, and only hyperlinking the cells where file names match the cell values. That means if some cells do not become hyperlinks, there is a spelling discrepancy between what the cell contains and what the name of the file really is.

Sub SetHyperlinks()
Application.ScreenUpdating = False
Dim cell As Range, strPath$
strPath = "C:\YourFilePath\"
For Each cell In Columns(1).SpecialCells(2)
If Len(Dir(strPath & cell.Value & ".pdf")) > 0 Then _
cell.Hyperlinks.Add anchor:=cell, _
Address:=strPath & cell.Value & ".pdf"
Next cell
Application.ScreenUpdating = True
End Sub

 

[WithaY]

TOM!!!!!

I tried this tonight as sort of a test run using only about 20 documents and it worked!!!!!!! I will try tomorrow with the entire worksheet.

I can't possibly thank you enough!!! You have saved me hours and hours and hours of time. Wow!

Thank you for being so very patient and helpful. I know I am not well versed in this, so I thank you again for your patience!!!

 

[Tom Urtis]

Very nice message, thanks for the follow-up.

 

[WithaY]

Tom -

The worksheet I have with the file names has 8 sheets. Does that matter? All of the file names are in column A on all the sheets.....

Thanks again....

 

[Tom Urtis]

I think you mean the workbook has 8 sheets.

There are two ways you can go with this.

One way is, if this is a recurring common task, we can modify the macro to loop through each of the 8 sheets. It would matter that, if there are any sheets in the workbook not associated with this list of file names, you'd need to either specify which sheet(s) should not be considered in the loop, or which sheets should be considered in the loop, either way in order to bypass the sheets not involved.

The other way might be easier and quicker if this is a one-time task. Simply activate the first of 8 sheets, hit Alt+F8, select the macro name in the Macro dialog box, click the Run button, then activate the second of 8 sheets, repeat those same steps of hitting Alt+F8, selecting the macro name and clicking Run, and so on, for each or the 8 sheets.