Trouble Updating a list of Files if a file no longer exists in the Folder/Sub Folder Dir.

I have a table of contents file that has a button to update it with new files if the file is not already in the list (See related post Here). Now I am trying to update the list if the file name in the list is not found in the Folder/Sub Folder Directory by changing the fill color and the font color of the file that is no longer in the directory. The purpose of this is to allow users to know that a file was deleted, and if it was deleted by accident we can contact IT to retrieve it for us.

I am not sure what I did wrong, but if you look at the sample file I have several files that get filled like they are not there and then it continues to add additional rows of formatting to the end of the list of files. I also cannot figure out why the specified range for filling is from A-E but what fills is B-F.

In the sample file, the line item "Test - File.docx" was added to a folder, the list was updated with the Call SearchFolders Routine commented out. I then deleted the test file and cleared the commenting for the Call SearchFolders Routine. and then lots of files ended up getting updated with formatting when only the one test file should have changed.

Here is the entire process. Everything worked as expected then I tried to add the SearchFolders routine to search for files based on the list.

Please Login or Register  to view this content.

Keebellah, I was able to use the Hyperlink as you suggested. Funny thing though is if I clear out the table and run it as if it were the first time it errors out but if the table has data in it and it is updated, it works fine. Luckily it wont ever be empty once I have it go live. I am curious to know why it does error out and after a lot of data has already been entered.

Here is wat I ended up using for the SearchFolders macro

Please Login or Register  to view this content.

Bernie, thanks I did not notice the relative range reference. I dropped the FileName reference and it worked perfect.

Keebellah, I figured it out by adding the following to the SearchFolders routine. I figured out that the error was being thrown because when the table populates, it ends up leaving a blank row in the table that does not have a hyperlink.

Please Login or Register  to view this content.

Keebellah - I figured out that even though the hyperlink address is correct in the cells and the files open when selected, VBA recognizes that the TOC file is in one of the folders in the hyperlink path and therefore sees it as a Relative path and not an absolute path. Stepping through the code and looing at the hyperlink address and seeing that it was starting with the Mechanical folder had me confused at first. But I came up with the following solution that seems to be working for now. I check to see if the hyperlink has the starting path dstcontrols.local, and if it does I continue, if it does not then I add the workbook path of the TOC to the variable that holds the hyperlink address.

Please Login or Register  to view this content.