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 02:37.
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.