Hi Karl,

Just before that line in code, add a Debug.Print to see at what folder it fails:

Debug.Print StartingFolder.Path

NewRow.Cells(1, 1).Hyperlinks.Add Anchor:=NewRow.Cells(1, 1), Address:=StartingFolder.Path, TextToDisplay:=StartingFolder.Path

In Immediate Window, you should see the latest folder listed.

You have to investigate that folder, there seems to be a specific issue with that folder on your computer, the code works fine for me.

Is that folder where the code breaks on OneDrive for example?

(there is no file attached, make sure you start upload after selecting a file)

Hi Catalin

I have downloaded three different variations of the file from this site (and uploaded them here for your reference):

Create-Hyperlinked-List-of-Files-in-Subfolders.xlsm - worked

Create-Hyperlinked-List-of-Files-in-Subfolders-In-One-Column.xlsm - worked

Create-Hyperlinked-List-of-Files-in-Subfolders-Table Version.xlsm - fails at 32,767 rows

I currently have 53,376 files in a folder (with about 8,000 subfolders) that I have tried this with. In another folder I have 44,056 files (with about 11,000 subfolders).

The file, Create-Hyperlinked-List-of-Files-in-Subfolders-Table Version.xlsm, always fails at row number 32,767 regardless of which folder I run it on. So, it is not a problem with any particular folder. Both folders are in daily use with more files/folders being added all the time. Is it something to do with the generated list being in two columns instead of one? Or is there a limit on how many hyperlinks can be put into a single sheet?

The two files that worked went down to row 61,704 but only the files are hyperlinked, the folders are not. There are 8,325 rows with folder names that are not hyperlinked - and 53,376 rows with file names that are hyperlinked.

Hi Karl,
32767 is the maximum value of a 16-bit signed integer. (2^15-1)
However, there is no parameter in the code declared as Integer, OffsetRow is declared as Long type.

There is a limitation in excel: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

Knowing that on each row we have 2 hyperlinks (folder link and file link), you will hit this limit on row 32766 of the table, this is the cause of error in your case. Microsoft should add a more clear description of the error,  hard to see the problem from the text displayed: "Application-defined or object-defined error."

A workaround will be to add hyperlinks only to second column (files):

However, if you will have more than 65530 files in the root folder and subfolders, this workaround will also fail.

Best solution:
Load the results in the table as text, not hyperlinks, but add a right click event to followHyperlink using cell text:

Updated the code as well to load data into array, instead of adding a list row at each iteration, code will be much faster. Instead of performing a sheet operation at each iteration, there will now be only 1 worksheet action to paste the array of results into a resized table.

There is no hyperlink in the sheet in this version. Instead of using hyperlinks, you just have to right click a cell, this will trigger the right click event that will open the file/folder.

Could you modify the Table Version of the file so that:

Column A = plain text list of folders

Column B = plain text list of files

Column C = Hyperlink(Column A)

Column D = Hyperlink(Column A&""&Column B)

with columns A and B being hidden and columns C and D being visible?

There does not appear to be the 65,530 limit when you use hyperlink in a formula vs hard-coded hyperlinks.

Here it is

That's excellent - thanks!

There does not appear to be the 65,530 limit when you use hyperlink in a formula vs hard-coded hyperlinks.

You still have to consider the impact of 120.000 formulas on performance, the right click action is still the best choice.

I've created some variants.

Right click version: I have modified it to just apply the right-click hyperlink function to cells A2 to B1048576 rather than the whole sheet. However, this is still limiting becuase I often right-click to copy a cell, and the hyperlink function interferes with that.

Double click version: I changed the right-click to a double-click function, which is a bit more intuitive, and similarly modified it to only apply to cells A2 to B1048576. However, the file name column shows the full path, not just the file name, which is not quite what I want.

Left click version: I have removed the function from the sheet but left the module in. I added hyperlink columns (C & D) linking to columns A & B, which are hidden. There does not appear to be much impact on performance from what I've seen so far.

Left click file: approximately 93 seconds to run, listing 53,432 rows with 2 hyperlink columns per row, plus 4 additional formula columns.

Double click file: approximately 180 seconds to run on the same folder - 53,432 rows with 4 additional formula columns but no hyperlink columns.

The hyperlink version works at about twice the speed of the non-hyperlink version. (I ran the tests twice for each file.)

If I try to run this on the root of a drive I get:

Run-time error '52':

Bad file name or number.

On Debug it highlights: CurrentFilename = Dir(TargetFiles, 7)

I think it must be due to either (or both) of these folders:

$RECYCLE.BIN

System Volume Information

Can the script be updated to ignore/bypass those folders, please?