Information

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(1)
http://www.rondebruin.nl/copy3.htm

Merge data from all workbooks in a folder(2)
http://www.rondebruin.nl/fso.htm

RDBMerge Add-in (very easy)
http://www.rondebruin.nl/merge.htm


Important info about the ADO examples

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

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.
 
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 file call a macro named GetData that do almost all the work.

There are six arguments in this macro

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)


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
    "Sheet1"

3) SourceRange : Range in the SourceSheet
    "A1:C5"

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
    Sheets("Sheet1").Range("A1")

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

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


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.



More information

Ole P. Erlandsen's Web Site
http://www.erlandsendata.no/english/index.php?t=envbadac

John Walkenbach
http://www.j-walk.com/ss/excel/tips/tip82.htm

Create a summary worksheet from different workbooks (with formulas)
http://www.rondebruin.nl/summary2.htm

Copy a range from closed workbook (Local, Network and on the internet)
http://www.rondebruin.nl/copy7.htm