Exporting to Excel

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

Guest

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
 
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 worksheet 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
GetUserId(rstUsers![UserId])
DoCmd.TransferSpreadsheet 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.
 
I typed in the function exactly as given. In the Sub, I used "userids" in
the Set command, which is the query table containing the field, patientid.
In the GetUserId call in the Sub, I used [patientid], and in the DoCmd
arguments I used blood2Query1 instead of qselUserData to contain all fields
to be exported to Excel.

In the criteria for the blood2Query1 I typed GetUserId to which Access adds
double quotes to.

I get repeated errors that "Action or method requires a Table Name argument.
The line highlighted is that containing the DoCmd. line. Not sure what the
problem is.

Klatuu said:
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 worksheet 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
GetUserId(rstUsers![UserId])
DoCmd.TransferSpreadsheet 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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 worksheet 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.TransferSpreadsheet 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 said:
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
Click to expand...
Click to expand...
 
I did not see any modified code. Did you attach it or send it somehow?

I did eventually try using GetUserId() in the criteria box for the "blood2
Query1" query/table but got the same error that this table is not recognized
as a Table Name in the DoCmd method. This query is in the same db as the
"userids" query which has all the patientid information and "Blood2 Query1"
has all the data I want to export to separate excel sheets for each value of
patientid.

I don't have any forms defined in this db, so am not sure what your
reference to a form is.
Thanks very much for your assistance.

Klatuu said:
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 said:
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 worksheet 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.TransferSpreadsheet 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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.TransferSpreadsheet 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 said:
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 said:
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 worksheet 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.TransferSpreadsheet 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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
I believe this is the line that is causing the extra windows to open:
DoCmd.OpenQuery "blood2 Query1"
Click to expand...

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

Ken C said:
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.TransferSpreadsheet 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 said:
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 said:
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 worksheet 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.TransferSpreadsheet 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.
:

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
Click to expand...
Click to expand...
Click to expand...
 
Yes, eliminating that line does the job. Thanks very much for all your help.
One final question: Is there a way to monitor Loop progress with a screen
message indicating the changing values of GetUserId() without any user
prompts as required by the MsgBox method? Right now, the code works fine and
all (1,004) Excel files are saved properly but it takes a minute or so and
I'd like to display progress to reassure future users that things are
progressing.

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

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

Ken C said:
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.TransferSpreadsheet 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 said:
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.


:

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 worksheet 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.TransferSpreadsheet 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.
:

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
Click to expand...
Click to expand...
Click to expand...
 
You said you don't have a form associated, so I guess you could use the
status bar
I would put the code like this:

Do While Not rstUsers.EOF
GetUserId (rstUsers![patientid])
VarX=Syscmd(acSysCmdSetStatus, GetUserId)

The when the loop is done:

Loop
Set rstUsers = Nothing
VarX = Syscmd(acSysCmdClearStatus)
End Sub


(You will need to dim VarX or whatever you want to call it.
Ken C said:
Yes, eliminating that line does the job. Thanks very much for all your help.
One final question: Is there a way to monitor Loop progress with a screen
message indicating the changing values of GetUserId() without any user
prompts as required by the MsgBox method? Right now, the code works fine and
all (1,004) Excel files are saved properly but it takes a minute or so and
I'd like to display progress to reassure future users that things are
progressing.

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

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

Ken C said:
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.TransferSpreadsheet acExport, , "blood2 Query1", "C:\Documents and
Settings\KEN COGGER\My Documents\Patient Databases\" & GetUserId & ".xls",
True
rstUsers.MoveNext
Loop
Set rstUsers = Nothing
End Sub


:

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.


:

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 worksheet 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.TransferSpreadsheet 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.
:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top