[RESOLVED] Application-defined or object defined error in Excel VBA from VB6

[RobDog888]

Note: Posting this thread for Hack

I am getting an "Runtime error 1004 Application-defined or object defined error" on the highlighted line when I run this sub

Private Sub PrintIssues()
Dim i As Long
 
    Set objExcel = New Excel.Application
    Set bkWorkBook = objExcel.Workbooks.Add
    Set shWorkSheet = bkWorkBook.ActiveSheet
      If optAppeal.Value = True Then
        shWorkSheet.Range("A1") = "Appeals Issues Log"
      Else
        shWorkSheet.Range("A1") = "Reopens Issues Log"
      End If
        shWorkSheet.Range("B3") = "Prov. Name:  " & strProvName
        shWorkSheet.Range("A4") = "Prov. Number:  " & strProvCode
       ' shWorkSheet.Range("C3") = "FYE:  " & cboFYE.Text
      If optAppeal.Value = True Then
        shWorkSheet.Range("A5") = "Appeal"
      Else
        shWorkSheet.Range("A5") = "Reopen"
      End If
        shWorkSheet.Range("C5") = "Number " & gstrAppealNo
        shWorkSheet.Range("A8") = "Issue No"
        shWorkSheet.Range("B8") = "Issue"
        shWorkSheet.Range("C8") = "Analyst"
        shWorkSheet.Range("D8") = "Disposition"
        shWorkSheet.Range("E8") = "Est. Impact Amount"
        shWorkSheet.Range("F8") = "Act. Impact Amount"
        shWorkSheet.Range("G8") = "Comments"
 
    Set rngRowStart = shWorkSheet.Range("A10")
          
        bkWorkBook.Worksheets(1).Columns(1).HorizontalAlignment = xlLeft
        bkWorkBook.Worksheets(1).PageSetup.Orientation = xlLandscape
        bkWorkBook.Worksheets(1).PageSetup.Zoom = False
        'in order for these PageSetup things to work, the ZOOM property
        'must be set to false
        bkWorkBook.Worksheets(1).PageSetup.FitToPagesWide = 1
        bkWorkBook.Worksheets(1).PageSetup.FitToPagesTall = 1
        bkWorkBook.Worksheets(1).Columns("E:E").HorizontalAlignment = xlCenter
        bkWorkBook.Worksheets(1).Columns("F:F").HorizontalAlignment = xlCenter
             
            For i = 1 To lvwIssues.ListItems.Count
                'Place each element in the coresponding column
                rngRowStart.Offset(0, 0).Value = lvwIssues.ListItems(1).Text 'issue number
                rngRowStart.Offset(0, 1).Value = lvwIssues.ListItems(2).Text 'issue description
                rngRowStart.Offset(0, 2).Value = lvwIssues.ListItems(3).Text 'analyst
                rngRowStart.Offset(0, 3).Value = lvwIssues.ListItems(4).Text 'disposition
                rngRowStart.Offset(0, 4).Value = Format(lvwIssues.ListItems(5).Text, "###,#0") 'est impact amt
                rngRowStart.Offset(0, 5).Value = Format(lvwIssues.ListItems(6).Text, "###,#0") 'act impact amt
                rngRowStart.Offset(0, 6).Value = lvwIssues.ListItems(7).Text
            Next
                    
            'Next Row
            Set rngRowStart = rngRowStart.Offset(1, 0)
       
       'start with first row of recordset display and wedgie down a couple
       lngLast = bkWorkBook.Worksheets(1).Range("A10").End(xlDown).Row + 2
       [hl=yellow]bkWorkBook.Worksheets(1).Cells(lngLast, 5).Value = Format(lblEstImpAmt.Caption, "###,#0")[/hl] 'est impact total
       bkWorkBook.Worksheets(1).Cells(lngLast, 6).Value = Format(lblActImpAmt.Caption, "###,#0") 'act impact total
 
        'make sure everything displays properly.
        shWorkSheet.Columns("A:BZ").AutoFit
       
        objExcel.Visible = True
End Sub

[Hack]

Thanks for posting this question for me Rob!

Lets approach this from another angle and ask the question a different way.

Each time this report is run, it will display a different number of records. Regardless of how many records there are, I need to display two totals two rows below whatever the last row is. The totals will always be in columns E and F, but the rows they will be in will differ each time the Excel report is run.

What I'm doing isn't working. How would you do it?

[zaza]

Just a quick interjection, could it be that your label caption:

lblEstImpAmt.Caption

needs to be

bkWorkBook.Worksheets(1).lblEstImpAmt.Caption

I've been caught by that before...


As far as your last question goes, I don't know that there are any easier ways of doing it. You could use the UsedRange or SpecialCells methods to figure out where the last used cells are instead of using End, but I don't know that it will make a vast difference unless somebody has already been filling in that column with other data and it is fooled into thinking that the end of the column is row 45601.


zaza

[Hack]

Just a quick interjection, could it be that your label caption:

lblEstImpAmt.Caption

needs to be

bkWorkBook.Worksheets(1).lblEstImpAmt.Caption

I've been caught by that before...

zaza

But, the label isn't on my worksheet. It is on a VB6 form.

[zaza]

Then "Userform1." ? I guess this code is also within the userform then, so it seems unlikely that this would be causing the problem, but if it's in a module or in the worksheet...


Ah, whoops, it's automated, non?

My mistake.


Why do you swap from using the defined sheet to using worksheets(1), BTW? I guess if you step through it, you'll find that lnglast isn't coming up with a number that can be used in the cell? Maybe CInt is the way forward or, as you say, another method of finding the last used cell.

For example:

Set c = Range("E:E").SpecialCells(xlCellTypeLastCell)
msgbox(c.row)

will return the row of the last used cell in column E. You want two cells below that...

[zaza]

Actually UsedRange probably won't work - it just applies to the worksheet rather than the column. But SpecialCells will.

zaza

[jcis]

I tested that code and lngLast = 65538, because..

bkWorkBook.Worksheets(1).Range("A10").End(xlDown).Row = 65536

Which is the last possible row number for a column I think. Is it ok?

Edit: I was adding values to cells the wrong way, now lngLast is getting the correct row number. By the way, you are not using i variable in the loop, i think it should be..

For i = 1 To lvwIssues.ListItems.Count
        'Place each element in the coresponding column
        rngRowStart.Offset(i, 0).Value = lvwIssues.ListItems(1).Text 'issue number
        rngRowStart.Offset(i, 1).Value = lvwIssues.ListItems(2).Text 'issue description
        rngRowStart.Offset(i, 2).Value = lvwIssues.ListItems(3).Text 'analyst
        rngRowStart.Offset(i, 3).Value = lvwIssues.ListItems(4).Text 'disposition
        rngRowStart.Offset(i, 4).Value = Format(lvwIssues.ListItems(5).Text, "###,#0") 'est impact amt
        rngRowStart.Offset(i, 5).Value = Format(lvwIssues.ListItems(6).Text, "###,#0") 'act impact amt
        rngRowStart.Offset(i, 6).Value = lvwIssues.ListItems(7).Text
    Next

[Comintern]

Posted over on CodeGuru, but I'll share over here too. If the range that you find the .End from starts at the last row (or higher) of the used range, it returns 65536. It should work normally if "A10" is within the UsedRange.

[Hack]

Posted over on CodeGuru, but I'll share over here too. If the range that you find the .End from starts at the last row (or higher) of the used range, it returns 65536. It should work normally if "A10" is within the UsedRange.

I posted it on CG just to see if I could, and I could. I got an error when I tried to post it here, which I'm looking into. That is why RobDog888 posted the question for me.

How would I ensure A10 is within the UsedRange?

[Hack]

By the way, you are not using i variable in the loop, i think it should be..

For i = 1 To lvwIssues.ListItems.Count
        'Place each element in the coresponding column
        rngRowStart.Offset(i, 0).Value = lvwIssues.ListItems(1).Text 'issue number
        rngRowStart.Offset(i, 1).Value = lvwIssues.ListItems(2).Text 'issue description
        rngRowStart.Offset(i, 2).Value = lvwIssues.ListItems(3).Text 'analyst
        rngRowStart.Offset(i, 3).Value = lvwIssues.ListItems(4).Text 'disposition
        rngRowStart.Offset(i, 4).Value = Format(lvwIssues.ListItems(5).Text, "###,#0") 'est impact amt
        rngRowStart.Offset(i, 5).Value = Format(lvwIssues.ListItems(6).Text, "###,#0") 'act impact amt
        rngRowStart.Offset(i, 6).Value = lvwIssues.ListItems(7).Text
    Next

I'll give this a shot. Thanks.

[Comintern]

I posted it on CG just to see if I could, and I could. I got an error when I tried to post it here, which I'm looking into. That is why RobDog888 posted the question for me.

How would I ensure A10 is within the UsedRange?

Something like this works:

If bkWorkBook.Worksheets(1).UsedRange.Rows > 10 Then
    '...

[Hack]

Thanks to jcis and Comintern I have this working.

(Thanks to RobDog888 for posting this question for me too! )