Link to file if exist

Hi guys,

I am still struggling with this one.

I managed so far to extract all files from folder and to hyperlink them. Only problem left is rows are not matching up, data is not in order. I have now column "F" with all file names (that is original data) and new column "K" with with files that exist on folder.

I need to re-arrange columns "F" and "K" (so that values in "F2,3,4..." is equal to values in "K2,3,4...")

any help is appreciated,

here is my current code:

Please Login or Register  to view this content.

Thanks JBeaucaire,

I had that part working properly, my problem was placing files next to original values. That piece was essential since people needed to monitor files uploaded.

I managed somehow by looping every predefined value and then in each loop I am looping files again to find one with the same name (if exist). Everything is working as expected, but drawback is really long time to populate sheet ( I am having thousands of files in one sheet)

Here is my final code in case someone need it, and if anybody has idea how I can speed this up a bit I'll really appreciate.

Thanks again,

Please Login or Register  to view this content.

Thanks JBeaucaire,

True, make sense completely. Any suggestions, this is my first code I ever did in Excel. I even had idea connecting this on web server and to register excel sheet as DB source, than with SQL I can do easily much more since I am more conversant with that.

I would prefer doing it completely with Excel, since it is much more convenient for people that are supposed to use it.

Thanks again for help, I really appreciate it.

Hi JBeaucaire,

Actually I found your example "Line Up Matches - Problem#2" which with combining with "Create Hyper linked Directory Listing" is perfect solution for me. I did some testing today and it looks perfect. I have testing excel document with 15 records and everything is working perfect, but when I run it on real excel document with 15 thousands records it messes up a bit my original records. It creates blanks and it doesn't match properly.

I have columns in my document "A" to "J" where rows cannot be sorted or changed in any way - they have to remain as they are. Then I am inserting files with hyperlinks in columns "K" to "N" where files are extracted alphabetically. Than I have couple of more columns from "O" to "S" where is also some original data which cannot be moved.

Original File name is in columns "F" and newly inserted Hyper linked document is in "K". Those two I have to line up.


When I use your "Line Up Matches" everything works perfect till last part where items are lining up (ps. I skipped "Sort both section so numbers are ascending in E and F" since I cannot sort my original data)

Here is my code I am using if you can have a look and give me any suggestion I'll really appreciate.

Thanks for all help

Please Login or Register  to view this content.

Thanks JBeaucaire,

I left it with loop as it was before, I just optimized it a bit, so it is sort of reasonable speed.

Thanks for help I really appreciate it