title: VBA- Opening hyperlinks from a List to copy specific data into a master sheet
body: <p>I currently am using the below code to generate a list of hyperlinks from a specific folder. I am trying to figure out how to access each of those hyperlinks to copy and paste certain data into a master spreadsheet then close the document in a loop. The data is located on the same tab for each file but In each file the number of Columns are always the same but not always the number of rows. Im trying to figure out how to capture all of that info into a master sheet without blanks and without overwriting data from the file before.</p>
  
  <pre><code>Option Compare Text
  Option Explicit
  
  Function Excludes(Ext As String) As Boolean
       'Function purpose:  To exclude listed file extensions from hyperlink listing
  
      Dim x, NumPos As Long
  
       'Enter/adjust file extensions to EXCLUDE from listing here:
      x = Array("exe", "xlsm", "xml")
  
      On Error Resume Next
      NumPos = Application.WorksheetFunction.Match(Ext, x, 0)
      If NumPos &gt; 0 Then
          Excludes = True
      Else
          Excludes = False
      End If
      On Error GoTo 0
  
  End Function
  
  Sub HyperlinkFileList()
       'Macro purpose:  To create a hyperlinked list of all files in a user
       'specified directory, including file size and date last modified
       'NOTE:  The 'TextToDisplay' property (of the Hyperlink object) was added
       'in Excel 2000.  This code tests the Excel version and does not use the
       'Texttodisplay property if using XL 97.
  
      Dim fso As Object, _
      ShellApp As Object, _
      File As Object, _
      SubFolder As Object, _
      Directory As String, _
      Problem As Boolean, _
      ExcelVer As Integer, _
      TotalD As String, _
      Wb As Workbook, _
      Ws As Worksheet
  
  
       'Turn off screen flashing
      Application.ScreenUpdating = False
  
       ' Clear sheet
      Cells.Delete Shift:=xlUp
      'Useless : Range("A1").Select
  
       'Create objects to get a listing of all files in the directory
      Set fso = CreateObject("Scripting.FileSystemObject")
  
       'Prompt user to select a directory
      Do
          Problem = False
          Set ShellApp = CreateObject("Shell.Application"). _
          Browseforfolder(0, "Please choose a folder", 0, "D:\JBOSS\Testdossier")
          'Browseforfolder(0, "Please choose a folder", 0, "D:\JBOSS\Testdossier")
  
          On Error Resume Next
           'Evaluate if directory is valid
          Directory = ShellApp.self.Path
          Set SubFolder = fso.GetFolder(Directory).Files
          If Err.Number &lt;&gt; 0 Then
              If MsgBox("You did not choose a valid directory!" &amp; vbCrLf &amp; _
              "Would you like to try again?", vbYesNoCancel, _
              "Directory Required") &lt;&gt; vbYes Then Exit Sub
              Problem = True
          End If
          On Error GoTo 0
      Loop Until Problem = False
  
       'Set up the headers on the worksheet
      With ActiveSheet
          With .Range("A1")
              .Value = "Listing of all files in:"
              .ColumnWidth = 40
               'If Excel 2000 or greater, add hyperlink with file name
               'displayed.  If earlier, add hyperlink with full path displayed
              If Val(Application.Version) &gt; 8 Then 'Using XL2000+
                  .Parent.Hyperlinks.Add _
                  Anchor:=.Offset(0, 1), _
                  Address:=Directory, _
                  TextToDisplay:=Directory
              Else 'Using XL97
                  .Parent.Hyperlinks.Add _
                  Anchor:=.Offset(0, 1), _
                  Address:=Directory
              End If
          End With
  
          With .Range("A2")
              .Value = "File Name"
              .Interior.ColorIndex = 15
              .ColumnWidth = 50
              With .Offset(0, 1)
                  .ColumnWidth = 15
                  .Value = "Date Modified"
                  .Interior.ColorIndex = 15
                  .HorizontalAlignment = xlCenter
              End With
              With .Offset(0, 2)
                  .ColumnWidth = 12
                  .Value = "File Size (Kb)"
                  .Interior.ColorIndex = 15
                  .HorizontalAlignment = xlCenter
              End With
  
          End With
      End With
  
       'Adds each file, details and hyperlinks to the list
      For Each File In SubFolder
              If Not Excludes(Right(File.Path, 3)) = True Then
                  With ActiveSheet
                       'If Excel 2000 or greater, add hyperlink with file name
                       'displayed.  If earlier, add hyperlink with full path displayed
                      If Val(Application.Version) &gt; 8 Then 'Using XL2000+
                          .Hyperlinks.Add _
                          Anchor:=ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0), _
                          Address:=File.Path, _
                          TextToDisplay:=File.Name
                      Else 'Using XL97
                          .Hyperlinks.Add _
                          Anchor:=ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0), _
                          Address:=File.Path
                      End If
                      'Add date last modified, and size in KB
                      With .Range("A65536").End(xlUp)
                          .Offset(0, 1) = File.datelastModified
                          With .Offset(0, 2)
                              .Value = WorksheetFunction.Round(File.Size / 1024, 1)
                              .NumberFormat = "#,##0.0"
                          End With
                      End With
                      'Add Total From this file to current workbook
                      Set Wb = Workbooks.Open(File)
                      Set Ws = Wb.Sheets("Summary")
                  On Error Resume Next
  
  
                      Wb.Close
                      Set Wb = Nothing
                      Set Ws = Nothing
                  End With
              End If
      Next File
  
       'Turn back on screen updating
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>