Export to MS Excel

[GW]

HI experts,

Appreciate if someone can help me on this. Is there any fastest way to export
a table to multiple excel files. I got one field name codes eg 100....250.
Can someone show me how to export data with code 100 to 100.xls.....code 250
to
250.xls. I dont know how to this. Please guide or possibly give me a sample to
do this.

tq.

 

[Klatuu]

You can use the Transferspreadsheet method. Create a select query that will
filter on your code. Then in the Transferspreadsheet, identify the query as
the tablename and and use the code you are filtering on as the filename. You
may want to consider putting them in a macro so you can do them all at one
time. And, with any luck, one of the "experts" may respond to your question

 

[Ken Snell [MVP]]

Klatuu's concept is the correct way to go. This can best and most
effectively be done via VBA code. It's a pretty standard thing to do. Post
back if you need specific details.

 

[Klatuu]

Ken,
I would use code, also, but I expect GW is not a VBA programmer. A good way
to move from the evil (IMHO) Macro is to create the macro, then convert it to
code. That way you get an understanding of what the code is doing.

 

[Ken Snell [MVP]]

Hmm... sometimes yes, sometimes no. Often, the wizard that creates the code
from a macro generates very old code, which can be confusing when you try to
compare it to the "more modern" code. And that generated code can be a bit
klunky. From my own personal experience, I found that the best way to learn
ACCESS VBA code was to plunge right in and not convert macros to code.

--

Ken Snell
<MS ACCESS MVP>

 

[Klatuu]

Ken,
You have a valid point. Most code generators write klunky code. The only
one I rememeber that did a pretty good job was the one that was included with
Foxpro 2.?. In the good ole' DOS days, it came with a screen builder that
the code generator used to write the code for you. It was not bad. The
interesting part was that the code generator was written in Foxpro, so you
could modify the code generator to your own liking.

The value with the conversion of Macro to VBA (klunky code aside) is that
for a complete novice who has no concept of how coding works, it is, at least
as starting point.

Life is easier now. My first programming job, with only experience as an
operator (tape ape) I was given a COBOL reference manual, a large stack of
punch cards and instructions on what my first program was required to do.
When I finally got it working, and watched it turn the tape drives to do a
history update, I was totally hooked.

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."

 

[Ken Snell [MVP]]

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]

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 Snell [MVP]]

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>

 

[GW]

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

 

[Klatuu]

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.

 

[GW]

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 >

 

[Klatuu]

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 >

 

[Ken Snell [MVP]]

Perhaps his database has no reference set to DAO library?

 

[Klatuu]

Possible, but wouldn't this happen for other DAO objects already? I'm
wondering if perhaps he has an naming error he hasn't discovered yet.

Do you see any problems with the code I posted for him?

 

[Ken Snell [MVP]]

I don't see any compiling problem with the first few lines (on which he says
he's erroring), but I do note that this step
qdf.Parameters(0) = rstBrCodes.[br_code]

is better written as
qdf.Parameters(0) = rstBrCodes![br_code]

with similar changes throughout the code.

Also, this step
DoCmd.TransferSpreadsheet acImport, 8, _
"qselBrData", strXLFileName, True

is missing the "export specification" argument:
DoCmd.TransferSpreadsheet acImport, "ExportSpecName", _
8, "qselBrData", strXLFileName, True

 

[Klatuu]

Ken,
You are correct on your first part, but in the TransferSpreadsheet, unless
there is an undocumented feature, or you are using something newer that 2000
and I am not familiar with anything newer, TransferSpreadsheet has no
specification name. Are you perhaps thinking of TransferText? In either
case, this is an import, not an export.

I don't see any compiling problem with the first few lines (on which he says
he's erroring), but I do note that this step
qdf.Parameters(0) = rstBrCodes.[br_code]

is better written as
qdf.Parameters(0) = rstBrCodes![br_code]

with similar changes throughout the code.

Also, this step
DoCmd.TransferSpreadsheet acImport, 8, _
"qselBrData", strXLFileName, True

is missing the "export specification" argument:
DoCmd.TransferSpreadsheet acImport, "ExportSpecName", _
8, "qselBrData", strXLFileName, True

--

Ken Snell
<MS ACCESS MVP>

Possible, but wouldn't this happen for other DAO objects already? I'm
wondering if perhaps he has an naming error he hasn't discovered yet.

Do you see any problems with the code I posted for him?

 

[Ken Snell [MVP]]

Ken,
You are correct on your first part, but in the TransferSpreadsheet, unless
there is an undocumented feature, or you are using something newer that
2000
and I am not familiar with anything newer, TransferSpreadsheet has no
specification name. Are you perhaps thinking of TransferText? In either
case, this is an import, not an export.

My apology... that's what I get for multitasking today....< g >

TransferSpreadsheet doesn't have a specification argument. My *big* error!
Thanks.

 

[Klatuu]

Not a *big* error. We all do it.
I see your posts out here frequently and you always have good advice.

 

[Ken Snell [MVP]]

Thank you