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