LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
Posts: 1
Default Connection files and pivot tables

Hi All,
Hopefully, this is the correct sub forum for this question, if not I apologise.

I have come across and issue with an excel report I am in the process of creating for a customer using pivot tables to summarise their data for them.

The data is held across multiple sheets in the excel file and I am trying to combine them in 1 pivot table using data connections.

I am using Excel 2010 on Windows 7 32 bit.

To create the initial connection, I went to the Data Menu, then selected From Other Sources in the Connection section, I then selected From Data Connection Wizard.
Next the data source type chosen to connect to is ODBC DSN.
Excel files was chosen as ODBC data Source
I selected the workbook that held the data (in this case the same workbook that will contain the pivot table)
I then selected the first range in the excel file and produced the pivot table.

When viewing the connection properties the connection string read like this
DSN=Excel Files;
DBQ=C:\Range Testing\range test.xlsx;
DefaultDir=C:\Range Testing;

The command text looks like this
SELECT * FROM `rangeSample1`
(rangeSample1 is the first set of data defined as a range)

If I change the command text to read
SELECT * FROM `rangeSample1`
Union all
SELECT * FROM `rangeSample2`
The pivot table will update to reflect both sets of data
(rangeSample2 is the second set of data on a different sheet)

However there is a limit of 65,536 rows in each range of data.
I am hoping someone might know of a way to increase the limit.
I cannot put all the data on one sheet because in total, there is over 2.5 million rows of data.
Also I cannot use Power Pivot because my customer is unable to install it on their systems
I hope this all makes sense
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
problem in attaching files( Pivot Tables ) Rajesh Chandra Excel Discussion (Misc queries) 1 January 19th 08 09:35 PM
Building pivot tables in Excel 2007 based on existing pivot tables? [email protected] Excel Discussion (Misc queries) 4 December 26th 07 09:05 PM
(Tom?) Pivot tables, code to refer to all pivot tables on template klysell Excel Programming 0 July 20th 07 09:32 PM
why do pivot tables create such huge files? jabba406 Excel Discussion (Misc queries) 1 February 14th 06 10:39 AM
Pivot tables-controlling user interaction with pivot tables Sindhura Excel Programming 0 August 27th 03 02:10 PM

All times are GMT +1. The time now is 09:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"