1

I have a spreadsheet with two columns: A & B. In column "A", I have product IDs. In column "B" I have the same product IDs listed (in the same order). I want each product ID in column "A" to link to a word doc saved on my computer. The word docs saved on my computer are titled with the product ID (exact). To save time, I hyperlinked the entire column "A" to link to the same word doc. Then, I want to copy the product ID from column "B" into the existing column "A" hyperlink and overwrite the generic/copied product ID that is already there. Overall goal, to click on the product ID in column "A" and have it open the corresponding word doc. I have been copying the product ID from column "B", right clicking on the product ID in column "A", edit hyperlink, paste product ID over the generic product ID in the hyperlink field. Doing this over 300 times is not a night of fun for me! Help!

5
  • 1
    Sounds like a job for VBA. ‘‘Record Macro’’ when you do what you do manually, and turn it into a loop. Mar 22, 2019 at 0:22
  • Now I need to figure out how to do that! :) Mar 22, 2019 at 1:17
  • 1
    Wow; that’s a big subject. For the first part, (1) enable the “Developer” tab, (2) on the “Developer” tab, click “Record Macro” and click “OK”, (3) manually copy one product ID, (4) click “Stop Recording”, and (5) click “Macros” → “Edit”.  That’s the easy part.  Now you need to wrap the code in a loop.  This site and this one (at microsoft.com) are useful references for VBA, … (Cont’d) Mar 22, 2019 at 4:08
  • 1
    (Cont’d) …  but it’s like trying to learn how to drive by reading a roadmap.  This is the official Super User starter’s guide for using VBA with Microsoft Office, and this search will lead you to nearly four thousand Super User posts about Excel and VBA.  Hundreds of them have good examples of working code (you might want to sort by votes). If you try something and get stuck, come back and ask a new question. We won’t do the job for you, but we’re willing to help. Mar 22, 2019 at 4:08
  • You’re welcome.  If you try to do this and you get stuck, please edit your question to show what you’re done and where you’re having a problem; people will be more likely to answer a question like that.  If you try to do this and you succeed, please post your solution as an answer, so other people can benefit from it. Mar 30, 2019 at 3:28

1 Answer 1

1

Lets assume you have your word documents stored in C:\Temp similar to the following:

Sample file location

We will assume you data is layed out in excel similar to the following:

Sampe excel layout

Step 1 - Set the file path

In an empty cell that is not being used, type in the path of the directory where your word documents are stored. You can copy and paste the address from your file explorer window if it makes life easier for you. Pay attention to how the path ends. You are going to want to tack on a \ at the end or you will need to do it in your formulas. If the spreadsheet is stored in the same directory as the doc files, you can automate the file path using a big formula, but that is for another question. for now just type in the path.

Step 2 - Develop the hyperlink formula

HYPERLINK formula is made up of two parts, The path to the file and a shorter name that is easier to read. Looks something like:

HYPERLINK( Path to file,  easy name to read)

So in order to get the path to the file and not have to type it in EVERY time, you want to build it through part and lock certain cell references so they don't change as the formula is copied. Right now lets look at developing the path to the document...note its to the document and not just the file path to the directory. Lets assume your Word documents end in .doc. Following the example data, the path to the first and second document would be:

C:\Temp\ID A1.doc
C:\Temp\ID A2.doc

Since the first part of that doesn't change and we have it entered in D1 we can start there. The next part which changes is in B2 (also A2 but it sounds like B2 is your refence data). The last part we do not have anywhere but its .doc. Through text manipulation we can ram all the together with the following formula:

=$D$1&$B2&".doc"

Note the use of $. It will keep the Row reference or column reference that is to the right of it from changing as the formula is copied.

Now that you have the file path built, it can be inserted into the HYPERLINK function as follows:

=HYPERLINK($D$1&$B2&".doc","Link")

Place the formula above into C1 for demonstration purposes and copy down. You should get the word link underlined in blue and if you click on it your word document will open. Now if you want to have the product name be displayed instead of link change the formula to:

=HYPERLINK($D$1&$B2&".doc",$B2)

Now the downside

  • You cannot copy column C and paste it as value and still have the hyperlink work. You are stuck having a reference column.
  • If the file does not exist or the path changes on you, you will get an error message pop up that the document cannot be found when you click on the link.
  • I have had some issues in the past where the hyperlink would not update to a changed path despite every indication that the change had occured. Solution is after entering the formula for the first time and before copying it, right click on the cell and select remove hyperlink. Apparently two hypelinks get created and the one that sits on top is initially created by the formula but not updated by it. When you delete it, you will then be activating the one that is part of the formula and gets updated when you change the path or file name. After this is done, copy your formula down as required.

POC

Note I used the alternate formula in A2 and copied down

1
  • This is EXCELLENT.
    – Alex M
    Mar 22, 2019 at 5:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .