Writing record from Access to multiple excel spreadheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!
 
This will take a little VBA code, a couple of queries. First, what you need
to use to export the data to Excel is the TransferSpreadsheet method. Since
you have an unknown number of UserIDs, you will need a query that extracts a
list of all the UserIDs in your table. You will use that to feed the other
query which will extract the data you want to import. It should look
something like this:
SELECT DISTINCT MyTable.UserID FROM MyTable;

Now, you need another query with a filter on UserID that includes the data
you want to export. Put a hidden text box on your form to pass the UserID
value to the query. For example purposes, we will call the query qselUserData
and the text box txtUser

Now you need some code something like this:

Dim rst As Recordset
Dim strFilePath As String

' This would be the path to the folder, but not the file name
strFilePath = "F:\SomeDirectory\"

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT UserID FROM MyTable;")
If rst.RecordCount = 0 Then
MsgBox "No Records to Process"
Exit Sub
End If
rst.MoveLast
rst.MoveFirst

Do While Not rst.EOF
Me.txtUser = rst!UserID
DoCmd.TransferSpreadsheet acExport, ,qselUserData, _
strFilePath & "Data For " & txtUser & ".xls", True
Loop

rst.Close
set rst = Nothing
 
Back
Top