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!

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.

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?

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

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

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.

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!

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