VBA - Open files and copy certain data

[prabby25101981]

Hi Guys,

Can someone guide as to how I can one by one open all files in one folder, copy certain data from each of the file, and paste it in another file through VBA?

I am pretty much OK with VBA but have never used file and folder codes...

Thanks!

 

[daverunt]

Something along these lines?

'Select ResultFile
    ResultFile = Application.GetOpenFilename("Excel Files (*.xls*)," & _
    "*.xls*", 1, "Select Result file", "Open", False)
 
'If the user cancels then exit
    If TypeName(ResultFile) = "Boolean" Then
        Exit Sub
    End If
 
'Open Result File
    Workbooks.Open ResultFile
    ResultFile = Dir(ResultFile)
 
 
'Select source file directory
  SourceFile = Application.GetOpenFilename("Excel Files (*.xls*)," & _
    "*.xls*", 1, "Select Source Files", "Open", False)
 
'If the user cancels then exit
   If TypeName(SourceFile) = "Boolean" Then
        Exit Sub
    End If
 
'Set xls as SourceFile
      SourceFile = Dir("*.xls")
 
  Do While SourceFile <> ""
 
       x = 1
       Workbooks.Open SourceFile
       Set XLSFile = ActiveWorkbook
       x = x + 1
 
       ---------------Do something with the source file-------------------       
'Activate the result file               
       Windows(ResultFile).Activate
 
     ---------------Do something with the result file-------------------    
 
 
 'Close open source file
    XLSFile.Close False
 
  SourceFile = Dir
  Loop
End Sub