Compare file list with contents of folders then hyperlink

[ShirleyP]

Compare file list with contents of folder then hyperlink

Hi! This is my first posting, so please be gentle. My organization is using Excel as a database for multiple unrelated collections of files. The database spreadsheet for each collection has a unique identifier in Column A and data describing the identified file in subsequent columns. The identifiers in column A are filenames only (not extensions) for files with several different formats.
DOCUMENT.0000001
DOCUMENT.0000002
DOCUMENT.0000003
DOCUMENT.0000004
TASK.0000001
TASK.0000002
TASK.0000003
XM.VIDEO.0000007
XM.AUDIO.0000001
XM.PHOTO.0000003

The Documents and Tasks files are almost always pdf's and are always in a subfolder called Documents and Tasks. The video, audio and photo files can have any number of file extensions; they are all gathered in a subfolder called Multimedia. The spreadsheet of data is on the same level as the subfolders.
Documents and Tasks (a subfolder)
Multimedia (a subfolder)
spreadsheet.xlsx
The material will be processed in different offices, with different drive letters and different master file names, so I can't specify the path beyond the relative path indicated above.

I have adapted the following code, which works great for one folder. As soon as I try to run a similar code for the Multimedia folder, the data overwrites and reorders. The cells must stay in the same order to maintain the integrity of the data in adjascent columns.

Please Login or Register  to view this content.

I need to do two things: (1) confirm that the files in the list in Column A are actually in the folders (we receive the spreadsheet and files from an outside agency that has been known to make mistakes) and (2) hyperlink the contents of the cell in Column A to the file, showing the full filename and extension as the friendly_name. As mentioned above, the cells must remain in the same order.

I've had pretty good success cobbling together the automation on this project to this point, but despite googling like crazy, I haven't been able to make it work. I would very much appreciate any help or advise. Please use small words, as this is about day 10 trying to teach myself VBA! Thanks in advance for any guidance.
Shirley

[JBeaucaire]

Offhand the first error I noticed is that you are adding the pathseparator twice between the mypath and the subfolder:

Please Login or Register  to view this content.

You need one or the other, not both.

[JBeaucaire]

So lets be clear, all you want to do is

1) Check each filename in column A searching in two separate subfolders
2) For each file name found, mark column B with a clickable HYPERLINK
3) For each file not found, mark column B with "not found"

[ShirleyP]

Thanks very much. I've corrected that. I had removed it in a previous effort, and forgot to fix things again. I think I may have solved the problem. I replaced

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

On a small test, it is working for the renaming and file checking portion of the exercise, although the last several lines of code are still a mystery to me.

[JBeaucaire]

See post #3.

[ShirleyP]

Sorry I missed post 3! Yes that would work. A colleague I was reviewing this with pointed out the possibility that there will be a file in the folder that is not in the list. That has certainly happened before. So somewhere (sheet 2 or column N, or a message box perhaps), I would like the script to indicate that discrepancy. Is that possible?

[JBeaucaire]

What discrepancy? All I've outlined is a macro that will look for files of the specific names listed in column A and mark then as linked or not found. We would not be looking in the folders for anything else.

[JBeaucaire]

Untested, I'm thinking this should work:

Please Login or Register  to view this content.

[ShirleyP]

It took me a while to figure it out, but this worked great once I changed ThisWorkbook.Path to ActiveWorkbook.Path! Thank you so much! I didn't even know it was possible to use wildcards in this way. Any ideas on how I would handle files that are in the folders but are not listed on the spreadsheet? I need some way of identifying them to the user either in a message or on Sheet2?

[ShirleyP]

I spoke too soon. I didn't make my needs clear. I'm using VBA rather than a formula because these files will be copied to other locations. The path needs to be relative, not absolute. That is why I needed the file extensions. I think I can add the folder names and build the hyperlinks once I have the extensions. I'll play with the code some more, as I am learning a lot from examining it!