Calling Excel function..having large dataset

D

Dale

Hi all,
I'm using this subroutine to call an excel function to calculate the 90th
percentile
within Access on a dataset of >437,000 records. The sub works with a
limited
number of records but on 437,000 records fails with a runtime error 13 "type
mismatch"
at line " dblResult = appXL.Application.Percentile((dblArray()), 0.9) ".

Is there a limitation to the number of records Excel can handle i.e. is
the limitation still 65,000 rows even though the call is from within the
database?

Thank you..Dale

Sub ExcelFun()
Dim appXL As Excel.Application
Dim dbs As Database, rst As Recordset
Dim strSQL As String, intI As Long
Dim dblArray() As Double, dblResult As Double

' Create SQL string.
strSQL = "SELECT [result] FROM [InLab];"
' Return reference to current database.
Set dbs = CurrentDb
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset(strSQL)
' Populate recordset.
rst.MoveLast
' Return to beginning of recordset.
rst.MoveFirst
' Redimension array based on recordset size.
ReDim dblArray(0 To rst.RecordCount - 1)
' Populate array with values from recordset.
For intI = 0 To UBound(dblArray)
dblArray(intI) = rst![result]
rst.MoveNext
Next
' Create new instance of Excel Application object.
Set appXL = CreateObject("Excel.Application")
' Pass array to Excel's Median function.
dblResult = appXL.Application.Percentile((dblArray()), 0.9)
Debug.Print dblResult
' Close Excel.
appXL.Quit
' Free object variables.
Set appXL = Nothing
Set dbs = Nothing
End Sub
 
D

Dale

Thanks Doug..
I just tested a dataset of 9000 records, it failed again, but less than 20
seems to work!!
Well back to the drawing board.
Thanks


Douglas J. Steele said:
AFAIK, the Excel limit doesn't care where the data's coming from.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Dale said:
Hi all,
I'm using this subroutine to call an excel function to calculate the 90th
percentile
within Access on a dataset of >437,000 records. The sub works with a
limited
number of records but on 437,000 records fails with a runtime error 13
"type mismatch"
at line " dblResult = appXL.Application.Percentile((dblArray()), 0.9) ".

Is there a limitation to the number of records Excel can handle i.e. is
the limitation still 65,000 rows even though the call is from within the
database?

Thank you..Dale

Sub ExcelFun()
Dim appXL As Excel.Application
Dim dbs As Database, rst As Recordset
Dim strSQL As String, intI As Long
Dim dblArray() As Double, dblResult As Double

' Create SQL string.
strSQL = "SELECT [result] FROM [InLab];"
' Return reference to current database.
Set dbs = CurrentDb
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset(strSQL)
' Populate recordset.
rst.MoveLast
' Return to beginning of recordset.
rst.MoveFirst
' Redimension array based on recordset size.
ReDim dblArray(0 To rst.RecordCount - 1)
' Populate array with values from recordset.
For intI = 0 To UBound(dblArray)
dblArray(intI) = rst![result]
rst.MoveNext
Next
' Create new instance of Excel Application object.
Set appXL = CreateObject("Excel.Application")
' Pass array to Excel's Median function.
dblResult = appXL.Application.Percentile((dblArray()), 0.9)
Debug.Print dblResult
' Close Excel.
appXL.Quit
' Free object variables.
Set appXL = Nothing
Set dbs = Nothing
End Sub
Click to expand...
Click to expand...
 
D

Dale

Ok I don't get it,
With a recordset of 19362 records well below the excel limit of 65000 rows,
the sub works with up to 5460 records....beyond that I get the type mismatch
error. There are no nulls.
I modified this line " dblArray(intI) = rst![result] " to " dblArray(intI) =
cdbl(rst![result])
What am I missing? or is that as good as it gets..sigh..

Dale said:
Thanks Doug..
I just tested a dataset of 9000 records, it failed again, but less than 20
seems to work!!
Well back to the drawing board.
Thanks


Douglas J. Steele said:
AFAIK, the Excel limit doesn't care where the data's coming from.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Dale said:
Hi all,
I'm using this subroutine to call an excel function to calculate the
90th percentile
within Access on a dataset of >437,000 records. The sub works with a
limited
number of records but on 437,000 records fails with a runtime error 13
"type mismatch"
at line " dblResult = appXL.Application.Percentile((dblArray()), 0.9) ".

Is there a limitation to the number of records Excel can handle i.e. is
the limitation still 65,000 rows even though the call is from within the
database?

Thank you..Dale

Sub ExcelFun()
Dim appXL As Excel.Application
Dim dbs As Database, rst As Recordset
Dim strSQL As String, intI As Long
Dim dblArray() As Double, dblResult As Double

' Create SQL string.
strSQL = "SELECT [result] FROM [InLab];"
' Return reference to current database.
Set dbs = CurrentDb
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset(strSQL)
' Populate recordset.
rst.MoveLast
' Return to beginning of recordset.
rst.MoveFirst
' Redimension array based on recordset size.
ReDim dblArray(0 To rst.RecordCount - 1)
' Populate array with values from recordset.
For intI = 0 To UBound(dblArray)
dblArray(intI) = rst![result]
rst.MoveNext
Next
' Create new instance of Excel Application object.
Set appXL = CreateObject("Excel.Application")
' Pass array to Excel's Median function.
dblResult = appXL.Application.Percentile((dblArray()), 0.9)
Debug.Print dblResult
' Close Excel.
appXL.Quit
' Free object variables.
Set appXL = Nothing
Set dbs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Dale,

The limit is 8,191 - as quoted from the Help on the Percentile function.

"If array is empty or contains more than 8,191 data points, PERCENTILE
returns the #NUM! error value."

Not good for your needs.



Dale said:
Ok I don't get it,
With a recordset of 19362 records well below the excel limit of 65000 rows,
the sub works with up to 5460 records....beyond that I get the type mismatch
error. There are no nulls.
I modified this line " dblArray(intI) = rst![result] " to " dblArray(intI) =
cdbl(rst![result])
What am I missing? or is that as good as it gets..sigh..

Dale said:
Thanks Doug..
I just tested a dataset of 9000 records, it failed again, but less than 20
seems to work!!
Well back to the drawing board.
Thanks


Douglas J. Steele said:
AFAIK, the Excel limit doesn't care where the data's coming from.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi all,
I'm using this subroutine to call an excel function to calculate the
90th percentile
within Access on a dataset of >437,000 records. The sub works with a
limited
number of records but on 437,000 records fails with a runtime error 13
"type mismatch"
at line " dblResult = appXL.Application.Percentile((dblArray()), 0.9) ".

Is there a limitation to the number of records Excel can handle i.e. is
the limitation still 65,000 rows even though the call is from within the
database?

Thank you..Dale

Sub ExcelFun()
Dim appXL As Excel.Application
Dim dbs As Database, rst As Recordset
Dim strSQL As String, intI As Long
Dim dblArray() As Double, dblResult As Double

' Create SQL string.
strSQL = "SELECT [result] FROM [InLab];"
' Return reference to current database.
Set dbs = CurrentDb
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset(strSQL)
' Populate recordset.
rst.MoveLast
' Return to beginning of recordset.
rst.MoveFirst
' Redimension array based on recordset size.
ReDim dblArray(0 To rst.RecordCount - 1)
' Populate array with values from recordset.
For intI = 0 To UBound(dblArray)
dblArray(intI) = rst![result]
rst.MoveNext
Next
' Create new instance of Excel Application object.
Set appXL = CreateObject("Excel.Application")
' Pass array to Excel's Median function.
dblResult = appXL.Application.Percentile((dblArray()), 0.9)
Debug.Print dblResult
' Close Excel.
appXL.Quit
' Free object variables.
Set appXL = Nothing
Set dbs = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Message from Access 2007 on sub function 3
Closing Excel Reference 4
Drill down using DAO 2
append SQL no records 8
Acc export: getting around Excel row limits 6
Exporting multiple Excel spreadhsheets using Excel templates 7
Do I need a Nested Loop? 14
Function Variables 1

Top