Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to increase the maximum rows in the worksheet?
Hi, I am using Excel97 and I try to create a pivot table getting external data form Ms Access. In Access, my database has over 120,000 records equivalent to 120,000 rows in Excel. I noticed that Excel has only 65536 rows in each worksheet. How can I increase this maximum rows so that could I paste the 120,000 records onto the worksheet? Thanks :) -- ken2005 ------------------------------------------------------------------------ ken2005's Profile: http://www.excelforum.com/member.php...o&userid=24734 View this thread: http://www.excelforum.com/showthread...hreadid=383042 |
#2
|
|||
|
|||
as far as I know it is not possible at all. 65536 is the absolute limit for
Excel. So you probably will have to split it up into different worksheets. It might be an idea to do a little preparation work in Access (e.g. some groupings through queries) in order to reduce the number of lines. Thore "ken2005" wrote: Hi, I am using Excel97 and I try to create a pivot table getting external data form Ms Access. In Access, my database has over 120,000 records equivalent to 120,000 rows in Excel. I noticed that Excel has only 65536 rows in each worksheet. How can I increase this maximum rows so that could I paste the 120,000 records onto the worksheet? Thanks :) -- ken2005 ------------------------------------------------------------------------ ken2005's Profile: http://www.excelforum.com/member.php...o&userid=24734 View this thread: http://www.excelforum.com/showthread...hreadid=383042 |
#3
|
|||
|
|||
You can create the pivot table from the data in Access, so there's no
need to import the data into Excel. In step 1 of the Pivot Wizard, select 'External Data Source', and select your Access file in step 2. ken2005 wrote: Hi, I am using Excel97 and I try to create a pivot table getting external data form Ms Access. In Access, my database has over 120,000 records equivalent to 120,000 rows in Excel. I noticed that Excel has only 65536 rows in each worksheet. How can I increase this maximum rows so that could I paste the 120,000 records onto the worksheet? Thanks :) -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Thanks fellas for the help. Just want to confirm that. I guess I have no other choice but to split the records into two worksheet. Debra, I tried using the Pivot wizard in Access to do the job but there is an error! Anyway, I need some mathematic functions in Excel to get the job down thats why I choose to bring over the data from Access. I try to retrieve data in Excel with:- Data - Get External Data - Create New Query. However Excel doesn't split the records into two worksheet when it is overloaded in the first worksheet. It prompts a window that I have save it in the pics as attached. Is there any other way to go about splitting the 120,000 records into two worksheet? +-------------------------------------------------------------------+ |Filename: external data.zip | |Download: http://www.excelforum.com/attachment.php?postid=3550 | +-------------------------------------------------------------------+ -- ken2005 ------------------------------------------------------------------------ ken2005's Profile: http://www.excelforum.com/member.php...o&userid=24734 View this thread: http://www.excelforum.com/showthread...hreadid=383042 |
#5
|
|||
|
|||
I meant that you could create the pivot table in Excel, directly from
the data in Access -- In Excel, choose DataPivotTable and PivotChart Report In step 1 of the Pivot Wizard, select 'External Data Source' In step 2, click Get Data, and select your Access file Then, complete the steps of the Wizard to build the pivot table ken2005 wrote: Thanks fellas for the help. Just want to confirm that. I guess I have no other choice but to split the records into two worksheet. Debra, I tried using the Pivot wizard in Access to do the job but there is an error! Anyway, I need some mathematic functions in Excel to get the job down thats why I choose to bring over the data from Access. I try to retrieve data in Excel with:- Data - Get External Data - Create New Query. However Excel doesn't split the records into two worksheet when it is overloaded in the first worksheet. It prompts a window that I have save it in the pics as attached. Is there any other way to go about splitting the 120,000 records into two worksheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
maximum number of rows that can be coppied to clipboard | New Users to Excel | |||
Increase rows in worksheet beyond 65,536 row limit | Excel Discussion (Misc queries) | |||
How do I unhide rows 1 through 28 on an Excel worksheet? | Excel Worksheet Functions | |||
Increase default size of worksheet | New Users to Excel | |||
Maximum no of rows in Excel | Excel Worksheet Functions |