Import data from a closed workbook (ADO)

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):
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

Another method that doesn't use the CopyFromRecordSet-method

With the macro below you can perform the import and have better control over the results returned from the RecordSet.

Sub TestReadDataFromWorkbook()
' fills data from a closed workbook in at the active cell
Dim tArray As Variant, r As Long, c As Long
    tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
    ' without using the transpose function
    For r = LBound(tArray, 2) To UBound(tArray, 2)
        For c = LBound(tArray, 1) To UBound(tArray, 1)
            ActiveCell.Offset(r, c).Formula = tArray(c, r)
        Next c
    Next r
    ' using the transpose function (has limitations)
'    tArray = Application.WorksheetFunction.Transpose(tArray)
'    For r = LBound(tArray, 1) To UBound(tArray, 1)
'        For c = LBound(tArray, 2) To UBound(tArray, 2)
'            ActiveCell.Offset(r - 1, c - 1).Formula = tArray(r, c)
'        Next c
'    Next r
End Sub

Private Function ReadDataFromWorkbook(SourceFile As String, SourceRange As String) As Variant
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
'   this function can only return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
'   this function can return data from any worksheet in SourceFile
' SourceRange must include the range headers
' examples:
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName")
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
    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 & "]")
    On Error GoTo 0
    ReadDataFromWorkbook = rs.GetRows ' returns a two dim array with all records in rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Function
InvalidInput:
    MsgBox "The source file or source range is invalid!", vbExclamation, "Get data from closed workbook"
    Set rs = Nothing
    Set dbConnection = Nothing
End Function

You can also use the procedure RS2WS to transfer the data from the recordset to a worksheet.

The macro example assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.

Document last updated 2000-09-16 12:46:39 Printer friendly version

User Contributed Comments (in descending order):

Ole P. from Norway wrote (2006-09-05 23:09:27 CET):

Re: What if I need data from sheet 2, and there are no named ranges?
You will have to cope without using ADO, e.g. like this.

Rob from Columbus, OH wrote (2006-09-05 18:55:15 CET):

What if I need data from sheet 2, and there are no named ranges?
I have about 100 workbooks that all have 2 worksheets. Unfortunately, all the data I need is on Sheet 2. Also, there are no named ranges in the workbooks. Is there a way to get at the data without having to change the workbooks?

Ole P. from Norway wrote (2006-07-24 14:16:09 CET):

Re:Importing different cells from different worksheets
See the previous answer.
Just use Edit, Paste Special... and paste values.

AJ from Canada wrote (2006-07-21 22:23:50 CET):

Re:Importing different cells from different worksheets
Thanks for the info. Appreciate the help!
Another quick question:
I'm now using a macro to copy specific data from my current excel file to the other excel file (with screen updating off). I also want to get results of the calculations performed in the other file (with the new updated data) into my current file. In your opinion, what is the best way to do this? (the Data is scattered over several sheets, cells in the other file)

Ole P. from Norway wrote (2006-07-20 23:42:44 CET):

Re:Importing different cells from different worksheets
ADO is suited to retrieve data from workbooks with layouts that resembles a database table.
If you are going to retrieve single values from multiple workbooks I recommend that you:
Start your macro recorder.
Open one of the workbooks you want to retrieve data from.
Copy the cell or cell range you want to retrive.
Return to the target workbook and paste the copied data.
Close the source workbook.
Stop your macro recorder.

You now have the base code necessary to retrive data from any workbook, you will probably need to tweek it a little to make it work for all the data you want to copy and paste.
If you add Application.Screenupdating = False to the beginning of your code, you will avoid the screen updating until the macro finishes.

AJ from Ca wrote (2006-07-20 20:44:50 CET):

Importing different cells from different worksheets
I actually want to import cells from several worksheets in one workbook to specific cells in one worksheet. I am using the 1st procedure mentioned on this page over and over for each cell I need to copy. Can you suggest a more efficient way of doing this?
I am new to VB, really appreciate the help.

Ole P. from Norway wrote (2006-07-20 16:53:13 CET):

Re: Help with configuring "MyDataRange"
You define a named data range (in the source workbook) using the menu Insert, Name, Define.
Specify any valid custom range name and select the range it is supposed to refer to (minimum 1 column and 2 rows).

AJ from Canada wrote (2006-07-20 16:50:37 CET):

Re: Help with configuring "MyDataRange"
Got it to work!
The learning curve I guess -:)

AJ from Canada wrote (2006-07-20 15:51:14 CET):

Re: Help with configuring "MyDataRange"
Thanks for the prompt response!
Specifically, I'm getting an error when I try to form a Range in SourceFile in the following manner:

Dim MyDataRange As Range
MyDataRange = Workbooks("C:\FolderName\WorkbookName.xls").Worksheets("PC").Range("C9")

I have the function call as described in the 1st paragraph of the text: GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "MyDataRange", Range ("B3"), True

Is this the right way of declaring MyDataRange?

Ole P. from Norway wrote (2006-07-20 15:09:48 CET):

Re: Help with configuring "MyDataRange"
The first paragraph of text in the example above contains information on how you can refer to a particular workbook and range.

<< Previous 1 2 3 4 5 Next >>