search directory, list files containing certain txt

Hi

Try this. It assumes that you are in the directory that contains the files you want to check.

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = ThisWorkbook.Sheets("Sheet1")
  Application.ScreenUpdating = False
  filess = Dir("*.xls")
  If filess <> "" Then
    Do
      If filess <> ThisWorkbook.Name Then
        Workbooks.Open Filename:=filess, UpdateLinks:=False
        alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
        If Not IsEmpty(alinks) Then
          For i = LBound(alinks) To UBound(alinks)
            outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            OutSH.Cells(outrow, 1).Value = ActiveWorkbook.Name
            OutSH.Cells(outrow, 2).Value = alinks(i)
          Next i
        End If
        ActiveWorkbook.Close savechanges:=False
      End If
      filess = Dir()
    Loop Until filess = ""
  End If
  Application.ScreenUpdating = True
End Sub

rylo

Originally Posted by rylo

Hi

Try this. It assumes that you are in the directory that contains the files you want to check.

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = ThisWorkbook.Sheets("Sheet1")
  Application.ScreenUpdating = False
  filess = Dir("*.xls")
  If filess <> "" Then
    Do
      If filess <> ThisWorkbook.Name Then
        Workbooks.Open Filename:=filess, UpdateLinks:=False
        alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
        If Not IsEmpty(alinks) Then
          For i = LBound(alinks) To UBound(alinks)
            outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            OutSH.Cells(outrow, 1).Value = ActiveWorkbook.Name
            OutSH.Cells(outrow, 2).Value = alinks(i)
          Next i
        End If
        ActiveWorkbook.Close savechanges:=False
      End If
      filess = Dir()
    Loop Until filess = ""
  End If
  Application.ScreenUpdating = True
End Sub

rylo

Thanks for your effort Rylo, but I have my confused head on again this morning, too much beer over the weekend. Where do I enter the 'link' I'm wanting the macro to search for? Or does it assume its checking the file for links to the workbook it's being run from? I tried to play around and try it either way, but I keep getting no results.

Hi

What that code will do is open all the files in the directory, and if they have links to any other files, the filename and the link reference will be put into sheet1 of the workbook containing the macro.

Once this has completed, you can search in the link references for any of the files you are chasing.

rylo

Originally Posted by rylo

Hi

What that code will do is open all the files in the directory, and if they have links to any other files, the filename and the link reference will be put into sheet1 of the workbook containing the macro.

Once this has completed, you can search in the link references for any of the files you are chasing.

rylo

First class, that should keep me going!! Many thanks.