RE: Exporting to Excel

I believe this is the line that is causing the extra windows to open:
DoCmd.OpenQuery "blood2 Query1"

Even if it is not, it serves no purpose. You do not need to open the query
before exporting it.

"Ken C" wrote:

Klatuu:

The code below my message runs fine.

I made no changes to the Static Function routine.
I did make the Criteria for the patientid field as GetUserId() not GetUserId.
I did call the OpenQuery statement before saving the excel files.
The OpenQuery call in the Loop seems to be critical.

Some extraneous windows open up when going through the loop, but after a
couple of minutes, everything is saved fine. This is for over 1000 excel
files with an average of eight records per file. Any suggestions on shutting
down the extraneous windows during the loop? Not a big problem, as our
nonprofit only goes through this exercize once every few years.

Thanks,
Ken

'Function to store field values in GetUserId
Static Function GetUserId(Optional ByVal varNewUserId As Variant) As String
Dim varUserId As Variant
If Not IsMissing(varNewUserId) Then
varUserId = varNewUserId
End If
GetUserId = varUserId
End Function

Private Sub CreateXL()
Dim rstUsers As Recordset
Set rstUsers = CurrentDb.OpenRecordset("userids")
If rstUsers.RecordCount = 0 Then
MsgBox "No Userids to Process"
Exit Sub
End If
rstUsers.MoveLast
rstUsers.MoveFirst
Do While Not rstUsers.EOF
GetUserId (rstUsers![patientid])
'Open the specific query with the data to be exported
'Under Criteria, GetUserId() in the patientid field should have been
previously entered.
DoCmd.OpenQuery "blood2 Query1"
DoCmd.TransferSpread*** acExport, , "blood2 Query1", "C:\Documents and
Settings\KEN COGGER\My Documents\Patient Databases\" & GetUserId & ".xls",
True
rstUsers.MoveNext
Loop
Set rstUsers = Nothing
End Sub


"Klatuu" wrote:

Ken, I apologize. I forgot something very important. First, for the query
builder to recognize it is a query, you have to put the () behind the name of
it. Then the next problem is that if you do not pass it a value, it only
executes 1 time, not for every row. So, I thought about how we might fix
this. rather than trying to make the function work when a value is passed
every time, it might be better to create a hidden text box on your form, put
that the value of the userid in it for each loop, then filter the query on
that text box. I have modified the code to do it that way.


"Klatuu" wrote:

Yes, there is a way.
First, you will need a table or a query that will return all the userids.
You can use this to drive a loop that will export a work*** per userid.
You will also need a query with a parameter that will filter your data by
userid. Since you wont be using a form control that has the userid value, I
would suggest a static function the query can use for filtering. Put it in a
standard module so the query can find it.

Here is the static function:
Static Function GetUserId(Optional ByVal varNewUserId As Variant) As String
Dim varUserId As Variant

If Not IsMissing(varNewUserId) Then
varUserId = varNewUserId
End If
GetUserId = varUserId
End Function

Here is the Loop:

Private Sub CreateXL()
Dim rstUsers as Recordset

Set rstUsers = CurrentDb.OpenRecordset("qselUserList")
If rstUsers.RecordCount = 0 Then
MsgBox "No Users to Process"
Exit Sub
End If

rstUsers.MoveLast
rstUsers.MoveFirst
Do While Not rstUsers.EOF
Me.txtUser = rstUsers![UserId]
DoCmd.TransferSpread*** acExport, , qselUserData, _
"C:\SomeDir\Data For " & GetUserId & ".xls", True
rstUsers.MoveNext
Loop
Set rstUsers = Nothing

End Sub

Now, in the query that returns the data, put the name of the function in the
Criteria row for the UserId column.
"Ken C" wrote:

In a database with 8000 records, there are 1000 unique values for one field,
userid. I want to export, for each userid, a separate table with all field
values but don't want to do this manually. Is there a way of doing this?
Ken
.