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

[Thore]

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

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

[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?

[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


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