Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula reference~muliple sheets | Excel Discussion (Misc queries) | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
Muliple formulas | Excel Worksheet Functions | |||
Sum If on muliple conditions | Excel Worksheet Functions | |||
Create New Workbook - Name book - 4 Sheets - Name Sheets | Excel Programming |