asked on

Auditing Excel spreadsheets for external links prior to migrating to Office 365

Hi All,
We are looking to migrate to Office 365, and as part of that we are looking to move the current files from a Windows based File Share to either SharePoint or OneDrive. (I understand that even using DropBox would be affected the same way)

We know that one department makes use of external links within spreadsheets, and I want to get an idea of how many other files are out there.

Does any one know of a way to find these files?
Is there software or a way to use windows search to find them?

Thanks in advance

crystal (strive4peace) - Microsoft MVP, Access

here is code to document workbook links to the Immediate (Debug) window:

Sub DocumentWorkbookLinks()
'161214 strive4peace

   Dim vLinks As Variant
   Dim i As Integer
   Dim iCount As Integer
   iCount = 0
   vLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
   If Not IsEmpty(vLinks) Then
      For i = LBound(vLinks) To UBound(vLinks)
         Debug.Print vLinks(i)
         iCount = iCount + 1
      Next i
   End If
   MsgBox "Documented " & iCount & " External links -- look at Immediate (Debug) Window", , "Done"
   
End Sub

Open in new window

you can open the workbook with the code, then activate another workbook, and run it

Steven Reid

ASKER

Thanks for that Crystal.

i was looking for something that I could point at the C: drive (for example) and find out what files have links in them.

crystal (strive4peace) - Microsoft MVP, Access

you're welcome, Steven

you could expand the code to loop through a list. Here is a free tool to make a list of all files, or just what is an Excel file (whatever pattern you give it) in an Access database.

http://www.accessmvp.com/strive4peace/DB_ListFiles.htm

ASKER CERTIFIED SOLUTION

Gary Spaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial