How to increase the maximum rows in the worksheet?

[ken2005]

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

 

[Guest]

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

 

[Debra Dalgleish]

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]

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 |
+-------------------------------------------------------------------+

 

[Debra Dalgleish]

I meant that you could create the pivot table in Excel, directly from
the data in Access --

In Excel, choose Data>PivotTable 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