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?

 

[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?

 

[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