What I would do is
1 st create a Query to list 1 of each UserID i call it USERS
This will each record will then come the file name and the part of the where statement.
Dim RstUser As Recordset
Dim SQL As String
Dim User As String
Dim MYSQL As String
Dim DB As Database
SQL = "SELECT Table1.* INTO Export FROM Table1 WHERE (((Table1.UserID)='Bla'));"
Set DB = CurrentDb
Set RstUser = DB.OpenRecordset("Users")
DoCmd.SetWarnings False ' turn the warning off
Do Until RstUser.EOF
User = RstUser.Fields("User") ' get the data
MYSQL = Replace(SQL, "bla", User) ' replace the bla with data a cheats way of doing it
DoCmd.RunSQL MYSQL 'Make a table call export but only With one userid in it
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel4, "Export", "C:\bla\" & User & ".xls", ture
DoCmd.SetWarnings True 'trun then back on
code not tested just off top of head mite need some more work
should get you started
Last edited by myle; 06-13-06 at 01:37.
hope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON