Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: Writing records from an Access table to multiple spreadheets

    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!!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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.

    [code]
    Sub doit()
    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
    RstUser.MoveNext

    Loop

    DoCmd.SetWarnings True 'trun then back on



    End Sub
    [\code]

    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


    StePhan McKillen
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •