 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
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
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.
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
"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.
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
http://www.erlandsendata.no/english/index.php?t=envbadac