mdb - > xls

[pm]

hello,

does anybody know how can i convert access mdb into excel xls file
(mdb table into xls sheet)

is it possible at all?

i have almost 100 mdbs to convert (i'd like to do it in loop)

best regards

ps. i hope i wrote on proper newsgroup (if not, please write what
group is best to write about this problem)

 

[Tim Williams]

Just one table in each mdb or many tables ?

I would use ADO to query the tables and then the recordset's
copyfromrecordset method to dump tp a worksheet.
Use Dir() or similar method to loop through the databases (if they're all in
a common directory).

Tim

 

[pm]

Just one table in each mdb or many tables ?

many tables, different names and number of tables in each of 80 mdbs
mdb are named 1.mdb, 2.mdb and so on...
(example here: http://miodek.no-ip.org/~zdj/example.mdb)

I would use ADO to query the tables and then the recordset's
copyfromrecordset method to dump tp a worksheet.
Use Dir() or similar method to loop through the databases (if they're all in
a common directory).

i understand, but i do now really know how to organize sucha a conversion..

create one xls file with macro, which imports tables to sheets to
particular xls (1.xls, 2.xls and so on)?

rgs
Peter

 

[Tim Williams]

What's the need for this conversion? That might determine the most suitable
approach...
In general probably creating one workbook for each mdb, with one worksheet
for each table would be reasonable.

How much data (rows)? Will you run up against the Excel row limit (65k)?

In general I don't download "sample" files unless from a site I'm familiar
with, so I didn't look at your example.



Tim

 

[pm]

How much data (rows)? Will you run up against the Excel row limit (65k)?

usually less then200-300 rows..

In general I don't download "sample" files unless from a site I'm familiar
with, so I didn't look at your example.

this site is on my computer

 

[Tim Williams]

You can use ADOX to get a list of the tables in each mdb. Create a new
workbook and for each table a new sheet.
Use ADO to get the records from each table and dump them to the appropriate
sheet.

Next mdb >> new workbook
etc etc


Tim