List files from a folder that contain links to other files

[fudgel]

I have been tasked with moving files/folders from legacy (G:Drive) to SharePoint. A lot of excel files contain links to other excel files. Is there any way I can create a list of which files contain links within a folder? Rather than going into each file individually? Can a file be recognised as containing links without opening?

 

[fudgel]

It has created the tabs and the headers, and downloads one excel workbook, then errors

I've worked out the error. I had left the \ off the end of the file path. After running, it comes up with a different error message
It has created the tabs and the headers, along with path, workbook, cell count = 0 for all, and nothing in the linkslist tab (apart from headers)

 

[Herakles]

Put this line:

MsgBox rngIncFormula.Address

under this line:

If Not rngIncFormula Is Nothing Then

Can you post a screen shot of the message box that comes up.

 

[fudgel]

Put this line:

MsgBox rngIncFormula.Address

under this line:

If Not rngIncFormula Is Nothing Then

Look at the first cell in the range list and post the formula.

So, after adding this, it was coming up with errors like attached (listing absolute cells), which I couldn't x out of, or close. I have entered a filepath where I know there is a workbook with a link, and the following error and debug came up.

It has created the tabs, and headers, (opened the workbook) but not listed in the tab.

 

[Herakles]

All the hightlighted code does is write out each row in the LinksList worksheet.
It uses standard Excel functions that have been around for ages.

I'd like to look at the formula's that you have in the cells.

What happens if you comment those lines out?

Does the code proceed without error?

Replace the lines with Wslist.Cells(lngRow, 1).value = objFile

What is the formula in call A3?

I've got to go out now for a short while.

 

[fudgel]

All the hightlighted code does is write out each row in the LinksList worksheet.
It uses standard Excel functions that have been around for ages.

I'd like to look at the formula's that you have in the cells.

What happens if you comment those lines out?

Does the code proceed without error?

Replace the lines with Wslist.Cells(lngRow, 1).value = objFile

What is the formula in call A3?

I've got to go out now for a short while.

Some success today! I have replaced the lines:

Wslist.Cells(lngRow, 1).Resize(1, 7).Value = Array(objFile, _
Ws.Name, _
rngCell.Address, _
Replace(Mid(rngCell.Formula, 2), ")", "", 1), _
Replace(Left(arr(1), InStr(1, arr(1), "]", vbTextCompare) - 1), "[", "", 1), _
Mid(arr(1), InStr(1, arr(1), "]", vbTextCompare) + 1), _
Replace(Mid(arr(2), 2), ")", "", 1))

With: Wslist.Cells(IngRow, 1).value = objFile

Comes up with run-time error '424' Object required.

However: It completes the WorkbooksList worksheet - has all the info added into the columns ie: Path, Workbook, Cell Count
Adds the worksheet for LinksList and all headers, but only has entries in the first column (Location), all other columns empty.

Debug line is as attached.

 

[fudgel]

Some success today! I have replaced the lines:

Wslist.Cells(lngRow, 1).Resize(1, 7).Value = Array(objFile, _
Ws.Name, _
rngCell.Address, _
Replace(Mid(rngCell.Formula, 2), ")", "", 1), _
Replace(Left(arr(1), InStr(1, arr(1), "]", vbTextCompare) - 1), "[", "", 1), _
Mid(arr(1), InStr(1, arr(1), "]", vbTextCompare) + 1), _
Replace(Mid(arr(2), 2), ")", "", 1))

With: Wslist.Cells(IngRow, 1).value = objFile

Comes up with run-time error '424' Object required.

However: It completes the WorkbooksList worksheet - has all the info added into the columns ie: Path, Workbook, Cell Count
Adds the worksheet for LinksList and all headers, but only has entries in the first column (Location), all other columns empty.

Debug line is as attached.

Hi Herakles

Thank you for spending time on this with me, it is really appreciated.
Is there anything else you could think of that would get this to work? Also, is it possible to extract a full list of G Drive, instead of each individual folder?

 

[fudgel]

Hi Herakles

Thank you for spending time on this with me, it is really appreciated.
Is there anything else you could think of that would get this to work? Also, is it possible to extract a full list of G Drive, instead of each individual folder?