Pivot table has source data has exceeded 65536 records type m
in [Programming]
|
From: Epidemic on 16 Apr 2010 15:06 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/Forums/en-US/isvvba/thread/3588960d-9b1c-4e02-ad3b-89be1c2ecc97 > > Or, this: > http://www.pcreview.co.uk/forums/thread-3548035.php > > Or, this: > http://accountant.intuit.com/practice_resources/articles/technology/article.aspx?file=rs_usingexcelpivotchartswithquickbooks > > > > -- > 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: ryguy7272 on 16 Apr 2010 17:16 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/Forums/en-US/isvvba/thread/3588960d-9b1c-4e02-ad3b-89be1c2ecc97 > > > > Or, this: > > http://www.pcreview.co.uk/forums/thread-3548035.php > > > > Or, this: > > http://accountant.intuit.com/practice_resources/articles/technology/article.aspx?file=rs_usingexcelpivotchartswithquickbooks > > > > > > > > -- > > 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: Epidemic on 19 Apr 2010 13:46 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/Forums/en-US/isvvba/thread/3588960d-9b1c-4e02-ad3b-89be1c2ecc97 > > > > > > Or, this: > > > http://www.pcreview.co.uk/forums/thread-3548035.php > > > > > > Or, this: > > > http://accountant.intuit.com/practice_resources/articles/technology/article.aspx?file=rs_usingexcelpivotchartswithquickbooks > > > > > > > > > > > > -- > > > 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: Epidemic on 22 Apr 2010 07:58 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 > > > . >
From: Epidemic on 26 Apr 2010 07:59 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 > > > . >
|
Pages: 1 Prev: Filtering by three criteria including top ten value Next: page number in word 2007 |