Check and match cell value to folder/subfolder returning a hyperlink.

[Nilletnilsson]

Hello,

I have an excel with about 1,500 rows, each row have a unique ID in the A column. I also have folders on my computer matched to theese unique IDs.
Instead of going through all 1,500 unique ID's manually and hyperlink to the correct map i want to make a script that makes the work alot easier.
The macro/script needs to check the value of a cell in excel, searches in my folder and subfolders for a folder with the same name, then return a hyperlink to that folder in another cell if it's a match.
If the script can't find a map with the same name it should not return anything.


Excelandmapps.jpg
On this image you can see how the excel/folders are built.

I would by so thankfull for your help.

[djbomaha]

Hi Nilletnilsson,

The code below may do what you are asking for. It will look for a list of folder names in Column A of the activesheet and then put hyperlinks to those folders in Column C if they exist.

Please Login or Register  to view this content.

Hope it helps,

Dan

[einspace]

Hi, I'm trying to test this code, but I can't make it work. I should enter some numbers or letters on Column A and when I hit Enter, it will show a hiperlink on Column C if it exist on my hard drive, Right? Thanks.

[Kenneth Hobson]

Here is my approach. I did not turn off and on the Application items to speed it up as Dan did not nor did I resize columns nor allow user to select the parent folder's path.

Obviously, you need to change "x:\t" to your path.

Please Login or Register  to view this content.

[einspace]

Ok, now I got Dan's code to work. Yours worked the first time I tried. The second one didn't want to work. Don't know why.
Thank you.

[djbomaha]

Hi guys,

Just a note on my code, when I initially wrote it, it was checking all drives and subfolders just to see how it went and the "C:\Windows" folder and "C:\Program Files" folders were beating it into submission. I think I was up to 26,000 folders by the time I finally stopped the code and I made the statusbar active just to see if it was still running.

To get isolate the number of folders it checks, I put in the part that lets the user pick the folder to start from. Just be aware that if you pick an entire drive, such as the drive your operating system is on, it could take it an eon to actually get through it simply by the number of folders and subfolders on that drive because it will find them all.

Anyway, I'm glad some people tried it and hoped it provided some assistance.

Thanks,

Dan

[Nilletnilsson]

Hello,

djbomaha, i can't get your code to run smoothly.
The script loops through the maps multiple times before it completes, when it completes it writes the shortcut "C:xxx\xxx\xx\xxxxxx" for each subfolder found in the first folder in column A resulting in 500 lines of "C:xxx\xxx\xx"

[Nilletnilsson]

And Kenneth your code doesn't seem to work at all for me. It just runs without anything happening.

[djbomaha]

Hi Nilletnilsson,

I tested it on my end with 1500 folders and it works okay. It sounds like it may be capturing a large amount of folders on your end and that may be the issue. I posted a video on youtube showing an example of it working on my end that might let you see a performance issue you are seeing when you run it. Here is the video link if you want to view it. It is a couple of minutes long.

https://youtu.be/w9wmpLLuWH4

Thanks,

Dan

[Kenneth Hobson]

Did you change the path as I explained? Obviously, it works for me or I would not have posted it.

As Dan said, if you have a large number of files and folders, then some wait time might be needed so that the macro does not out run itself.

[Nilletnilsson]

Hello Dan,

Thank you for the video.
I saw that you had 1500 different maps. Im doing it with 3,500 maps. 1main map 35 submaps and the rest of the 3464 maps are divided in the 35 submaps.
When i run the script just like you do it takes an enourmous amount of time. I've had it running for about 6 hours without any results. It looks like it's just looping if i watch the task bar in excel at the bottom left corner.
I know i have twice the amount of maps that you do but yours only took 1min, there must be something wrong?
Maybe it's the way i've built my map system on the computer?

Im running on a pretty decent computer so i think it's wierd that it takes that amount of time.

[djbomaha]

Hi Nilletnilsson,

The attached workbook has the example of the program working on my end. I also posted another video showing it at work, so you can use it as reference. I adjusted the programming so it captures over 3500 folders and it doesn't have any trouble. I did add a number of cycles limitation and also put in programming that provides for a 1 second delay to help the program not crash.

Anyway, if you are still looking for a solution, you can look at the video and use this example workbook to verify it works like it does on my end. Here is the link to the video: https://www.youtube.com/watch?v=q5eEB-kgS2k

Hope that helps,

Dan

[Nilletnilsson]

Hello Dan,

I tried it with the excel file you sent and now it works perfectly! Took 1min to generate all maps.
I can't tank you enough, you've made my life so much easier.

A big thank you!

[einspace]

Hi Dan

I tried the code following your video and it worked great! I also tried the workbook you posted and worked perfect! Thank you very much for sharing this.

[djbomaha]

Glad to hear we found a solution. It was a fun problem to work with since I learned a few things myself.

Thanks,

Dan