Excel to Excel Data transfer without opening any of the files(source or target)

[Transformer]

If you want to transfer data from one excel file to another then you can use the following procedure.
code:

Sub TransferData(strInputFileFullName As String, strOutPutFileFullName As String, strInputSheetName As String)
    
    Dim adoConnection   As New ADODB.Connection
    Dim adoRcdSource    As New ADODB.Recordset
   
    adoConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & strOutPutFileFullName & ";Extended Properties=""Excel 8.0;HDR=YES"";"
    adoRcdSource.Open "Select * into [" & strInputSheetName & "] From [" & strInputSheetName & "] IN '" & strInputFileFullName & "'[Excel 8.0;HDR=YES;]", adoConnection
       
End Sub

call TransferData("E:\source.xls","E:\Destination.xls","shtData")

If target workbook is not available in the destination drive then it will create it automatically and transfer the data.
eg. if in above example if Destination.xls is not available then it will create it with the sheet "shtData"


Regards,
Transformer