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;
DriverId=1046;
MaxBufferSize=2048;
PageTimeout=5;

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