Import data from a workbook (ADO)

 2000-09-16    Import & Export    2    115

If you want to import a lot of data from a closed workbook you can do this with ADO and the macro below. If you want to retrieve data from another worksheet than the first worksheet in the closed workbook, you have to refer to a user defined named range. The macro below can be used like this (in Excel 2000 or later):

' call the macro like this:
' GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21", ActiveCell, False
' GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "MyDataRange", Range ("B3"), True

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, TargetRange As Range, IncludeFieldNames As Boolean)
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
'   this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
'   this will return data from any worksheet in SourceFile
' SourceRange must include the range headers
'
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    Set TargetCell = TargetRange.Cells(1, 1)
    If IncludeFieldNames Then
        For i = 0 To rs.Fields.Count - 1
            TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
        Next i
        Set TargetCell = TargetCell.Offset(1, 0)
    End If
    TargetCell.CopyFromRecordset rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set TargetCell = Nothing
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Sub
InvalidInput:
    MsgBox "The source file or source range is invalid!", vbExclamation, "Get data from closed workbook"
End Sub


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2017-07-14 19:26:30 (GMT)

Hi!

You would normally use the latest version of the library that is installed on your computer.
E.g. Microsoft ActiveX Data Objects 2.8 Library or Microsoft ActiveX Data Objects 6.0 Library.

Guillen | 2017-07-14 19:11:57 (GMT)

Quick doubt,
Which "Microsoft ActiveX Data Objects x.x Object Library" should i be using?
I have the versions {(mulit-dimensional) 2.8, (mulit-dimensional) 6.0, 2.0, 2.1, 2.5, 2.6, 2.7, 2.8, 6.0}
I've tried but i cant get to open the database connection.

my dbConnectionString ="DRIVER={Microsoft Excel Driver (*.xls)};DBQ=D:\XXX\XXXX.xls"

*I'm using excel 2016 and I installed "Microsoft Access Database Engine 2010 Redistributable"