How to Read Multiple excel files and save data in single excel sheet

[upadhyay40]

Hi venkat,

Thanks for your help,

i written a code to read multiple excel sheet and save it properlly, function is

Code:

Function ExcelUtilities( )

Dim oFolder        
Dim aFile        
Dim FSO              
Set oXL = CreateObject("Excel.Application")        
Set objExcel = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")                
        

oXL.DefaultFilePath = "C:\Results"                
oXL.DisplayAlerts = False        
If FSO.FolderExists(oXL.DefaultFilePath) then            
     Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)                      
     For each aFile in oFolder.Files              
          If Right(LCase(aFile.Name), 4) = ".xls" Then                                      
              
                Set objWorkbook1= objExcel.Workbooks.Open(aFile.Name)
                Set objWorkbook3= objExcel.Workbooks.Open("C:\results.xls")
                rcount=objWorkbook3.Worksheets("Sheet1").UsedRange.rows.count
                rcount=rcount+1
                st="A"&rcount
                objWorkbook1.Worksheets("Results").UsedRange.Copy
                objWorkbook3.Worksheets("Sheet1").Range(st).PasteSpecial Paste =xlValues
                objWorkbook1.save
                objWorkbook3.save
                objWorkbook1.close
                objWorkbook3.close
                
            End If            
    Next      
    Set oFolder = Nothing        
    end if        
    oXL.DisplayAlerts = True        
    oXL.Quit        
    Set     oXL = Nothing

End Function

it works fine,
but when i run next time is there any process to delete all previous data from results.xls and save it once again from row 1. please take in consider

Thanks
Mahesh