Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2006
    Location
    Charlotte, North Carolina, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    towcester, england, UK
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  3. #3
    New Lounger
    Join Date
    May 2006
    Location
    Charlotte, North Carolina, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks! That worked :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •