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?
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

ShumsDistinguished Expert - 2017Commented:

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

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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.