How to I export to multiple Excel Sheets in Access 2003?

[The Mecca]

In Access 2002, I used to be able to export to multiple sheets in Excel using
the TransferSpreadsheet Action in a macro. It does not let me in 2003- does
anyone know how I can do this? Maybe I'll have to use VBA code? I am not very
familiar with VBA. Help please!

 

[Ken Snell [MVP]]

2003 works the same way as 2002 for this feature. Describe what is happening
and what you're doing.

 

[The Mecca]

Sorry, I kept searching for my post, but it was not coming up on any of my
searches, so I reposted....

well poo, I have been working on this since Friday and I swear it wasn't
working. But today it is working. Thanks anyway.

But for anyone else: just put the name of the sheet you want export to in
the range field.

 

[Ken Snell [MVP]]

Sorry, I kept searching for my post, but it was not coming up on any of my
searches, so I reposted....

well poo, I have been working on this since Friday and I swear it wasn't
working. But today it is working. Thanks anyway.

But for anyone else: just put the name of the sheet you want export to in
the range field.

Note that your solution is an undocumented feature of the export option for
TransferSpreadsheet.... that means that it may not work in future versions
of ACCESS. The best way to ensure a query is exported to its own sheet is to
use a different name for the query that is being exported...the query name
will dictate the worksheet onto which the data are exported.

 

[Macro Newbie]

I would like to also export a databse to multiple worksheets in a single
workbook but have not been able to accomplish it using the method in this
post.

My situation is that the Access table I have has 100000+ records and I would
like to export to excel having the rows overflow to a new worksheet as each
worksheet is filled to its row limit. I am a novice macro user so as much
detail as possible would be appreciated.

Thanks in advance....

 

[Joseph R. Pottschmidt]

Dear Macro Newbie:

What you are proposing isn't something that excel is good at. When you
export rows of data, you have that limit of 65535 rows with excel 2003
and 2000 and with office 97 and earlier it is 16384 rows of data per
sheet. I'm not sure why you need to export all this data?

If you want to export all the data, you are going to have to automate
the process by going column by column and row by row until you've filled
up the sheet and then select another sheet.

It would be a great deal easier if you created a query that output the
result that you're trying to get from excel and then export that to
excel and graph and do whatever you wish from there.

Joe P.

-----Original Message-----
From: Macro Newbie [mailto:Macro (e-mail address removed)]
Posted At: Monday, June 12, 2006 9:18 AM
Posted To: microsoft.public.access.macros
Conversation: How to I export to multiple Excel Sheets in Access 2003?
Subject: Re: How to I export to multiple Excel Sheets in Access 2003?

I would like to also export a databse to multiple worksheets in a single

workbook but have not been able to accomplish it using the method in
this
post.

My situation is that the Access table I have has 100000+ records and I
would
like to export to excel having the rows overflow to a new worksheet as
each
worksheet is filled to its row limit. I am a novice macro user so as
much
detail as possible would be appreciated.

Thanks in advance....