+ Reply to Thread
Results 1 to 18 of 18

Excel 2007 : Add hyperlinks

  1. #1
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Add hyperlinks

    I have around a thousand files in excel 2007 sheet that i need to hyperlink it a different folder in our server, is there a macro that i could used so that i could hyperlink it in one time? and how will i add that macro in my excel sheet, i dont know much about macro.

    Some of the folders in the server are : Correspondence, transmittal, RFI etc.and in my excel sheet all transmittal, rfi and other correspondence are just in one excel sheet. I have attached a sample excel sheet for you to see what i need.

    Please help me............

    Thanks in advance
    estman
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How can i hyperlink?

    Hello MamadraE, welcome to the forum.

    Thanks for posting a sample file. Unfortunately, it is not clear at all, where hyperlinks should be constructed, nor what they should link to.

    Can you please add a few manually constructed samples of the hyperlinks you'd expect to see? Explain where you would source the data to construct your hyperlink, create a few sample links that go in line with your source data.

    cheers

  3. #3
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    Thanks for replying.

    Actually its the column B that needs to be hyperlink. Each Mail No. has a scanned pdf copy in our R:Drive in the server. For Example: ABC-RFI-00025 this rfi has a scanned copy in our R: Drive with the same file name..so how can i hyperlink all of this in one time.

    I hope this will help you.

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How can i hyperlink?

    I hope this will help you.
    Afraid it does not.

    Mock up a few rows with examples of the hyperlinks you would expect to see based on your data in column B.

    For example: What's the full path? Is it a network drive? What is the file extension? And: where would you want to see the hypelink result?


    We can't read your mind. Help us help you!

  5. #5
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    Here is the revised excel sheet. Hope this can help you to solve my prolem.

    Thansk in Advance.

    Estman
    Attached Files Attached Files
    Last edited by shg; 08-10-2010 at 06:31 PM. Reason: deleted quote

  6. #6
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    yes the source file is the network drive.

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How can i hyperlink?

    Ahh! See? Your sample link had a lot more information about the hyperlink than your spreadsheet our your explanation in this thread.

    You can insert a new column, and then use a formula like
    Please Login or Register  to view this content.
    copy down.

  8. #8
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    Thanks again but i cant open the file. i copy and pasted it but there is an error message " Can not open the File".

    Estman

  9. #9
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    once i click the hyperlink it wont open the file - error message " can not open the specified file".

  10. #10
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How can i hyperlink?

    Check that the file path is correct in the formula. Use Windows Explorer to navigate to the folder and copy the full folder path from the Address bar. Paste it into the formula.

    Or,

    use Insert - Hyperlink to create a hyperlink to one of your documents, then edit the hyperlink and copy the path from there into the formula.

  11. #11
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    I really appreciate your help and thanks you so much.

    I have check the path and its all correct. I have tried copying it to your formula and still can not open the file.

    Here is the set up:

    in our R:\drive i have a main folder as Correspondence underneath is a set of sub folders like confidential, transmittal rfi etc. and underneath those subfolder are another folders IN & Out for each folder and underneath it is another set of folders to whom it came from.

    Example:
    2.1 Correspondence
    2.1.1 Confidential
    2.1.1.1 IN
    XZY company
    M_M Company
    2.1.1.2 OUT
    Mars Company
    Hershey Company
    and so on
    SO in my excel sheet the MAIL No. (Column B) i have a thousand records that i need to hyperlink it to those folders i have mention.

    Hope this will help and if there is a macro to do this could you please tell me how can i put it in my excel sheet because im still new in macro

    Thanks again,
    estman

  12. #12
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How can i hyperlink?

    eastman, if the formula does not work, a macro will not do better.

    select an empty cell. Enter a word into it, like "test". Select the cell and then click "insert - hyperlink". Navigate to your folder and hit OK.

    Edit the hyperlink and copy the path.

    Paste the path into the formula discussed above.

    I've tried it in my setting. It works. I've used long and complicated network paths with lots of spaces in folder and file names. It worked every time.

    If it does not work in your setting, it's not something I see that can be solved with Excel.

  13. #13
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    thanks, its working now but my problem as you can see in my worksheet the second item is a transmittal if i copy and paste it the file name is correct but when you click it its still the same document as the first one, is there any other way that i could direct it to the right folder as i copy the formula or do i really need to do it manually? it's a thousand records for one project only and i have a lot of project.....please help me!!!!

    Thanks in advance,

    estman

  14. #14
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How can i hyperlink?

    I can't see your workbook after you have applied the formula. You would need to post it first.

    In the formula I gave you above, the file name is appended to the path from the value in column B. What is your formula?

  15. #15
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    Quote Originally Posted by teylyn View Post
    I can't see your workbook after you have applied the formula. You would need to post it first.

    In the formula I gave you above, the file name is appended to the path from the value in column B. What is your formula?
    I have attached the screen shot and some explantion of what i want to have.

    Thanks in advanced for helping me out.

    estman
    Attached Files Attached Files

  16. #16
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How can i hyperlink?

    so, according to the explanation in the Word document, your formula is

    Please Login or Register  to view this content.
    That's not what I posted. Take away the quote marks around the first "B2". It's a cell reference and will append the content of the cell B2 to the path. If copied down, it will adjust.

  17. #17
    Registered User
    Join Date
    07-13-2010
    Location
    United Arab Emirates
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How can i hyperlink?

    Quote Originally Posted by teylyn View Post
    so, according to the explanation in the Word document, your formula is

    Please Login or Register  to view this content.
    That's not what I posted. Take away the quote marks around the first "B2". It's a cell reference and will append the content of the cell B2 to the path. If copied down, it will adjust.
    I did remove the quote but then its still not hyperlinking to the proper document that is save in the G drive.

  18. #18
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Add hyperlinks

    Look, the formula syntax is

    Hyperlink(link_location, friendly_name)

    You can construct the link_location by concatenating constants and/or cell contents. In the end, the string for link_location needs to be the EXACT path and file name of your document. Otherwise, the hyperlink won't work.

    Tip: If you want to see what your formula evaluates to, select the cell, then click the Formulas ribbon and select the Evaluate Formulas command. Now click through the formula step by step. See how the path and the file name are concatenated from the input you provided in the formula. If something is not right, you will see which component of the formula goes wrong and you can correct it.

    As I said before: I can't see your formula if you don't post your spreadsheet. Even if you do, I can't check if the path is correct for your network situation. So, please, do your homework and don't just complain that it does not work.

    I have given you a few pointers about how to find the correct network path. I trust that your column B will hold the correct file name. Does it include the file extension? Is your system set up for inclusion or exclusion of file extension?

    All these things I don't know, so I don't know how to advise you further.

    The method and the tools for error checking are all in this thread. Do your homework now, or post the formula you are actually using. I don't know how else to help you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1