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

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

Post by milo2lo » Sat, 25 Dec 2004 07:44:47


>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.