title: Getting Cell Value from Closed Workbooks in a Directory - How to Loop Through All Existing Files?
body: <p>I have a working code to retrieve a specific cell in a closed workbook when I specify the directory. I'm trying to get this code to loop through all the existing workbooks in said directory, retrieving the same cell from each file.</p>
  
  <p>This is the code I have so far with the loop (I'm including the function used as well):</p>
  
  <pre><code>Private Function GetValueFromClosedWorkbook(path, file, sheet, ref)    
      Dim arg As String, xFolder As String
  
      If Right(path, 1) &lt;&gt; "\" Then path = path &amp; "\"
  
      If Dir(path &amp; file) = "" Then
          GetValueFromClosedWorkbook = "File not found."
          Exit Function
      End If
  
      arg = "'" &amp; path &amp; "[" &amp; file &amp; "]" &amp; sheet &amp; "'!" &amp; _
      Range(ref).Address(, , xlR1C1)         'create the argument
      GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg)
  End Function
  
  
  Sub TestGetValueFromClosedWorkbook()        
      Dim p As String, xFolder As String        
      Dim s As String, a As String, f(1 To 2) As String, z As Long
  
      xFolder = "\\generic path"
  
      For z = 1 To 2
          s = "Sheet1"
          a = "A1"
          p = xFolder '&amp; "\*.xlsx"
          f(z) = Dir(p &amp; "\*.xlsx")  
  
          Do While f(z) &lt;&gt; ""
              ActiveSheet.Range("A" &amp; (z + 7)) = GetValueFromClosedWorkbook(p, f(z), s, a)   
              f(z) = Dir()
          Loop
      Next z
  End Sub
  </code></pre>
  
  <p>This gets the correct cell, and then pastes in the active worksheet in cell A8.</p>
  
  <p>The only issue is that when it loops through the second file, it still gets the cell from the first file. Why is that happening? Or how can I ensure that the second loop it retrieves the cell from the second workbook?</p>