Developer Forum

>From Excel 97 Programmatically create access 97 or 2000 database and
export data to.

Ref. Access 97 or 2000, Excel 97.


I have an excel workbook "Abc.xls".
"Abc.xls" has a variable number of sheets,
in this example has "Sheet135","Sheet147","Sheet085".

In each sheet there are a variable number of columns,
Each column rappresent a field, the text in the first cell is the name
of the field.


I would like to have a macro that from excel workbook like from
"abc.xls",
creates an access 97 or access 2000 database, with the following
details.


database path = active workbook path
database name = excel workbook name ("abc.xls = abc.mdb")
table name = sheets name (a table for each sheet : "Engine Parts",
"Friends", "Clients")
field name = first cell text of each column


like this ...


from an Excel workbook like this ....

"Abc.xls"

Sheet "Engine Parts"
' A B C
-'---------------------------
1' NAME PART CODE
2' O D F
3' Y T Y
4' E W X
5' Q H J

Sheet "Friends"
' A B
-'---------------------
1' NAME ADDRESS
2' A A
3' R 2
4' R W
5' Q 1

Sheet "Clients"
' A B C
-'---------------------------
1' NAME ADDRESS PHONE
2' T D F
3' U Y 5
4' 8 W T
5' Q H P



I woul get an access database like this


Database = "abc.mdb"
Table 1 = "Engine Parts"
field1 = "Name"
field2 = "Part"
field3 = "Code"

Table 2 = "Friends"
field1 = "Name"
field2 = "Address"

Table 3 = "Clients"
field1 = "Name"
field2 = "Address"
field3 = "Phone"


(containing also values, not only tables and fields)
any Help ?

Thanks.
Robert.