Are you using the old Excel workbook format? Pre Excel 2007, a worksheet can only contain 65536 rows. For your query, you will get 172800 rows. So this could be causing the problem.
As well as checking whether you are exceeding the row limit like Eugene mentioned, you may be hitting a limit from the PI server on the maximum number of events allowed to be returned from an archive call. This number can be adjusted from the archive tuning parameters tab in PI-SMT using the ArcMaxCollect parameter. This is set to 1,500,000 by default but would be worth checking too.
Hi Eugene and David,
Thanks for your reply .My excel sheet has 172809 rows.
Only 18hrs of data am able to fetch and near to 80,000 rows.
please advise me howo fetch 24hrs of data.
You may be facing the know issue 26747OSI8 - VBA function 'dlresize' gives message box when attempting to resize to greater than 65535 rows.
- You could split the values in two separate Datalink calls to have less than 65000 values per call
- Or you can re-size the array formula to make it big enough to contain the values and click on the refresh button to update the values.
Let us know if this works for you
How to manually resize the array formula ( of a PI DATALINK function ) :
- Select the existing data and continue the selection till you have expanded to the space you need. ( see image for example )
- Then press F2 to enter edit mode of the array formula
- Press Ctrl+Alt+Enter to finish and extend the formula to the newly selected range
- Right click on the selection and select Re-Calculate to refresh the data.
Hi! I got kind of the same problem. I wrote a code in VBA to extract Data in an automatic way, but when exceeding 65K rows or so I got that error. is there a way to solve it so the code runs smoothly and that i doesn't involve splitting the data?
And its kind of weird but when I call DLRSEIZE from vba code it doesn't generate the data and sends the error but when I call the Resieze manually (right click then reseize) it does generate the data correctly but it still sends the error.
hope for some help.
I have tried your solution to re-size the array formula, But when i right click on value am not able to see the ReCalculation option and am not able to modify the function also for *-2d.
Can you please provide the following information:
- The Excel version you are using
- The PI Datalink version you are using
If right click does not show re-size the array formula it is probably because your selection is too large.
Just select one cell that contains PI data, then righ click and you should see it.
I would also like to point out to you that we have a lot of resources for PI Datalink:
- Youtube video series (contains 13 videos): OSIsoft: What is PI DataLink - YouTube
- Documentation: https://techsupport.osisoft.com/Products/PI-Visualization/PI-DataLink
Let us know how it goes!
Thank you for your reply.
Hey sorry i have tried your 1st solution again, the re-calculate option was showing when i right click on values. But still the same error is showing (‘Outpu t exceeds sheet dimension. Try changing output cell or changing data orientation or decreasing number of events').
My excel version is 2010 and DataLink 4.1.1.
- Does it work if you change the Compress Data function start time and end time, so it retrive less data?
- I think that you might need to expand your array formula again. How big is it now? How much did you expend it? I recommend that you use the Calculated Data function first, use the same start time and end time as for the compressed data you want to achieve. Select calculation mode: count. Once you have that, resize the array formula so its rows count is bigger than the event count that you just calculated.
Hope this helps.