Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Copy a range from closed workbooks (ADO)


The basic examples in the workbook that you can download use ADO to copy data from a closed workbook or workbooks without opening the workbook or workbooks. This can be very fast to merge data from many workbooks but when you open workbooks with code you have much more control and more options.

See the pages below if you want to open the files with code and merge the data

Merge data from all workbooks in a folder

RDBMerge Add-in (very easy)


Important info about the ADO examples

1) The code in the workbook is working in Excel 2000-2013.

2) In a Database you cannot mix data types, a column must be all numbers or all text. If there
are different data types in the column ADO will copy only the Data type that have the majority. Lewis Metzger let me know that you can add IMEX=1 to the Extended Properties in the GetData function as a workeround like this "Extended Properties=""Excel 8.0;HDR=yes;IMEX=1"";"

3) If you want to copy only one cell from each workbook then use A3:A3 and not A3 in the code.


How do I use ADO

Click here to Download a example workbook with 7 code examples and also a data file named test.xls. With this two workbooks you can test the code (copy both files in the same folder).
Note: Read the info on the worksheet and also the commented lines in the macro's.

All macros in the Ado Tester.xls call a macro named GetData with 6 arguments that do almost all the work.

If we look at the first test macro that copy "A1:C5" from "Sheet1" in the test.xls workbook we see: GetData ThisWorkbook.Path & "\test.xls", "Sheet1", "A1:C5", Sheets("Sheet1").Range("A1"), True, True

1) SourceFile : Path/Name of the source file
    ThisWorkbook.Path & "\test.xls"

2) SourceSheet : Name of the sheet in the SourceFile

3) SourceRange : Range in the SourceSheet

Note: You can also use a named range if you want like "MyRange".
leave the SourceSheet argument empty "" if you want to copy from a workbook level name.

4) TargetRange: Destination Sheet/Range

5) Header: Does the range have a header row?

6) UseHeaderRow :Do you want to copy the header row ?

I hope that it is easy to use the code examples in the Ado Tester.xls file. Let me know if you have suggestions or problems with the code. Remember that I am a simple Excel user and no ADO expert.

Personal I use the code or my add-in that open the files so I have more control.

See also this page from Ole P. Erlandsen's