io ole overflow issue

[goat]

Hello,

Please can you help me with this issue.

I am trying to use an IO OLE connection to download data from our mainframe. It is connecting fine and retreiving the data but it only gets to about 32777 rows on excell then gives an overflow error. Ive changed all the settings to use long instead of integer but it still only brings back the same amouth of rows.

Im using the below code :

Option Explicit

Public Sub SecurityDistribution()
Dim c As Object
Dim rq As Object
Dim row As Long
Dim col As Long
Dim sName As String
Set c = CreateObject("SunGard.IOConnection")
c.CommType = "IP"
c.HostIP = Range("B2")
c.Port = Range("B3")
Select Case Range("A6")
Case "SP0"
c.SideInfo = UCase$(Range("SP0_REGION"))
c.Operator = Range("SP0_OPERATOR")
Set rq = c.CreateDistRequest
rq.Account = Range("SP0_ACCOUNT")
rq.RequestDate = Range("SP0_REQDATE")
If UCase$(Left$(Range("SP0_SCTYONLY"), 1)) = "N" Then
rq.SecuritiesOnly = False
End If
Case "SP1"
c.SideInfo = UCase$(Range("SP1_REGION"))
c.Operator = Range("SP1_OPERATOR")
Set rq = c.CreateTranRequest
rq.Account = Range("SP1_ACCOUNT")
rq.FromDate = Range("SP1_FROMDATE")
rq.ToDate = Range("SP1_TODATE")
Select Case Range("SP1_DATETYPE")
Case 1: rq.DateType = "E"
Case 2: rq.DateType = "T"
Case 3: rq.DateType = "C"
Case 4: rq.DateType = "S"
Case 5: rq.DateType = "G"
Case 6: rq.DateType = "N"
End Select
Select Case Range("SP1_POSITIONS")
Case 1: rq.Positions = "N"
Case 2: rq.Positions = "A"
Case 3: rq.Positions = "O"
End Select
Case "SP2"
c.SideInfo = UCase$(Range("SP2_REGION"))
c.Operator = Range("SP2_OPERATOR")
Set rq = c.CreateFundRequest
rq.Account = Range("SP2_ACCOUNT")
rq.ClassOfShares = Range("SP2_CLASSOFSHARES")
rq.RequestDate = Range("SP2_REQDATE")
rq.Periods = Range("SP2_PERIODS")
Select Case Range("SP2_PERIODTYPE")
Case 1: rq.PeriodTYpe = "P"
Case 2: rq.PeriodTYpe = "D"
Case 3: rq.PeriodTYpe = "W"
Case 4: rq.PeriodTYpe = "M"
Case 5: rq.PeriodTYpe = "Q"
Case 6: rq.PeriodTYpe = "Y"
Case 7: rq.PeriodTYpe = "A"
End Select
Case Else
MsgBox "Unable to interpret the spread sheet"
End Select

For row = 1 To 5
If Range(Range("A6") & "_F" & row & "ITEM") > "" Then
rq.AddFilter Item:=Range(Range("A6") & "_F" & row & "ITEM"), Operand:=Range(Range("A6") & "_F" & row & "OPER"), Value:=Range(Range("A6") & "_F" & row & "VALUE")
End If
Next row
col = 1
Do Until Trim$(Cells(8, col)) = ""
rq.AddItem Cells(8, col)
Cells(10, col).Value = rq.Items(Cells(8, col)).Title
col = col + 1
Loop

rq.Start
rq.Movenext

row = 11
Do Until Trim$(Cells(row, 1)) = ""
row = row + 1
Loop

Do Until rq.EOF
col = 1
Do Until Cells(8, col) = ""
Cells(row, col) = rq.ItemValue(Cells(8, col))
If rq.Items(Cells(8, col)).display = "General Date" Then
If Range("A6") <> "SP2" Then
If CDbl(Cells(row, col)) = 0 Then
Cells(row, col) = ""
Else
Cells(row, col).NumberFormat = "mm/dd/yyyy"
On Error Resume Next
Cells(row, col) = Format$(DateSerial(Mid(rq.ItemValue(Cells(8, col)), 1, 4), Mid(rq.ItemValue(Cells(8, col)), 5, 2), Mid(rq.ItemValue(Cells(8, col)), 7, 2)), Cells(row, col).NumberFormat)
On Error GoTo 0
End If
End If
Else
Cells(row, col).NumberFormat = rq.Items(Cells(8, col)).display
End If
col = col + 1
Loop
row = row + 1
rq.Movenext
Loop
MsgBox row - 11 & " rows loaded!"
End Sub