title: Excel VBA efficient get file names function
body: <p>I need to get a collection of file names from a folder on a remote server using VBA in excel 2010. I have a function that works and in the majority of cases it would do the job, however the remote server frequently has terrible, <strong>terrible</strong> network performance issues.  This means that looping through say 300 files to put their names into a collection can take 10 minutes, the number of files in the folder is likely to grow to thousands so this is not workable, I need a way to get all of the file names in a single network request and not looping.  I believe its connecting to the remote server that is taking the time so a single request should be able to get all of the files in one pass fairly quickly.</p>
  
  <p>This is the function I currently have in place:</p>
  
  <pre><code>Private Function GetFileNames(sPath As String) As Collection
  'takes a path and returns a collection of the file names in the folder
  
  Dim oFolder     As Object
  Dim oFile       As Object
  Dim oFSO        As Object
  Dim colList     As New Collection
  
  Set oFSO = CreateObject("Scripting.FileSystemObject")
  Set oFolder = oFSO.GetFolder(folderpath:=sPath)
  
  For Each oFile In oFolder.Files
      colList.Add oFile.Name
  Next oFile
  
  Set GetFileNames = colList
  
  Set oFolder = Nothing
  Set oFSO = Nothing
  
  End Function
  </code></pre>