Re: File and Folder Index with Hyperlinks
Hi John W,
I've been using a slightly modified version of your code for quite a while (thanks, btw), and recently found a small issue. Namely, file addresses longer than 255 characters produce an error and are thus just 'skipped' without leaving any evidence (i.e. the corresponding folder may look empty when it's not).
Windows will just not allow you to create folder paths which are longer than 255 characters, so it only happens when the path is already quite close to the length limit, and the file name pushes it over the edge.
The File System Object iterator Folder.Files.Item just doesn't 'see' this long-address files, so the best I could do was print a warning in case the number of files in the folder (which includes these 'ghost' files) is bigger than the actual number of iterations:
'Add hyperlink for each file in this folder
...
Dim nfiles As Integer
...
'Add hyperlink for each file in this folder
nfiles = thisFolder.Files.Count 'actual number of files
n = 1
For Each fileItem In thisFolder.Files 'files with very long names will not be included in the iteration
destCell.Offset(n, 1).Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, 1), Address:=fileItem.Path, TextToDisplay:=fileItem.Name
n = n + 1
Next
'If any number of files were skipped, print a warning. The code could be modified to make a msgbox pop-up or something.
If nfiles > n - 1 Then
destCell.Offset(n, 1).Value = "Hidden files, path too long!!"
destCell.Offset(n, 1).Font.Color = vbRed
n = n + 1
End If
List_Folders_and_Files = n
...I tought I'd leave it here in case someone has stumbled upon the same issue.
Cheers.