+ Reply to Thread
Results 1 to 7 of 7

automatic hyperlink of file list in excel

  1. 01-10-2012, 06:13 AM #1
    maheshwow
    maheshwow is offline
    Registered User
    Join Date
    01-05-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2003
    Posts
    22

    automatic hyperlink of file list in excel

    I require help on macro , couldnt find this issue anywhere else in this forum.

    I have a list of names(around 500 mech parts) in excel 2003.Eg: "5922 X0E". These names can be converted to filenames if the spaces in between are removed. I have pooled all the files with these filenames in server directory(\\Server\ABC).

    I need a macro which automatically hyperlinks the excel list to original files (which is .dwg). If i could assign a software with which the files would open, it would be added advantage.

    so basically:
    1. remove spaces in column A text
    2. hyperlink to file with this name, in network directory
    3. assign software to open the link with (solidworks viewer)


    have been searchin for solution since 1 week Pl give me some magic script.
    Last edited by maheshwow; 01-10-2012 at 10:17 AM.
    Reply With QuoteRegister To Reply
  2. 01-10-2012, 06:43 AM #2
    Domenic
    Domenic is offline
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: automatic hyperlink of file list in excel

    Assuming that Column A contains the data, starting at Row 2, maybe something like this...

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 01-10-2012, 07:24 AM #3
    maheshwow
    maheshwow is offline
    Registered User
    Join Date
    01-05-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: automatic hyperlink of file list in excel

    That was quick & very helpful solution. Thankyou very much Mr.Domenic.
    please also give me below tweak for a problem which i hadnt foreseen.

    change font color to "red" for hyperlinks with invalid target.

    This could also be run as a second macro to verify generated links using the code you've provided. (since i might've missed to add some files in server folder)
    Reply With QuoteRegister To Reply
  4. 01-10-2012, 10:10 AM #4
    maheshwow
    maheshwow is offline
    Registered User
    Join Date
    01-05-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2003
    Posts
    22

    Thumbs up Re: automatic hyperlink of file list in excel

    Finally the complete code. merged your code with http://www.excelforum.com/excel-gene...yperlinks.html.
    Thanks to all for valuable information.


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 01-10-2012, 10:34 AM #5
    Domenic
    Domenic is offline
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: automatic hyperlink of file list in excel

    Actually, we can avoid the second loop...

    (UNTESTED)

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 01-11-2012, 10:04 AM #6
    maheshwow
    maheshwow is offline
    Registered User
    Join Date
    01-05-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: automatic hyperlink of file list in excel

    Thanks again Mr.Domenic!
    Reply With QuoteRegister To Reply
  7. 01-11-2012, 10:49 AM #7
    Domenic
    Domenic is offline
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: automatic hyperlink of file list in excel

    You're very welcome!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1