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

**Transformer** · 03-23-2012, 10:28 PM

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

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

Code:

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

**Rick Rothstein** · 03-23-2012, 10:50 PM

Originally Posted by Transformer

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

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 12.0 Macro;HDR=YES"";"
    adoRcdSource.Open "Select * into [" & strInputSheetName & "] From [" & strInputSheetName & "] IN '" & strInputFileFullName & "'[Excel 8.0;HDR=YES;]", adoConnection
       
End Sub

Code:

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"

I am not a "database person" myself, so I do not know the answer to this question... Do any of the three text sections I highlighted in red in any way restrict the functionality of your subroutine to a specific version of Excel or, perhaps, a specific version of some database engine?

**Admin** · 03-23-2012, 10:58 PM

Hi Transformer,

Welcome to board !!!

Couple of things.

Tell the users that they need to add the references
1. Microsoft ActiveX Data Objects 2.x Library
2. Microsoft ActiveX Data Objects Recordset 2.x Library

secondly, you need '$' followed by the sheet name, like

Code:

adoRcdSource.Open "Select * into [" & strInputSheetName & "] From [" & strInputSheetName & "$] IN '" & strInputFileFullName & "'[Excel 8.0;HDR=YES;]", adoConnection

Expect many more from you !!

**Rajan_Verma** · 03-23-2012, 11:05 PM

Originally Posted by Rick Rothstein

I am not a "database person" myself, so I do not know the answer to this question... Do either, or both, of the text sections I highlighted in red in any way restrict the functionality of your subroutine to a specific version of Excel or, perhaps, a specific version of some database engine?

Hi Rick,

this will work on 2007 and 2010

Rajan

**Transformer** · 03-23-2012, 11:27 PM

Oh yes I forgot.Thanx Admin

**Rick Rothstein** · 03-23-2012, 11:44 PM

Originally Posted by Rajan_Verma

Hi Rick,

this will work on 2007 and 2010

Rajan

Thanks for the info. I mainly use XL2003... can either, or both, of those numbers be changed to make it work on XL2003? What about earlier versions of Excel?

**Rajan_Verma** · 03-23-2012, 11:55 PM

Hi Rick
you can change the Provider .
Replace "Provider=Microsoft.ACE.OLEDB.12.0" with "Provider =Microsoft.Jet.OLEDB.4.0"

Rajan.

**Transformer** · 03-24-2012, 12:01 AM

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

**Rick Rothstein** · 03-24-2012, 12:33 AM

Originally Posted by Transformer

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)...

Code:

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

**Transformer** · 03-24-2012, 10:46 AM

Hi Rick,

Revised Sub is

Code:

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
    Dim strFileExt      As String
    
    
    If Len(Dir(strInputFileFullName)) = 0 Then
        MsgBox "Input file does not exist"
        Exit Sub
    End If
    strFileExt = Mid(strOutPutFileFullName, InStrRev(strOutPutFileFullName, ".", -1, vbTextCompare), Len(strOutPutFileFullName))
    If strFileExt = ".xlsx" Then
        ExtProperties = "Excel 12.0 XML"
    Else
        ExtProperties = "EXCEL 8.0"
    End If
    If CDbl(Application.Version) > 11 Then
      Provider = "Microsoft.ACE.OLEDB.12.0"
    Else
       Provider = "Microsoft.JET.OLEDB.4.0"
    End If
    
    adoConnection.Open "Provider=" & Provider & ";Data Source= " & strOutPutFileFullName & ";Extended Properties=""" & ExtProperties & ";HDR=YES"";"
    adoRcdSource.Open "Select * into [" & strInputSheetName & "] From [" & strInputSheetName & "$] IN '" & strInputFileFullName & "'[" & ExtProperties & ";HDR=YES;]", adoConnection
       
    
    adoConnection.Close
    
    Set adoRcdSource = Nothing
    Set adoConnection = Nothing
    
End Sub