Links to other files

[Officer_Bierschnitt]

Hi,

I know there is a possibility in Excel to display the paths and names of all files which this particular one links to.
My question is, is there any method - easier than typing it off - to get this info onto another sheet?

Background: We have a spiderweb of Excel_files on_site and no one knows what are the "right and left neighbours" of his/her Excel_file, so no one knows what he/she might break by introducing some change in his/her file.

Now, me and a colleague have developed a docu_sheet with a responsible for each list, a list of files that particular one draws from and the files that draw from this.
I now want to have IT make this into a template_file and drop it into the directories of each and every PC and laptop on_site so this sheet will become a part of each new list generated.


But expecting the employees to type the info from this dialog onto that new sheet could well be expected from them, but it is just more than can reasonably be expected to happen ... so I would like to offer some easier and more convenient way of getting that info there.

Thanks a lot!
Best regards,

Officer_Bierschnitt

[macropod]

It's easy-enough to extract a report of all files a given workbook links to, but no workbook contains any information about what other files link to it. Furthermore, it's not only Excel files you need to consider; Word and PowerPoint files, for example, might contain links to an Excel file.

[Officer_Bierschnitt]

Hi,

strange - when I try to quote your post, the system tells me I'm not logged in, even though I am. Posting a reply is obviously possible, though.

Well - would you mind telling me how to extract such a report? Thanks a lot!

(it is very important to provide a rel. easy way of getting that info on that sheet, else it isn't realistic to believe people are going to do it ...)

I'm aware that an Excel_file cannot "know" which other files, Excel or whatever, use its data. That information has to be filled in to the docu_sheet I have developed "backwards" - whenever somebody makes a link to an earlier file, he/she should fill in that info in that earlier file.


Best regards,

Officer_Bierschnitt

[macropod]

For example:

Sub Demo()
Dim StrLnks As String, i As Long, aLnks
With ActiveWorkbook
  On Error Resume Next
  aLnks = .LinkSources(xlExcelLinks)
  StrLnks = StrLnks & "Excel Links: "
  For i = 1 To UBound(aLnks)
    StrLnks = StrLnks & vbCr & aLnks(i)
  Next i
  aLnks = .LinkSources(xlOLELinks)
  StrLnks = StrLnks & vbCr & vbCr & "OLE Links: "
  For i = 1 To UBound(aLnks)
    StrLnks = StrLnks & vbCr & aLnks(i)
  Next i
End With
MsgBox StrLnks
End Sub

As coded, the above macro tests the active workbook and simply outputs what it finds to a message box. Getting the data onto a worksheet or output to a text file would be quite easy, but you obviously wouldn't want it overwriting your worksheet data! With some extra code, the process could be made to run against all Excel files in a folder and send the output, including each linking file's name, to a new file (e.g. Excel workbook/text file/Word document).

[Officer_Bierschnitt]

Hi macropod,

thanks again!
That "it would be quite easy to ..." is great to hear/read when you don't know how. You also said this whole thing was easy. Well, it is - once you know the code.
Like Columbus' egg, everything is easy to the one who knows how to do it. Building a house or knitting a scarf is also easy, still ...
Running that macro over all Excel_files is unfortunately not an option, IT would never allow it - but I can use it on one file at a time all right.
Well, I'll find out.

Thanks a lot!

Best regards,

Officer_Bierschnitt

[Officer_Bierschnitt]

Okay,

with some help of a knowledgeable colleague I have now adapted the code so that all the linked worksheets are written, one per line, into the docu_sheet.
Now I can take one workbook at a time, insert a VBA_module with this code, run it and remove it again afterwards. A bit tedious, but the easiest way we have.

Best regards,

Officer_Bierschnitt

[macropod]

Originally Posted by Officer_Bierschnitt

Running that macro over all Excel_files is unfortunately not an option, IT would never allow it - but I can use it on one file at a time all right.

The following macro allows you to select a folder and extract the link data for all Excel files in that folder.

Sub Demo()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim strFolder As String, strFile As String, aLnks
Dim xlWkBk As Workbook, xlWkSht As Worksheet, i As Long, j As Long
Set xlWkSht = ThisWorkbook.Sheets(1)
strFolder = GetFolder
If strFolder = "" Then Exit Sub
j = xlWkSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
strFile = Dir(strFolder & "\*.xls", vbNormal)
While strFile <> ""
  If strFolder & "\" & strFile <> ThisWorkbook.FullName Then
    Set xlWkBk = Workbooks.Open(Filename:=strFolder & "\" & strFile, AddToMRU:=False, ReadOnly:=True)
    j = j + 1
    With xlWkBk
      xlWkSht.Cells(j, 1).Value = .FullName
      On Error Resume Next
      aLnks = .LinkSources(xlExcelLinks)
      For i = 1 To UBound(aLnks)
        j = j + 1
        xlWkSht.Cells(j, 2).Value = aLnks(i)
      Next i
      aLnks = .LinkSources(xlOLELinks)
      For i = 1 To UBound(aLnks)
        j = j + 1
        xlWkSht.Cells(j, 2).Value = aLnks(i)
      Next i
      j = j + 1
      .Close SaveChanges:=False
    End With
  End If
  strFile = Dir()
Wend
Set xlWkBk = Nothing
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

You'll probably find it takes only a few minutes to process even a very large folder.