filter and export to excel

[joemeshuggah]

i have a table in access with appx 1 million rows of data. there is an
identification field in that table that has approximately 500 unique values.
what i would like to do is create a macro that would automatically export the
data associated with each of those 500 identifiers into their own tab in
excel where the macro automatically creates a tab for each of the values (so
i can avoid creating 500 separate tabs myself). is this possible?

 

[bhicks11 via AccessMonster.com]

Yes, you can use transferspreadsheet and use a query as your datasource.
Keep in mind that there are worksheet size limits in excel.

Bonnie
http://www.dataplus-svc.com

 

[joemeshuggah]

wouldn't i be required to create 500 seperate queries and 500 seperate
transfer spreadsheet actions? if not, how do i designate which field should
be used to generate a new tab?

 

[bhicks11 via AccessMonster.com]

Yes, sorry Joe maybe I don't have your best solution. I think something can
be done with VBA looping but would take some time.

Maybe somebody else can help.

Bonnie
http://www.dataplus-svc.com

wouldn't i be required to create 500 seperate queries and 500 seperate
transfer spreadsheet actions? if not, how do i designate which field should
be used to generate a new tab?

Yes, you can use transferspreadsheet and use a query as your datasource.
Keep in mind that there are worksheet size limits in excel.

[quoted text clipped - 8 lines]

 

[tina]

you could do it in VBA, building a SQL statement and then using looping code
to export the dataset and then revise the statement to change the criteria
each time. how you would export as new tabs to the same workbook, i don't
know, as i've never done it. i have to ask, though, first, will a single
Excel workbook hold 500 sheets? and even if it will, is there a reason you
want to work in Excel rather than manipulating the data in Access?

hth