Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    Member Transformer's Avatar
    Join Date
    Mar 2012
    Posts
    91
    Rep Power
    13

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

    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
    Last edited by Transformer; 03-24-2012 at 12:06 AM.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Transformer View Post
    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?
    Last edited by Rick Rothstein; 03-24-2012 at 12:22 AM. Reason: Highlighted an additional text section that I overlooked earlier

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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 !!
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Quote Originally Posted by Rick Rothstein View Post
    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

  5. #5
    Member Transformer's Avatar
    Join Date
    Mar 2012
    Posts
    91
    Rep Power
    13
    Oh yes I forgot.Thanx Admin

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rajan_Verma View Post
    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?

  7. #7
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Hi Rick
    you can change the Provider .
    Replace "Provider=Microsoft.ACE.OLEDB.12.0" with "Provider =Microsoft.Jet.OLEDB.4.0"

    Rajan.
    Last edited by Rajan_Verma; 03-23-2012 at 11:56 PM. Reason: spellingMistake

  8. #8
    Member Transformer's Avatar
    Join Date
    Mar 2012
    Posts
    91
    Rep Power
    13
    Hi Rick,
    You can use this
    adoConnection.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source= " & strOutPutFileFullName & ";Extended Properties=""Excel 8.0;HDR=YES"";"

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Transformer View Post
    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

  10. #10
    Member Transformer's Avatar
    Join Date
    Mar 2012
    Posts
    91
    Rep Power
    13
    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
    Regards,

    Transformer

Similar Threads

  1. Replies: 4
    Last Post: 06-09-2013, 01:43 AM
  2. Macro to copy data from a set of excel files
    By Sreejesh Menon in forum Excel Help
    Replies: 5
    Last Post: 11-15-2012, 11:17 AM
  3. Macro for Opening files and copy the contents of the File
    By ravichandavar in forum Excel Help
    Replies: 16
    Last Post: 08-15-2012, 09:17 PM
  4. Replies: 1
    Last Post: 06-26-2012, 08:17 PM
  5. Collate Data from csv files to excel sheet
    By dhiraj.ch185 in forum Excel Help
    Replies: 16
    Last Post: 03-06-2012, 07:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •