By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,472 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,472 IT Pros & Developers. It's quick & easy.

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

topher23
Expert 100+
P: 234
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub GenerateExplodedBOM()
  2. 'On Error GoTo Err_Handler
  3. Dim appXL As Object
  4. Dim wbBOM As Object
  5. Dim rs As New adodb.Recordset
  6.  
  7.     Set appXL = CreateObject("Excel.Application")
  8.     appXL.Visible = True
  9.     Set wbBOM = appXL.workbooks.Add
  10.     wbBOM.Activate
  11.     wbBOM.sheets.Add Before:=wbBOM.sheets(1)
  12.     wbBOM.sheets(1).name = Forms!PDC_home!ItemNum & " Exploded BOM"
  13.     wbBOM.sheets(1).Select
  14.     rs.Open "SELECT * FROM tblProduct WHERE ItemNum = '" & _
  15.  Forms!PDC_home!ItemNum & "'", CurrentProject.Connection, _
  16.  adOpenForwardOnly
  17.     With wbBOM.sheets(1)
  18.         .cells(1, 1).value = rs!ItemNum
  19.         .cells(1, 2).value = rs!PurchaseDescription
  20.         .cells(1, 3).value = "1"
  21.         .cells(1, 4).value = CCur(rs!BOMtotal)
  22.     End With
  23.  
  24.     rs.Close
  25.     Set rs = Nothing
  26.  
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.
Feb 21 '14 #1

✓ answered by 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 ;-)

Share this Question
Share on Google+
6 Replies


topher23
Expert 100+
P: 234
Grrr! I just tacked a
Expand|Select|Wrap|Line Numbers
  1. DoEvents
before the With block and now it works. Of all the ridiculous kludges!
Feb 21 '14 #2

ADezii
Expert 5K+
P: 8,597
  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?
Feb 22 '14 #3

topher23
Expert 100+
P: 234
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.
Feb 22 '14 #4

ADezii
Expert 5K+
P: 8,597
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?
Feb 22 '14 #5

topher23
Expert 100+
P: 234
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).
Feb 24 '14 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 ;-)
Feb 24 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.