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)
1) The code in the workbook is working in Excel
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.
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