Application-defined error when populating cells in late-bound Excel automation object

[topher23]

I have an Access subroutine that needs to generate an Excel spreadsheet for accounting. I've used late-bound Excel automation before, but for some reason I keep running into a strange issue when I run this one.

This is the relevant portion of the procedure:

Private Sub GenerateExplodedBOM()
'On Error GoTo Err_Handler
Dim appXL As Object
Dim wbBOM As Object
Dim rs As New adodb.Recordset
 
    Set appXL = CreateObject("Excel.Application")
    appXL.Visible = True
    Set wbBOM = appXL.workbooks.Add
    wbBOM.Activate
    wbBOM.sheets.Add Before:=wbBOM.sheets(1)
    wbBOM.sheets(1).name = Forms!PDC_home!ItemNum & " Exploded BOM"
    wbBOM.sheets(1).Select
    rs.Open "SELECT * FROM tblProduct WHERE ItemNum = '" & _
 Forms!PDC_home!ItemNum & "'", CurrentProject.Connection, _
 adOpenForwardOnly
    With wbBOM.sheets(1)
        .cells(1, 1).value = rs!ItemNum
        .cells(1, 2).value = rs!PurchaseDescription
        .cells(1, 3).value = "1"
        .cells(1, 4).value = CCur(rs!BOMtotal)
    End With
 
    rs.Close
    Set rs = Nothing
 

When I run this, I always get "Application-defined error" when I hit ".cells(1, 1).value = rs!ItemNum" - but the crazy part is that, after the code breaks, I can hit "Run" and it continues right through, no problems. Any ideas on why this happens and how to work around it? Google hasn't been very helpful on this.

[topher23]

Grrr! I just tacked a

DoEvents

before the With block and now it works. Of all the ridiculous kludges!

[ADezii]

1. Why Late Binding?
2. Why the Sheets Collection instead of the Worksheets Collection?
3. Index 1 of the Worksheets/Sheets Collection has a special meaning in Excel - why not use .Activate/ActiveSheet instead?

[topher23]

1. Late binding because there are several different versions of Excel in use at our company, so this eliminates broken references.
2. I used to use Worksheets more often, but the Sheets collection allows direct reference to worksheets, chart "sheets", and modules (instead of just standard worksheets), so I tend to use that instead when coding.
3. I've had Activesheet give me unpredictable results with automation in the past, particularly when using .Visible=False. I feel more comfortable giving a specific sheet reference.

FWIW, whether I referenced Sheets(1) or Sheets("[ItemNum] Exploded BOM"), the application still threw the same error when I tried to write the cell values. I also tried with .Activate/ActiveSheet - still the same error. I'm not sure what Excel needs to process before it changes those cell values, but DoEvents lets it happen.

[ADezii]

Thanks Topher23, just wanted to satisfy my own curiosity.

the Sheets collection allows direct reference to worksheets, chart "sheets", and modules

What exactly do you mean by direct reference to modules?

[topher23]

Hmmm, looks like I may have been wrong about modules. I remembered reading in the past that you could use Sheets([modulename]) just like Modules([modulename]) in Access, but I just tried it and it didn't work. Further reading leads me to the belief that my memory on that may have been faulty. The Sheets collection only consists of the objects that show up as tabs in the GUI, which can also be referenced through the collections relating to their specific object types: Worksheets(), Charts(). So if tab 1 is a chart and tab 2 is a worksheet, tab 2 would be Worksheets(1) but Sheets(2).

[NeoPa]

FWIW the addition of a DoEvents typically indicates that correct working of the code depends on something catching up in the background. Either OS or Application code somewhere. By its very nature this is always difficult to tie down. Knowing it may be a problem is very helpful though. It means you can shove that one line in and get everything to work ;-)