Create muliple sheets using querytable
 

I'm currently able to export query results to an Excel file via the
querytable method. It is working great... and also very fast, but it
limited to 65xxx records. Is there any way for me to use a querytable
and to programmatically spill over to additional worksheets should I
exceed the Excel 65xxx threshold?

Examples would be appreciated. Thanks.


--
mdengler
------------------------------------------------------------------------
mdengler's Profile: http://www.excelforum.com/member.php...o&userid=32030
View this thread: http://www.excelforum.com/showthread...hreadid=526823

Create muliple sheets using querytable
 

I don't believe you can. Since you posted in Programming, you could use ADO
to get your recordset and then break it yourself:

http://www.erlandsendata.no/english/...php?t=envbadac
http://www.rondebruin.nl/accessexcel.htm

--
Regards,
Tom Ogilvy




"mdengler" wrote:


I'm currently able to export query results to an Excel file via the
querytable method. It is working great... and also very fast, but it
limited to 65xxx records. Is there any way for me to use a querytable
and to programmatically spill over to additional worksheets should I
exceed the Excel 65xxx threshold?

Examples would be appreciated. Thanks.


--
mdengler
------------------------------------------------------------------------
mdengler's Profile: http://www.excelforum.com/member.php...o&userid=32030
View this thread: http://www.excelforum.com/showthread...hreadid=526823

Create muliple sheets using querytable
 

Another approach (if you don't need it all there at once) is to use you
database as the source for a pivot Table and use pagefields so you can look
at subsets of the data. This would be particularly more applicable if you
are summarizing your data anyway and retrieving it is an intermediate step.

Another approach would be to build queries in Access (making an assumption
here) and build the queries to break up your database. then build separate
querytables to the queries.

--
Regards,
Tom Ogilvy


"mdengler" wrote:


I'm currently able to export query results to an Excel file via the
querytable method. It is working great... and also very fast, but it
limited to 65xxx records. Is there any way for me to use a querytable
and to programmatically spill over to additional worksheets should I
exceed the Excel 65xxx threshold?

Examples would be appreciated. Thanks.


--
mdengler
------------------------------------------------------------------------
mdengler's Profile: http://www.excelforum.com/member.php...o&userid=32030
View this thread: http://www.excelforum.com/showthread...hreadid=526823

Create muliple sheets using querytable
 

Just to expand on Tom's first point about the pivot table, the pivot table is
not limited to 65k records (depending on the amount of data you can approach
a million records) and you can use your existing query as the source for your
pivot (The first step in the pivot table wizard is to select the data source.
If you select External Data and then select queries you can find your query
and then you are off to the races.

--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Another approach (if you don't need it all there at once) is to use you
database as the source for a pivot Table and use pagefields so you can look
at subsets of the data. This would be particularly more applicable if you
are summarizing your data anyway and retrieving it is an intermediate step.

Another approach would be to build queries in Access (making an assumption
here) and build the queries to break up your database. then build separate
querytables to the queries.

--
Regards,
Tom Ogilvy


"mdengler" wrote:


I'm currently able to export query results to an Excel file via the
querytable method. It is working great... and also very fast, but it
limited to 65xxx records. Is there any way for me to use a querytable
and to programmatically spill over to additional worksheets should I
exceed the Excel 65xxx threshold?

Examples would be appreciated. Thanks.


--
mdengler
------------------------------------------------------------------------
mdengler's Profile: http://www.excelforum.com/member.php...o&userid=32030
View this thread: http://www.excelforum.com/showthread...hreadid=526823

Create muliple sheets using querytable
 

Tom,

Thanks a bunch for the quick response! I appreciate it. I've changed
modified my export code to use "CopyFromRecordset", but unfortunately
when using this method (as well as using a multi-dimensional array), I
run into a situation whereby I cannot import any record greater than
911 characters into an excel cell. Anything less than this amount of
characters works fine.

Ideas?


--
mdengler
------------------------------------------------------------------------
mdengler's Profile: http://www.excelforum.com/member.php...o&userid=32030
View this thread: http://www.excelforum.com/showthread...hreadid=526823

Create muliple sheets using querytable
 

Tom,

Thanks a bunch for the quick response! I appreciate it. I've changed
modified my export code to use "CopyFromRecordset", but unfortunately
when using this method (as well as using a multi-dimensional array), I
run into a situation whereby I cannot import any record greater than
911 characters into an excel cell. Anything less than this amount of
characters works fine.

Ideas?


--
mdengler
------------------------------------------------------------------------
mdengler's Profile: http://www.excelforum.com/member.php...o&userid=32030
View this thread: http://www.excelforum.com/showthread...hreadid=526823