title: Need to pull data from all files in a directory
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/7524064/excel-vba-question-need-to-access-data-from-all-excel-files-in-a-directory-wit">Excel - VBA Question. Need to access data from all excel files in a directory without opening the files</a>  </p>
  </blockquote>
  
  
  
  <p>So I need to pull data from multiple files in a directory and paste it into one excel file without having to open the files. Someone was nice enough to provide me with code on how to do that for a single file, now I just need to figure out how to do this for all the files in the directory. 
  This code is for a single cell and I need it for a range. That's not an issue but I just thought I'd mention it as well.</p>
  
  <pre><code>Dim rngDestinationCell As Range
  Dim rngSourceCell As Range
  Dim xlsPath As String
  Dim xlsFilename As String
  Dim sourceSheetName As String
  
  Set rngDestinationCell = Cells(3,1) ' or Range("A3")
  Set rngSourceCell = Cells(3,1)
  xlsPath = "C:\MyPath"
  xlsFilename = "MyBook.xls"
  sourceSheetName = "Sheet1"
  
  rngDestinationCell.Formula = "=" _
  &amp; "'" &amp; xlsPath &amp; "\[" &amp; xlsFilename &amp; "]" &amp; sourceSheetName &amp; "'!" _
  &amp; rngSourceCell.Address
  </code></pre>
  
  <p>So I'm assuming I have to do some sort of loop to run through all the files but I'm not sure how to do it. If someone can help me with this I'd really really appreciate it. </p>