Overflow -- 6 error

[DJShay]

I have been using the code below for several months now with no problem. But suddenly this morning I get an Overflow -- 6 error. The code is below and it seems to be erroring out at the "For y = RowStart To rst.RecordCount" part. I'm not sure what is causing this.


Private Sub Command17_Click()
On Error GoTo CloseRecords
Dim objexcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field

Dim x As Integer
Dim y As Integer
Dim rcount
Const fcount = 11
Dim RowStart As Integer



Set db = CurrentDb
Set rst = db.OpenRecordset("Loss Prevention Data", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
Set objexcel = CreateObject("excel.application")
objexcel.Visible = False
Set wkb = objexcel.Workbooks.Open("\\NCMAIN03\USERS\COMMON_H R\#HRIS\PLATEAU REPORTING\LP_Compliance_Report\LP_Compliance_Repor t_Template.xls")
Set wks = wkb.Worksheets("Compliance Detail")
'end test segment
RowStart = 1 ' renumbers rows
For y = RowStart To rst.RecordCount
For x = 1 To fcount
wks.Cells(y + 1, x).Value = rst.Fields(x - 1).Value
Next x
rst.MoveNext
Next y

wks.Cells.EntireColumn.AutoFit

Set wks = wkb.Worksheets("Dist %")

Set rst = db.OpenRecordset("Dist%Complete", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
RowStart = 1 ' renumbers rows
For y = RowStart To rst.RecordCount
For x = 1 To fcount - 9
wks.Cells(y + 1, x).Value = rst.Fields(x - 1).Value
Next x
rst.MoveNext
Next y

wks.Cells.EntireColumn.AutoFit

wks.SaveAs "\\NCMAIN03\USERS\COMMON_HR\#HRIS\PLATEAU REPORTING\LP_Compliance_Report\LP_Compliance_Repor t_" & Format(Now(), "MMDDYYYY") & ".xls"





objexcel.Dialogs.Application.ActiveWorkbook.Save
objexcel.Workbooks.Close
objexcel.UserControl = True
objexcel.Quit
Exit_MyProc:
rst.Close
Set wks = Nothing
Set objexcel = Nothing
Set rst = Nothing
Set db = Nothing
Exit Sub

CloseRecords:
MsgBox Err.Description & " -- " & Err.Number
Resume Exit_MyProc
Resume


End Sub

[philadams-uk]

Hi DJ

the variables X, Y etc are defined as Integer, so they can hold a maximum of 32768..... so if your database enquiry holds more rows than this, you will suffer an overflow. To correct this switch the integer declarations for "Long"

alternatively, i have seen the 'overflow' error raised when a numeric value is applied to a cell which is formatted as "Date" rather than "General" (or other format suitable for numerics)... so if the above is not the answer, maybe worth a quick check on the formatting.

Hope this helps

Cheers
Phil

[DJShay]

Thanks! That worked :-)