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