Writing records from an Access table to multiple spreadheets

[GEawf01]

Ok, this is beyond my level so hopefully someone can help.

I need to export spreadsheets from Access based on a series of queries. The data looks like this in access:

Table in Access
UserID Data1 Data2 Data3
first 1 1 1
first 2 3 1
first 2 1 2
second 1 1 1
second 3 4 2
third 2 2 2
etc. … … …

And I need a process to write-out 3 result sets to different spreadsheets named for the values in the UserID field. So something like this is the result:

Producing 3 excel spreadsheets
first.xls
UserID Data1 Data2 Data3
first 1 1 1
first 2 3 1
first 2 1 2

second.xls
UserID Data1 Data2 Data3
second 1 1 1
second 3 4 2

third.xls
UserID Data1 Data2 Data3
third 2 2 2


Except I have about 750 UserIDs which change frequently (updates are already dealt with in the Access database, however there will be between 200-250 data points for each) and need to do this weekly.

Can anyone help me with how I can get this done??

Thanks VERY MUCH!!

 

[ejstefl]

Just some ideas:

First, I would create a recordset with all of your user ids:

set rs = currentdb.openrecordset("SELECT Distinct UserID FROM tblTable",dbOpenDynaset)

Then, I would loop through that recordset, creating a new recordsets for each userID:

Do while not rs.EOF
set rsResults = currentdb.openrecordset("SELECT Data1, Data2, Data3 FROM tblTable WHERE UserID = " & rs!UserID, dbopendynaset)
'Insert code to export the recordset to Excel here, you can search the forum or google
rsResults.close
set rsResults = nothing
rs.movenext
loop