once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

It may be time to start learning MS Access:
http://www.mrexcel.com/tip102.shtml

If you don't have access to Access, look at this:
http://social.msdn.microsoft.com/For...b-89be1c2ecc97

Or, this:
http://www.pcreview.co.uk/forums/thread-3548035.php

Or, this:
http://accountant.intuit.com/practic...thquic kbooks



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Epidemic" wrote:

once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

I have a tool that is used but several cellular markets it involves thousands
of lines of code, and does more than just create pivots. it is not as
easy as start over again. hell I am not sure I could ever create something
as good as this tool again period. I simply need to know if there is a way
to improve the source data portion of the macro to include more than 65,536
records with as little modification as possible. my current range command
uses Cells (variable1, variable2) somehow the way I am using the range
command has a problem with more than 65536 records.

"ryguy7272" wrote:

It may be time to start learning MS Access:
http://www.mrexcel.com/tip102.shtml

If you don't have access to Access, look at this:
http://social.msdn.microsoft.com/For...b-89be1c2ecc97

Or, this:
http://www.pcreview.co.uk/forums/thread-3548035.php

Or, this:
http://accountant.intuit.com/practic...thquic kbooks



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Epidemic" wrote:

once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

From a very old post from Jim Thomlinson (when it comes to Excel, he's one of
the 'Great Ones').

Everything depends on how your database is set up. When you select
Data Import External Data New Database Query
You should see Oracle listed as one of the database options on the Databases
tab. At this point you are accessing the ODBC connection to the oracle
Databases. When you do this you will probably be presented with a list of
DSN's (data server names). Now you need to know on where the tables you want
to access live. Once you select that server you will be shown a list of the
tables on that server. Select the appropriate table and MS Query will open
up. You can now add other tables and criteria and such to generate the data
set you want. The data set can be returned directly to XL or to a pivot
table. If you send the data set to a pivot table you are not limited to
65,536 records.

....

Pivot tables are not constrained to 65,536. I have done them up to 650,000
records so I don't think that is your issue. Are you using MS Query to return
the results into a sheet which you intend to then pivot off of, or are you
selecting get external data when you are configuring your pivot table. The
first method will cause problems as the sheet is tied to that 65,536 limit.
The only limit that I know of for pivot tables is that it does not like any
one dimension to be too flat. By that I mean If you have too many unique
items such as part numbers or such then the pivot will not be able to deal
with that. That limit is somewhere around 8,000 unique items.

....

When it says items, that leads me to believe that one of your dimensions is
too flat. A dimension contains members. A member is a unique "bucket" within
the dimension that aggregates all instances of that member. For example how
many unique part numbers or dates or ???'s do you have? The pivot is a way of
aggregating a large amount of data into a small number of members "unique
buckets". I think you might be asking for too many buckets... If that is the
case then you are hooped... You need to figure a way of decreasing the number
of members.

Also:
XL2000: Limits of PivotTables in Excel
http://support.microsoft.com/default.aspx?id=211517

Description of the limits of PivotTable reports in Excel
http://support.microsoft.com/default.aspx?id=820742


So...give that a go. I bet you get it working soon!!!



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Epidemic" wrote:

I have a tool that is used but several cellular markets it involves thousands
of lines of code, and does more than just create pivots. it is not as
easy as start over again. hell I am not sure I could ever create something
as good as this tool again period. I simply need to know if there is a way
to improve the source data portion of the macro to include more than 65,536
records with as little modification as possible. my current range command
uses Cells (variable1, variable2) somehow the way I am using the range
command has a problem with more than 65536 records.

"ryguy7272" wrote:

It may be time to start learning MS Access:
http://www.mrexcel.com/tip102.shtml

If you don't have access to Access, look at this:
http://social.msdn.microsoft.com/For...b-89be1c2ecc97

Or, this:
http://www.pcreview.co.uk/forums/thread-3548035.php

Or, this:
http://accountant.intuit.com/practic...thquic kbooks



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Epidemic" wrote:

once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

My database is simply a spreadsheet xlsm 43 columns wide by 77,000 records
long. When I crossed out of the 65536 boundary it blew up. I attached the
code and where I think there is a fault. is there any reason that either
the range or cells opjects should be giving me difficulty as I have used them?




"ryguy7272" wrote:

From a very old post from Jim Thomlinson (when it comes to Excel, he's one of
the 'Great Ones').

Everything depends on how your database is set up. When you select
Data Import External Data New Database Query
You should see Oracle listed as one of the database options on the Databases
tab. At this point you are accessing the ODBC connection to the oracle
Databases. When you do this you will probably be presented with a list of
DSN's (data server names). Now you need to know on where the tables you want
to access live. Once you select that server you will be shown a list of the
tables on that server. Select the appropriate table and MS Query will open
up. You can now add other tables and criteria and such to generate the data
set you want. The data set can be returned directly to XL or to a pivot
table. If you send the data set to a pivot table you are not limited to
65,536 records.

...

Pivot tables are not constrained to 65,536. I have done them up to 650,000
records so I don't think that is your issue. Are you using MS Query to return
the results into a sheet which you intend to then pivot off of, or are you
selecting get external data when you are configuring your pivot table. The
first method will cause problems as the sheet is tied to that 65,536 limit.
The only limit that I know of for pivot tables is that it does not like any
one dimension to be too flat. By that I mean If you have too many unique
items such as part numbers or such then the pivot will not be able to deal
with that. That limit is somewhere around 8,000 unique items.

...

When it says items, that leads me to believe that one of your dimensions is
too flat. A dimension contains members. A member is a unique "bucket" within
the dimension that aggregates all instances of that member. For example how
many unique part numbers or dates or ???'s do you have? The pivot is a way of
aggregating a large amount of data into a small number of members "unique
buckets". I think you might be asking for too many buckets... If that is the
case then you are hooped... You need to figure a way of decreasing the number
of members.

Also:
XL2000: Limits of PivotTables in Excel
http://support.microsoft.com/default.aspx?id=211517

Description of the limits of PivotTable reports in Excel
http://support.microsoft.com/default.aspx?id=820742


So...give that a go. I bet you get it working soon!!!



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Epidemic" wrote:

I have a tool that is used but several cellular markets it involves thousands
of lines of code, and does more than just create pivots. it is not as
easy as start over again. hell I am not sure I could ever create something
as good as this tool again period. I simply need to know if there is a way
to improve the source data portion of the macro to include more than 65,536
records with as little modification as possible. my current range command
uses Cells (variable1, variable2) somehow the way I am using the range
command has a problem with more than 65536 records.

"ryguy7272" wrote:

It may be time to start learning MS Access:
http://www.mrexcel.com/tip102.shtml

If you don't have access to Access, look at this:
http://social.msdn.microsoft.com/For...b-89be1c2ecc97

Or, this:
http://www.pcreview.co.uk/forums/thread-3548035.php

Or, this:
http://accountant.intuit.com/practic...thquic kbooks



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Epidemic" wrote:

once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

Hi

your problem is that you wrote the code under XL2002.
You have obviously moved to XL2007 (xlsm file and 77,000 rows)

Change
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

to
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
--
Regards
Roger Govier

Epidemic wrote:
once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

Now I have looked at that but I was having problems identifying the
difference between xl12 and xl10. I think you have provided my answer.
I will give it a try. thanks in advance


"Roger Govier" wrote:

Hi

your problem is that you wrote the code under XL2002.
You have obviously moved to XL2007 (xlsm file and 77,000 rows)

Change
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

to
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
--
Regards
Roger Govier

Epidemic wrote:
once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

.

I am trying to work through this. The configuration I had for the pivot
completely changed. I can now exceed 65536 but Excel decided that my
format was not something that was important so it completely revamped things
into an unusable mess:)

in example instead of the data fields being rows them became columns, and
all of my identification information decided to combine into one column


Still working.

"Roger Govier" wrote:

Hi

your problem is that you wrote the code under XL2002.
You have obviously moved to XL2007 (xlsm file and 77,000 rows)

Change
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

to
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
--
Regards
Roger Govier

Epidemic wrote:
once my report exceeded 65536 this command no longer works yielding an error
type missmatch. the command worked fine until the size exceeded 65536 and I
know RWS variable is where I am having problems. But I do not know why or
how to overcome the problem. below you will find the code which is broken.




Workbooks(SSRname).PivotCaches.Create(SourceType:= xlDatabase,
SourceData:=SrcDat.Range(SrcDat.Cells(1, 1), SrcDat.Cells(RWS, CLMN)),
Version:=xlPivotTableVersion10).CreatePivotTable
TableDestination:=Workbooks(SSRname) .Worksheets(DTPivot).Cells(1, 1),
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10





I have broken out the specific problem area for you to see.


Specifically the SourceData:=SrcDat.Range(SrcDat.Cells(1, 1),
SrcDat.Cells(RWS, CLMN))

RWS = 75000
CLMN= 43

I have dimensioned RWS as long

.