+ Reply to Thread
Results 1 to 11 of 11

How to Auto-Hyperlink cells to folders with same name?

  1. #1
    Forum Contributor
    Join Date
    01-08-2013
    Location
    ankara
    MS-Off Ver
    Excel 2010
    Posts
    134

    Lightbulb How to Auto-Hyperlink cells to folders with same name?

    Untitled 1.jpg

    In my # Liste named excel file, I have a list of people (as you see on B column) and a folder for each of them with their names.

    I want this if you can help me:
    When I select a cell and press macro button, I want macro to create a hyperlink to the folder in same location as in picture.

    Extra:
    1. In addition to macro above, is it also possible to create a folder with same name? That would save my time from creating folder with same name.
    2. After a while I compress folders in rar format with same name and move to archieve folder. Is it possible something like: "if hyperlink doesn't work, go to archieve folder and hyperlink to a rar file there?"


    I am an amateur macro user and wonder about the limitations of VBA by my last two additional questions. Thanks in advance..

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: How to Auto-Hyperlink cells to folders with same name?

    You can use the code attached to create the folder and the hyperlink
    Please Login or Register  to view this content.
    To deal with folders being archived/compressed and hyperlinks becoming invalid as a result, I suggest you write a small routine that checks the validity of all hyperlinks on the workhseet and if the target folder cannot be found, change the hyperlink address to the archive folder.
    If you like my contribution click the star icon!

  3. #3
    Forum Contributor
    Join Date
    01-08-2013
    Location
    ankara
    MS-Off Ver
    Excel 2010
    Posts
    134

    Re: How to Auto-Hyperlink cells to folders with same name?

    error.jpg

    Thank you for your close interest but I got this error above. The color of "Folder" is black unlike Object and String. I guess somehow I need to define "Folder" first.

    I can understand what you wrote but can't activate Folder.

  4. #4
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: How to Auto-Hyperlink cells to folders with same name?

    Sorry my mistake. I changed the code to late binding but forgot the folder. Please change to type Object (just like fso).

  5. #5
    Forum Contributor
    Join Date
    01-08-2013
    Location
    ankara
    MS-Off Ver
    Excel 2010
    Posts
    134

    Re: How to Auto-Hyperlink cells to folders with same name?

    That works great OllieB. VBA amazed me after this job, ty

    Also meanwhile I was looking for how to define Folder and this answer from a website solved previous problem as well. I wrote here in case some other people want to use other way:

    You need to reference th project to
    Microsoft Scripting Runtime
    Tools -> References in the VBE
    Just hyperlink doesn't work on folders with # but I can rename path of course. (D:\Hazine\# Başvurular\Bitmeyenler)

  6. #6
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: How to Auto-Hyperlink cells to folders with same name?

    You basically have two possibilities. One is called early binding in which case you will have to set a reference to the Microsoft scripting runtime. When you use this method object types like the folder in our example are known to the compiler. The second method is called late binding. No reference is needed and variables are declared as objects. Both are valid approaches, each with their own advantages and disadvantages.

  7. #7
    Forum Contributor
    Join Date
    01-08-2013
    Location
    ankara
    MS-Off Ver
    Excel 2010
    Posts
    134

    Re: How to Auto-Hyperlink cells to folders with same name?

    I see what you mean.

    Do you have any suggestion about how to start VBA programming or a website which gives small codes as I requested here? I have qbasic, matlab and little JAVA background.

  8. #8
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: How to Auto-Hyperlink cells to folders with same name?

    I myself have learned by trying and using Google. I believe the first thread in this forum contains lots of information, links and books for people learning VBA

  9. #9
    Forum Contributor
    Join Date
    01-08-2013
    Location
    ankara
    MS-Off Ver
    Excel 2010
    Posts
    134

    Re: How to Auto-Hyperlink cells to folders with same name?

    thanks a lot

  10. #10
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: How to Auto-Hyperlink cells to folders with same name?

    Ps with your background it is going to be a piece of cake

  11. #11
    Forum Contributor
    Join Date
    01-08-2013
    Location
    ankara
    MS-Off Ver
    Excel 2010
    Posts
    134

    Re: How to Auto-Hyperlink cells to folders with same name?

    I will ask another tough question in another topic

+ 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