Results 1 to 3 of 3

Thread: Run Time Error

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Run Time Error

    Some times program process the records but some times it gives run time error:
    Run-time error '-2147221080 (800401a8)':
    Method 'Cells' of object '_Worksheet' failed
    Total records in this query is usually between 50 to 70,000
    Also this error is not comming on any specific one line but its comming on different places
    Thanks.

    Private Sub DemoGraphicQueryProcess()
    Dim DBConn
    Dim swOnOff As Integer
    Dim rowcounter As Double
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim rngColumn As Excel.Range
    Dim rngCell As Excel.Range
    Dim strSource As String
    Dim mysheet As String
    Dim lngRow As Long

    Dim rs
    'This is Adaptive Server Enterprise Connection
    Set DBConn = CreateObject("ADODB.Connection")
    DBConn.Open "ODBC; Driver=Adaptive Server Anywhere 6.0; DSN=Debtmaster; uid=dm; pwd=,,PeAches..;"
    Dim sSQL As String
    sSQL = "select clt_ref_no,cur_addr1,cur_addr2,cur_city,cur_state, cur_zip, home_phone, work_phone, " & _
    "cur_bal, status_abbrev,debt_descr " & _
    ",substring(DEBT_DESCR,(LOCATE(DEBT_DESCR,':',1)+1 ), 20) AS ORIGINALACCOUNT " & _
    "from dbtr_view r join debt on debt_id=debtor_id " & _
    "WHERE CLT_ID LIKE 'IDTC%' AND R.STATUS_CODE<'400';"

    Set rs = DBConn.Execute(sSQL)

    strSource = "\\usnymel1fs001\new claims\IDT Carmel\upload\" & "EXHIBIT Demographic" & " " & Format(Date, "MMDDYY") & "." &

    Format$(Now, "hhmmss") & ".xls"

    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Add 'this creates a new, blank workbook
    mysheet = xlApp.Worksheets(1).Name
    xlApp.Columns("A:H").EntireColumn.AutoFit
    Set xlWS = xlWb.Worksheets(mysheet)
    xlWS.Range("A1").Value = "CFG_ACCOUNT"
    xlWS.Range("B1").Value = "ADDRESS1"
    xlWS.Range("C1").Value = "ADDRESS2"
    xlWS.Range("D1").Value = "CITY"
    xlWS.Range("E1").Value = "STATE"
    xlWS.Range("F1").Value = "ZIP"
    xlWS.Range("G1").Value = "PHONEHOME"
    xlWS.Range("H1").Value = "PHONEWORK"
    xlWS.Range("I1").Value = "BALANCE"
    xlWS.Range("J1").Value = "STATUS"
    xlWS.Range("K1").Value = "ORIGINALACCOUNT"
    xlWS.Range("L1").Value = "RELATIONSHIPID"
    lngRow = 2
    Do While Not rs.EOF
    Debug.Print rowcounter
    rowcounter = rowcounter + 1
    If rowcounter < 65530 Then
    xlWS.Cells(lngRow, 1).Value = rs!clt_ref_no
    xlWS.Cells(lngRow, 2).Value = rs!cur_addr1
    xlWS.Cells(lngRow, 3).Value = rs!cur_addr2
    xlWS.Cells(lngRow, 4).Value = rs!cur_city
    xlWS.Cells(lngRow, 5).Value = rs!cur_state
    xlWS.Cells(lngRow, 6).Value = rs!cur_zip
    xlWS.Cells(lngRow, 7).Value = rs!home_phone
    xlWS.Cells(lngRow, 8).Value = rs!work_phone
    xlWS.Cells(lngRow, 9).Value = rs!cur_bal
    xlWS.Cells(lngRow, 10).Value = rs!status_abbrev
    xlWS.Cells(lngRow, 11).Value = rs!debt_descr
    xlWS.Cells(lngRow, 12).Value = rs!ORIGINALACCOUNT
    lngRow = lngRow + 1
    rs.MoveNext
    End If

    If rowcounter > 65530 Then
    lngRow = 2
    xlWb.Close SaveChanges:=True, FileName:=strSource
    Set xlWb = Nothing
    Set xlWS = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    swOnOff = 1
    rowcounter = 0
    ShellExecute 0, vbNullString, Chr(34) & strSource & Chr(34), vbNullString, strSource, vbMaximizedFocus
    End If

    If swOnOff = 1 Then
    strSource = "\\usnymel1fs001\new claims\IDT Carmel\upload\" & "EXHIBIT Demographic" & " " & Format(Date, "MMDDYY") &

    "." & Format$(Now, "hhmmss") & ".xls"
    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Add 'this creates a new, blank workbook
    mysheet = xlApp.Worksheets(1).Name
    xlApp.Columns("A:H").EntireColumn.AutoFit
    Set xlWS = xlWb.Worksheets(mysheet)
    xlWS.Range("A1").Value = "CFG_ACCOUNT"
    xlWS.Range("B1").Value = "ADDRESS1"
    xlWS.Range("C1").Value = "ADDRESS2"
    xlWS.Range("D1").Value = "CITY"
    xlWS.Range("E1").Value = "STATE"
    xlWS.Range("F1").Value = "ZIP"
    xlWS.Range("G1").Value = "PHONEHOME"
    xlWS.Range("H1").Value = "PHONEWORK"
    xlWS.Range("I1").Value = "BALANCE"
    xlWS.Range("J1").Value = "STATUS"
    xlWS.Range("K1").Value = "ORIGINALACCOUNT"
    xlWS.Range("L1").Value = "RELATIONSHIPID"
    swOnOff = 0
    ShellExecute 0, vbNullString, Chr(34) & strSource & Chr(34), vbNullString, strSource, vbMaximizedFocus
    End If
    Loop

    xlWb.Close SaveChanges:=True, FileName:=strSource
    Set xlWb = Nothing
    Set xlWS = Nothing
    xlApp.Quit
    Set xlApp = Nothing

    End Sub

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    When running in the IDE, when you get an error, select the "debug" option (or press ctrl-break) to dump you into the debugger.

    Then, at the active line in the IDE, pause the mouse over the rs!field_name property in order to see just what is in the recordset. (checking to see if the recordset field contains a null.)

    BTW. Your app will be more efficient if you lose the late binding on the database objects, and use early binding instead.

    instead of
    Set DBConn = CreateObject("ADODB.Connection")
    use
    Set DBConn = New ADODB.Connection
    DBConn.Open

    and instead of
    Set rs = DBConn.Execute(sSQL)
    use
    Set rs = New ADODB.Recordset
    rs.Open sSQL, DBConn

    As far as I'm concerned, the CreateObject approach is only valid in ASP scripting apps, where you CAN'T DIM an object as an object type.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Not related to your question, but:
    Quote Originally Posted by mustish1
    If rowcounter < 65530 Then
    ... 'Store data
    End If
    If rowcounter > 65530 Then
    ... 'Save sheet
    End If
    This looks like you lose a row of data when rowcounter = 65530.

Posting Permissions

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