I have two workbooks. (i.e., "Workbook_Closed" and "Workbook_Open"). I would like to put code in Workbook_Open that will import cells "C2:AA120200" from "Sheet1" of "Workbook_Closed". What is the VBA code to do this for ADO, DAO and RDO. Also which one of these data accesses interfaces is the fastest, most stable and which one would you recommend and why for this process?

You all probably know all about each, but below is a link describing them:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:

Solution provided here did not help you?

Change the Path, File Name, Sheet Name and cell where you want to paste the copied cell in below code:
Sub CopyFromClosedWB()
Dim MyPath As String, MyFile As String, FileName As String
Dim ClosedWB As Workbook, OpenedWB As Workbook
Dim ClosedWS As Worksheet, OpenedWS As Worksheet

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Importing Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Set Variables
MyPath = "C:\Files\Samples\" 'Change you path here
MyFile = MyPath & "Test.xlsx" 'Change your file name here
FileName = Dir(MyFile)
Set ClosedWB = Application.Workbooks.Open(MyPath & FileName)
Set ClosedWS = ClosedWB.Worksheets("Sheet1") 'Change your sheet name here
Set OpenedWB = Application.ThisWorkbook
Set OpenedWS = OpenedWB.Worksheets("Sheet1") 'Change your sheet name here

'Copy from Closed to Opened WB
OpenedWS.Range("C200").PasteSpecial xlPasteAll 'Change "C200" to where you want to paste the copied cells
Application.CutCopyMode = False

'Close Closed Workbook
Application.DisplayAlerts = False
ClosedWB.Close SaveChanges:=False
Application.DisplayAlerts = True
FileName = Dir

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With

End Sub

Open in new window

This code uses ADO, for Excel 2016:
Public Sub Demo()
    AdoImport "Closed_Workbook_Path", "Sheet1", "C2:AA120200", Range("C2:AA120200")
End Sub

Private Sub AdoImport(strSourceFile As Variant, strSourceSheet As String, strSourceRange As String, rngTargetRange As Range)
    Dim objAdoConn As Object
    Dim objAdoRS As Object
    Dim strConnectionString As String
    Dim strSQL As String

    ' Create the connection string
    strConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;" & _
                          "Data Source=" & strSourceFile & ";" & _
                          "Extended Properties=""Excel 16.0;HDR=No"";"
    ' build the query string
    strSQL = "SELECT * FROM [" & strSourceSheet$ & "$" & strSourceRange$ & "];"

    On Error GoTo ErrHandler

    Set objAdoConn = CreateObject("ADODB.Connection")
    Set objAdoRS = CreateObject("ADODB.Recordset")

    objAdoConn.Open strConnectionString
    objAdoRS.Open strSQL, objAdoConn, 0, 1, 1
    ' Check to make sure we received data and copy the data
    If Not objAdoRS.EOF Then
        rngTargetRange.Cells(1, 1).CopyFromRecordset objAdoRS
        MsgBox "No records returned from : " & strSourceFile, vbCritical
    End If

    ' Clean up
    Set objAdoRS = Nothing
    Set objAdoConn = Nothing
    Exit Sub

    MsgBox "An error occured" & vbCrLf & vbCrLf & "Error # :" & Err.Number & vbCrLf & "Description :" & Err.Description, vbCritical, "Error"
    On Error GoTo 0

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ouestqueAuthor Commented:
Hey All! Thank you for the responses. :) My objective is to understand how to import data using each of these 3 interfaces as well as compare the stability and speed of each.

Hey Shums. Your code is great, but it  (opens workbook-->copies data-->closes workbook) and doesn't seem to use ADO, DAO or RDO.
MacroShadow: Cool ADO code. Do you also know how to do the same thing using RDO and DAO?
My recommendation is to use ADO.  There's no guarantee that the DAO or RDO libraries will be on Windows systems in the future.
ouestqueAuthor Commented:
Makes sense. Thanks guys!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.