+ Reply to Thread
Results 1 to 16 of 16

Macro for making hyperlinks from auto generated folder dir.

  1. #1
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Macro for making hyperlinks from auto generated folder dir.

    Hi!

    I have a Macro that generates a list of all the files in a folder. After the list is generated i combine filename plus path and then make a hyperlink for the files. Allthough i would like the macro itself to make hyperlinks of the list it generates and place it where i want.

    The code is below:

    Please Login or Register  to view this content.
    The macro gets the path from cell "J7" and stores it in Cell "E4" (dont really know why its nessesary but it works )
    then it list all the files starting in cell "I7" and down and then the macro is done.

    I would like it to make hyperlinks by using cell "J7" where the path is stored and list it makes from cell "I7" and down.
    and on top of that place the hyperlink starting in cell "C7" and down.

    Is that possible?

    Hope im being clear enough and thanks in advance for all the help!!

    Cheers!
    Last edited by lavinius; 01-17-2013 at 08:50 AM.

  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: Macro for making hyperlinks from auto generated folder dir.

    Hi Lavinius,

    I have changed the addresses used a but, but the code should be clear enough to change it around to meet your needs

    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you like my contribution click the star icon!

  3. #3
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Macro for making hyperlinks from auto generated folder dir.

    Hi OllieB! And thank you for your reply!

    Code works nice!

    I have one more question reguarding this function, Maybe you could assist me in this matter aswell?
    I have changed the parameters to get the links where i want them, and where the table is generated i have Collored cells to resemble a box, with header in cell "B6" ant then the list starts in cell "B7" and down, and say i makes 10 links to the files found in the folder.

    so the last link will be in cell "B16" and in cell under it cell "B17" i have a footer in a different collor.

    Is it possible to "expand" that "box", so if i add 2 new files in the folder. the code inserts 2 new rows above cell "B17" and under cell "B16" and puts the hyperlinks for thoese 2 new files in the newly added rows?

    Many Thanks!

    Cheers!

  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: Macro for making hyperlinks from auto generated folder dir.

    lavinius, would it not be smarter to keep the header in B6 but delete all rows beneath, and then, once all files have been written, add the footer line to the next line as opposed to inserting lines in between?

  5. #5
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Macro for making hyperlinks from auto generated folder dir.

    Oh, i also forgot,

    so that it would work the other way around. if 2 files are removed so that it delets the 2 then empty rows!

    Cheers

  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: Macro for making hyperlinks from auto generated folder dir.

    See post#4, basically delete all rows beneath the header, import all files, add a footer row

  7. #7
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Macro for making hyperlinks from auto generated folder dir.

    That would ofc work, but im thinking, isnt that more problematic? As the footer is different collor?

  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: Macro for making hyperlinks from auto generated folder dir.

    No that would actually be quite simple. Post the workbook and I will make the changes for you.

  9. #9
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Macro for making hyperlinks from auto generated folder dir.

    Hi!

    I have illustrated how i have it in my own file at work.
    So you get a clear picture.

    Thanks in advance once again!

    HyperlinkDir.xlsm

  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: Macro for making hyperlinks from auto generated folder dir.

    Lavinius,
    I see what you mean. The problem with your design is that the formatting gets disturbed whatever option I try. If I insert a line above row 23 an invalid border shows up from line 24. If I insert a line before line 22, I miss the separator border. You have not chosen a very easy format to work with. The only solution I see is to physically remove all rows below line 6, and re-establish the format for the detail and footer line(s) once all data has been imported. That will have to be tomorrow morning because I do not have time for the rest of the day.

  11. #11
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Macro for making hyperlinks from auto generated folder dir.

    Oh sorry, dont mind the Borders between the cells in row c or b

  12. #12
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Macro for making hyperlinks from auto generated folder dir.

    No problem, i appriciate you taking your time! i can live without the borders, asolong as cells in footer has border and cell in header as em, the cells in between i can live without.

    So no worries, my workday ends soon today anyhow!

    Thans for all the help so far!

  13. #13
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Macro for making hyperlinks from auto generated folder dir.

    Hi!

    MAaybe its easier to have one or two blank rows at the end of the table before the footer? For generating new rows for newly added files or deleting rows for when files are removed from folder?

    I can have one or two bland rows above footer

    Se new attached file

    Thanks in advance!
    Attached Files Attached Files

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

    Re: Macro for making hyperlinks from auto generated folder dir.

    Hi Lavinius,

    The problem is with the deleting of empty lines, I mean you want to auto-expand if there are more files and auto-contract when there are less files. Let's say for argument sake that there is only one file found in the directory. You would have one body-line and one footer-line. On a subsequent run you have more files so we need to auto-expand, however inserting rows will copy the partial format of the previous row and you table is messed up again.

    So in the end I went with my approach, which is to delete all rows 7 and down (I have moved the default path name to K6!), load the files, and re-apply the desired formatting, which is not that difficult.

    Please Login or Register  to view this content.
    I hope this works for you!

  15. #15
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Macro for making hyperlinks from auto generated folder dir.

    Good Morning OllieB!!

    That code works perfect and does everything and more than i hoped for in the start of this thread!!

    Cant thank you enough! Your skills in VBA is awsome!!!!

    Thank you and have a nice day m8!!

    Cheers!!!

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

    Re: Macro for making hyperlinks from auto generated folder dir.

    Glad to hear you liked it. You are very welcome.

+ 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