+ Reply to Thread
Results 1 to 8 of 8

Automatically hyperlink to file without knowing extension

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Automatically hyperlink to file without knowing extension

    Hi Guys,

    I have thousands of file of various types (pdf,xlsx,docx,dat etc) in a specific folder. This directory keeps growing as well some time i delete some files from it. Every file in the directory has a unique no. I have listed the file names in spreadsheet say sheet 1 column A. I want to create hyperlinks to all those file in the same sheet i.e. sheet 1 column B. If there is no file present in the directory with same name (it has been delete), correspondent cell in column B should return NA or "File Not Available"

    I found two solutions to this problem at the following link. http://www.excelforum.com/excel-prog...extension.html

    @AlphaFrog suggested both of these solutions a couple of years ago.

    The first was
    Please Login or Register  to view this content.
    This prompts the user to choose folder where the files are residing and then creates hyperlinks in a new sheet.


    The second code which looks more neat is as follows
    Please Login or Register  to view this content.
    This does exactly what i want but there are two small issues first it doesn't give the user option to select folder where the files are residing as i want yo keep this spreadhsheet in a different and second it only shows hyperlink in any cell column b if i activate column correspondent cell in column A i.e. either type the value and press enter or press F2 and then enter. I have more than 20,000 cell i want to avoid pressing enter each time i copy a value in column A.


    So i was wondering is there any way where the second macro code can be amended where it gives me option to select the folder (Just like code 1) i.e. from where to create hyperlinks and avoid the necessity to press enter to generate hyperlink.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Automatically hyperlink to file without knowing extension

    Hi,

    I think this is a very easy problem if you use Power Query instead of VBA. Read:
    https://www.thebiccountant.com/2017/...inks-to-excel/
    http://efficiency365.com/2015/03/18/...iew-hyperlink/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Automatically hyperlink to file without knowing extension

    .
    I tried to insert the macro code here but the Forum servers keep refusing because of specific symbols contained in the code.

    You'll have to review the attached workbook.

    My apologies.

    This macro will create a hyperlink for each file in the folder. It does not rely on your list in Col A.
    Hopefully you can add to the macro to finish your project.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Re: Automatically hyperlink to file without knowing extension

    Quote Originally Posted by MarvinP View Post
    Hi,

    I think this is a very easy problem if you use Power Query instead of VBA. Read:
    https://www.thebiccountant.com/2017/...inks-to-excel/
    http://efficiency365.com/2015/03/18/...iew-hyperlink/
    Thanks for introducing me to Power Query. But it doesn't help as i want hyperlinks to local files in folder not to web urls. Beside i want hyper links to all the files which appear both in my naming list in column A and in folder. If there is a name in column A but there is no corresponding file in folder i want to skip it.

    Thanks

  5. #5
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Re: Automatically hyperlink to file without knowing extension

    Thanks for your reply

    I already tried using

    Please Login or Register  to view this content.
    and then merging it with this
    Please Login or Register  to view this content.
    but due to almost zero knowledge of coding i don't know how to do it.

    Your solution will only create a list of hyperlinks which i have to manually copy and past in-front of each file. In some cases i have 20,000 names in my list whereas there are only 15,000 files. so if i use your solution i have to manually find those 5,000 missing name and apply the hyperlinks to rest.

    This is exactly what i need if it gives me option of choosing folder.
    Please Login or Register  to view this content.

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Automatically hyperlink to file without knowing extension

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  7. #7
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Re: Automatically hyperlink to file without knowing extension

    Thank you very very much. It worked like a charm.
    Last edited by AliGW; 07-21-2018 at 02:39 AM. Reason: Unnecessary quotation removed.

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Automatically hyperlink to file without knowing extension

    Glad to help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA to automatically Hyperlink cell text with similar file name
    By ahmed1234 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-25-2018, 09:50 AM
  2. How to print the file in the hyperlink automatically
    By reimar_rem in forum Excel General
    Replies: 4
    Last Post: 01-23-2018, 02:20 AM
  3. [SOLVED] How to Enter Name of File and Automatically Turn It Into Hyperlink to Said File
    By Pheonix843 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 04-02-2016, 02:37 AM
  4. Automatically hyperlink to file without knowing extension
    By lozoroo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-24-2013, 10:00 AM
  5. Replies: 0
    Last Post: 01-13-2013, 06:21 PM
  6. Replies: 0
    Last Post: 11-16-2011, 01:35 PM
  7. Hyperlink in excel spreadsheet to email file automatically
    By sparker717 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2006, 06:10 PM

Tags for this Thread

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