Connection files and pivot tables

S

simonh

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

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

The data is held across multiple sheets in the excel file and I a
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, the
selected From Other Sources in the Connection section, I then selecte
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 sam
workbook that will contain the pivot table)
I then selected the first range in the excel file and produced the pivo
table.

When viewing the connection properties the connection string read lik
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 ove
2.5 million rows of data.
Also I cannot use Power Pivot because my customer is unable to instal
it on their systems
I hope this all makes sens
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top