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

    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

    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
    UserID Data1 Data2 Data3
    first 1 1 1
    first 2 3 1
    first 2 1 2

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

    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
    New Zealand
    Provided Answers: 5
    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.

    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


    DoCmd.SetWarnings True 'trun then back on

    End Sub

    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

    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

Posting Permissions

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