Export to MS Excel

[GW]

Hi Klatuu,
Have a look at it,

Public Sub exporting()
'FYI, I'm using MS Access 2002
'Before I add DAO library, I get so many errors, but not now.
'Fine after I added DAO 3.6 Lib
'After trying so many changes & I remove all the dim
'Dim db As Database, Dim qdf As QueryDefs, Dim rstbrcodes,
'rstbrdata As Recordset, Set db = CurrentDb
'I managed to compile this module but come to run the module
'I got run time error object not found for this line "
'qdf.Parameters(0) = rstbrdata![app_sys_code]

Set qdf = CurrentDb.QueryDefs("qselapp_sys_code")
'"qselapp_sys_code" is the query name for "select * from prov group by
app_sys_code
Set rstbrcodes = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
'Be sure there are records to process
If rstbrcodes.RecordCount = 0 Then
MsgBox "No Data Found For This Report", vbInformation + vbOKOnly, _
"Data Error"
'Exit Do
'Exit Do give me an error too.
Else
rstbrcodes.MoveLast
rstbrcodes.MoveFirst
End If

Do While Not rstbrcodes.EOF
Set qdf = CurrentDb.QueryDefs("qselapp_sys_data")
'qselapp_sys_data is the query name for "Select * from prov where
app_sys_code="AMC"
'"AMC" is one of the app_sys_code group of data"
qdf.Parameters(0) = rstbrdata![app_sys_code]
Set rstbrdata = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
If rstbrdata.RecordCount > 0 Then
strXLFileName = "MyPathj" & rstbrcodes.[app_sys_code] & ".xls"
DoCmd.TransferSpreadsheet acImport, "ExportSpecName", _
8, "qselBrData", strXLFileName, True
End If
rstbrdata.Close
rstbrcodes.MoveNext
Loop
rstbrcodes.Close
Set rstbrcodes = Nothing
Set rstbrdata = Nothing
Set qdf = Nothing

End Sub

GW,
I really don't know. The code looks ok, but without your system in front of
me to play with it, I can't tell. I know it works for me because what I did
was copy it from one of my modules and changed the names. Are there any
parameters in your query?
Don't dispare, we can work it out, we just have to experiment with it. By
the way, what version of Access are you on. I am on 2000. Try removing the
arguments from the statement.
Set rstBrCodes = qdf.OpenRecordset
Did you put Dim statements in your code
Dim qdf as Querydef
Dim rstBrCodes as Recordset

Let me know how it works out. If I see anything in the meantime, I will get
back to you.

Hi Klatuu,

Why line2 openRecordset return an error method or data member not found.
help me pls.

GW,

First, you question about the number of [br_code]s you need to deal with.
The easiest way to do that is to create a group by select query that returns
only the br_code. This should give you a record set that contains one
occurance of each br_code in your table. We will call that qselBrCodes.
Then you will need a query based on your table with one parameter which is
br_code. It should be layed out the way you want it to be in Excel. We will
call that qselBrData.

Here is the basic looping logic for that:

Set qdf = CurrentDb.QueryDefs("qselBrCodes")
Set rstBrCodes = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
'Be sure there are records to process
If rstBrCodes.Recordcount = 0 Then
MsgBox "No Data Found For This Report", vbInformation + vbOKOnly, _
"Data Error"
Exit Do
Else
rstBrCodes.MoveLast
rstBrCodes.MoveFirst
End If

Do While Not rstBrCodes.EOF
Set qdf = CurrentDb.QueryDefs("qselBrData")
qdf.Parameters(0) = rstBrCodes.[br_code]
Set rstBrData = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
If rstBrData.Recordcount > 0 Then
strXLFileName = "MyPathj" & rstBrCodes.[br_code] & ".xls"
DoCmd.TransferSpreadsheet acImport, 8, _
"qselBrData", strXLFileName, True
End If
rstBrData.Close
rstBrCodes.MoveNext
Loop
rstBrCodes.Close
set rstBrCodes = Nothing
set rstBrData = Nothing
set qdf = Nothing

Please be aware this is untested Air Code.

:

Hi Ken,
I've to export the data one by one based on br_code value to respective .xls.

Query : Select * from CP where [br_code]="2803"
...........Select * from CP where [br_code]="3206"
one by one group

Below are required info..
tq so much.
DataType:
text|text|text|datetime|NumericDouble|text|NumericLonginteger|NumericDouble
FieldName:
name|acct_no|br_code|agrt_date|net_os|lawyer_code|app_age|app_gr_inc
Data:
CLARK|500000192290|2803|18/11/1997 0:00:00|-41.25|PAULC|39|0.00
JONATHAN|500000396191|3206|24/10/1997 0:00:00|0.00|LIMBP|32|0.00
MARTHA|500000709513|3302|2/3/1996 0:00:00|0.00|KADIR|38|0.00
LEX|502000145638|2803|16/5/2000 0:00:00|0.00|GANES|49|4500.00
LIONEL|504000186532|3301|7/3/2001 0:00:00|0.00|YAACO|34|1500.00
LANA|504000283954|3301|20/4/2001 0:00:00|0.00||31|2000.00
CHLOE|504001629429|3208|6/8/2003 0:00:00|0.00|||
LOIS|504001675332|3201|2/9/2003 0:00:00|0.00|HARIS|51|0.00
JASON|504001787879|2906|31/10/2003 0:00:00|21.50||56|26000.00
KALEL|502000150669|2806|20/5/2000 0:00:00|32.42|ROSNA|6|0.00
JOREL|504000379688|3206|8/6/2001 0:00:00|62.30|ARMAN|41|2617.50
KENT|504000704187|2905|27/12/2001 0:00:00|132.95|HARCH|15|0.00
GW|504000704217|2905|27/12/2001 0:00:00|132.95||15|0.00
KEN|505000000783|2905|22/12/2001 0:00:00|158.90|SOBRI|15|0.00
MARK|502000413887|3401|1/11/2000 0:00:00|222.00|FERN|46|2970.71
MIKE|504000978643|3201|13/6/2002 0:00:00|241.57||42|2717.00



:

Tell us more about the query and data structures so that we can suggest
meaningful examples. Also show us an example of the data records.
--

Ken Snell
<MS ACCESS MVP>



Hi experts,

Actually, appreciate if you could show me how write the code for the
problem
(the looping proces). I can create a query or a macro but what if there
are
thousand of
group of data in that particular fields and the data is not consistent eg
100...120,130...150,160...250. I want to export the data to excel based on
all group of data in that particular field at one time eg.
for 100 to 100.xls....250 to 250.xls.

pls guide me..

tq.



:

Ken,

Pardon my waxing nostalgic. Sort of sounds like "In my day we didn't
have
school buses, we had to walk to school in the snow and it was up hill
both
ways."

A quick Google search of the newsgroups will find many, 'nostalgic' posts
from people's experiences! I will spare everyone mine.... < g >

 

[GW]

Error for this line is object not found as per my reply earlier
qdf.Parameters(0) = rstbrdata![app_sys_code]
but actually it was object required

GW,
I really don't know. The code looks ok, but without your system in front of
me to play with it, I can't tell. I know it works for me because what I did
was copy it from one of my modules and changed the names. Are there any
parameters in your query?
Don't dispare, we can work it out, we just have to experiment with it. By
the way, what version of Access are you on. I am on 2000. Try removing the
arguments from the statement.
Set rstBrCodes = qdf.OpenRecordset
Did you put Dim statements in your code
Dim qdf as Querydef
Dim rstBrCodes as Recordset

Let me know how it works out. If I see anything in the meantime, I will get
back to you.

Hi Klatuu,

Why line2 openRecordset return an error method or data member not found.
help me pls.

GW,

First, you question about the number of [br_code]s you need to deal with.
The easiest way to do that is to create a group by select query that returns
only the br_code. This should give you a record set that contains one
occurance of each br_code in your table. We will call that qselBrCodes.
Then you will need a query based on your table with one parameter which is
br_code. It should be layed out the way you want it to be in Excel. We will
call that qselBrData.

Here is the basic looping logic for that:

Set qdf = CurrentDb.QueryDefs("qselBrCodes")
Set rstBrCodes = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
'Be sure there are records to process
If rstBrCodes.Recordcount = 0 Then
MsgBox "No Data Found For This Report", vbInformation + vbOKOnly, _
"Data Error"
Exit Do
Else
rstBrCodes.MoveLast
rstBrCodes.MoveFirst
End If

Do While Not rstBrCodes.EOF
Set qdf = CurrentDb.QueryDefs("qselBrData")
qdf.Parameters(0) = rstBrCodes.[br_code]
Set rstBrData = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
If rstBrData.Recordcount > 0 Then
strXLFileName = "MyPathj" & rstBrCodes.[br_code] & ".xls"
DoCmd.TransferSpreadsheet acImport, 8, _
"qselBrData", strXLFileName, True
End If
rstBrData.Close
rstBrCodes.MoveNext
Loop
rstBrCodes.Close
set rstBrCodes = Nothing
set rstBrData = Nothing
set qdf = Nothing

Please be aware this is untested Air Code.

:

Hi Ken,
I've to export the data one by one based on br_code value to respective .xls.

Query : Select * from CP where [br_code]="2803"
...........Select * from CP where [br_code]="3206"
one by one group

Below are required info..
tq so much.
DataType:
text|text|text|datetime|NumericDouble|text|NumericLonginteger|NumericDouble
FieldName:
name|acct_no|br_code|agrt_date|net_os|lawyer_code|app_age|app_gr_inc
Data:
CLARK|500000192290|2803|18/11/1997 0:00:00|-41.25|PAULC|39|0.00
JONATHAN|500000396191|3206|24/10/1997 0:00:00|0.00|LIMBP|32|0.00
MARTHA|500000709513|3302|2/3/1996 0:00:00|0.00|KADIR|38|0.00
LEX|502000145638|2803|16/5/2000 0:00:00|0.00|GANES|49|4500.00
LIONEL|504000186532|3301|7/3/2001 0:00:00|0.00|YAACO|34|1500.00
LANA|504000283954|3301|20/4/2001 0:00:00|0.00||31|2000.00
CHLOE|504001629429|3208|6/8/2003 0:00:00|0.00|||
LOIS|504001675332|3201|2/9/2003 0:00:00|0.00|HARIS|51|0.00
JASON|504001787879|2906|31/10/2003 0:00:00|21.50||56|26000.00
KALEL|502000150669|2806|20/5/2000 0:00:00|32.42|ROSNA|6|0.00
JOREL|504000379688|3206|8/6/2001 0:00:00|62.30|ARMAN|41|2617.50
KENT|504000704187|2905|27/12/2001 0:00:00|132.95|HARCH|15|0.00
GW|504000704217|2905|27/12/2001 0:00:00|132.95||15|0.00
KEN|505000000783|2905|22/12/2001 0:00:00|158.90|SOBRI|15|0.00
MARK|502000413887|3401|1/11/2000 0:00:00|222.00|FERN|46|2970.71
MIKE|504000978643|3201|13/6/2002 0:00:00|241.57||42|2717.00



:

Tell us more about the query and data structures so that we can suggest
meaningful examples. Also show us an example of the data records.
--

Ken Snell
<MS ACCESS MVP>



Hi experts,

Actually, appreciate if you could show me how write the code for the
problem
(the looping proces). I can create a query or a macro but what if there
are
thousand of
group of data in that particular fields and the data is not consistent eg
100...120,130...150,160...250. I want to export the data to excel based on
all group of data in that particular field at one time eg.
for 100 to 100.xls....250 to 250.xls.

pls guide me..

tq.



:

Ken,

Pardon my waxing nostalgic. Sort of sounds like "In my day we didn't
have
school buses, we had to walk to school in the snow and it was up hill
both
ways."

A quick Google search of the newsgroups will find many, 'nostalgic' posts
from people's experiences! I will spare everyone mine.... < g >