Export Data From One Excel File to Another Without Opening Them

Posted: April 26, 2013 by Transformer in Excel, VBA
Tags: , , ,

Sometimes one might need to export data from one Excel file to another and if one wants to do so without opening any of those files (Source and destination files), then it can be done using the following procedure.

It has four parameters, last of which is optional:
1. strSourceFileFullName : Full name of the source file with extension. e.g. “C:\Challenge1.xlsx”
2. strTargetFileFullName : Full name of the target file with extension. e.g. “C:\Challenge2.xlsx”
3. strSourceSheetName : Sheet name from where data needs to be exported.
4. strTargetSheetname : Sheet name in the target file where data should be pasted. This is an optional parameter. If this parameter is not passed then a new sheet will be created with the same name as the source sheet.


Sub TransferData(strSourceFileFullName As String, _
                 strTargetFileFullName As String, _
                 strSourceSheetName As String, _
                 Optional strTargetSheetname As Variant)

    Dim adoConnection   As Object
    Dim adoRcdSource    As Object
    Dim Provider        As String
    Dim ExtProperties   As String
    Dim strFileExt      As String

    Set adoConnection = CreateObject("ADODB.Connection")
    Set adoRcdSource = CreateObject("ADODB.Recordset")
    If Len(Dir(strSourceFileFullName)) = 0 Then
        MsgBox "Input file does not exist"
        Exit Sub
    End If

    strFileExt = Mid(strTargetFileFullName, InStrRev(strTargetFileFullName, ".", -1, vbTextCompare), Len(strTargetFileFullName))

    If strFileExt = ".xlsx" Then
        ExtProperties = "Excel 12.0 XML"
    ElseIf strFileExt = ".xlsb" Then
        ExtProperties = "Excel 12.0"
    ElseIf strFileExt = ".xlsm" Then
        ExtProperties = "Excel 12.0 Macro"
    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

    If IsMissing(strTargetSheetname) Then
        strTargetSheetname = strSourceSheetName
    End If
    adoConnection.Open "Provider=" & Provider & ";Data Source= " & strTargetFileFullName & ";Extended Properties=""" & ExtProperties & ";HDR=YES"";"

    On Error GoTo Errorhandler
    adoRcdSource.Open "Select * into [" & strTargetSheetname & "] From [" & strSourceSheetName & "$] IN '" & strSourceFileFullName & "'[" & ExtProperties & ";HDR=YES;]", adoConnection
    adoRcdSource.Close
Errorhandler:
    If Err.Number = -2147217900 Then
        MsgBox "A sheet or a named range with the same name already exists in the target workbook. Data will not be copied.", , "Name Conflict"
    End If

    adoConnection.Close
    Set adoRcdSource = Nothing
    Set adoConnection = Nothing

End Sub

Note: If a file with the name ‘strTargetFileFullName’ is not found then it gets created automatically in the target folder but if you want to transfer data into an xlsm file than it must be there. It won’t be created automatically.
If you face any problems, email us or leave a reply.

 

 

Advertisements
Comments
  1. Venkat says:

    Hi

    I tried using the above given code, to transfer data from one workbook to another. However, i received as Invalid procedure or Call Argument for the line “strFileExt = Mid(strTargetFileFullName, InStrRev(strTargetFileFullName, “.”, -1, vbTextCompare), Len(strTargetFileFullName))”. I am not sure how exactly we need to assign the source & destination page. Please give me the code that would copy data from one work book to another workbook with the specific file name and from specific destination. The changes that I made was:

    ‘Set strSourceFileFullName = “C:\venkat\source.xlsm”
    ‘Set strTargetFileFullName = “C:\destination\mainglsheet.xlsx”
    ‘Set strSourceSheetName = “PR”
    ‘Set strTargetSheetname = “PR”

    Actually, i have a usrform created using VBA and when I click on a specific button, I would want the data from the specific work book moved to a work book which is in the shared folder. Please help me. I am strugling to find a solution from long time. There is a detailed question in one of the post that I had put out http://en.kioskea.net/forum/affich-728179-excel-vba#p730515 . Please help.

  2. Transformer says:

    call TransferData(“C:\venkat\source.xlsm”,”C:\destination\mainglsheet.xlsx”, “PR”,”PR”)

  3. Venkat says:

    Hi,

    Could you please provide me the entire code if possible. Please I am not that great in VBA so please help me out.

  4. Venkat says:

    Hey Transformer,

    I am sorry for my previous question. Please ignore it. The process works perfectly. However, I am going through a challenge wherein the data is being moved to different sheets everytime the user clicks the botton where the module is available. If could fine tune the code where the data gets moved to a specific worksheet and to the new row it would be helpful.

    Regards,

  5. Venkat says:

    To be more presise, it create a new PR shete as PR1, PR2 etc. Please help.

  6. Transformer says:

    Hey Venkat, Check you mails.

  7. Amitabha Chatterjee says:

    When I run the code, it shows database or object is read-only. But I check the property but both the file are not read only. So what i can do?

  8. Transformer says:

    Hi Amitabh, this error generally occurs when one tries to transfer data into an xlsm or csv file that does not exist already. xlsm/csv file will not be created automatically if it does not exist.If it is not the case then please share your workbooks here: mistertransformer@gmail.com so that i can understand the issue.

  9. do you remember ,i always ask this query , this also helped me today. 🙂

  10. ContactMe says:

    Using this code I am not able to make a connection with xls file. It says table is not in expected format.

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s