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

[Rick Rothstein]

Hi Rick,
You can use this
adoConnection.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source= " & strOutPutFileFullName & ";Extended Properties=""Excel 8.0;HDR=YES"";"

Thanks Transformer and Rajan_Verma for the extra information. Without actually trying your code out yet, but based on the extra information you have provided, the following code should be able to work on XL2003 or above (I don't know about earlier versions of Excel because I am not sure whether the extra information covers them or not)...

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