Excel Automation Error 91

[mike777]

Hello folks.
I have a procedure where I'm automating Excel.

1  Option Compare Database: Option Explicit:   Option Base 1
2  Dim xlApp as Excel.Application,xlWkb as Excel.Workbook, xlWks as Excel.Worksheet
3  sub some_procedure
4       on error goto errorhandler
5       set xlApp=getobject(,"Excel.Application")
6       xlapp.workbooks.open "c:\temp\someworkbook.xls"
7       set xlwkb=xlapp.activeworkbook
8       set xlwks=xlwkb.activesheet
9       with xlwks
10           xlwks.range("a1").horizontalalignment=xlleft
11           do more xlWks formatting here...
12      end with
13      exit sub
14 errorhandler:
15      if err.number=429 then
16           set xlApp=createobject("Excel.Application")
17              resume
18      elseif err.number=91 then
19          set xlWkb=xlApp.activeworkbook
20           set xlWks=xlWkb.activesheet
21           resume
22      else
23           msgbox str(err.number) & vbCrLf & err.description
24      end if
25 end sub

The problem is an intermittent one.
Everything consistenly works fine up to line 7 or line 10 (not sure which). At some point MS Access loses its ability to communicate with Excel and starts spitting back "Oject variable not set...", or whatever that message is for Error 91.
The way I know the error is happening on those lines is because whenever it occurs, Excel is always open, and the target workbook is also open. The formatting, however, is not done. So it got past the point of opening the workbook and choked.
Like I said, it only happens, maybe 1 out of 20 tries, but that being said, 1 out of 20 times is 5%, which is a pretty severe rate of failure.
I tried to trap the error there on line 17, but the fix I propose is not effective (never helps, it seems).
Do you have any suggestions? I've searched MSDN and every other place I could find via Google. (By the way, does anybody ever get anything out of MSDN? I go there all the time and rarely (maybe 10-20% of the time) get any good help from it.)
Many thanks.
-Mike Kemp
P.S. The code above is just a mock-up of the real code. If there's some minor syntax errors there, you can pretty much ignore them, since I didn't do a copy and paste from the real code.
P.P.S I know VB is an event-driven language, but I am just curious why they can't return the line number of an error. Or does it? I think no, but when procedures get really long, it would incredibly helpful to know which line in the code (going by the top of the module being line 1, I guess) caused the error. Am I missing something? Thanks again.

 

[SkipVought]

Hi,

You were using the xlApp object incorrectly

6       Set xlWkb = xlApp.Workbooks.Open("c:\temp\someworkbook.xls")
8       Set xlWks = xlWkb.ActiveSheet
9       With xlWks
10           .Range("a1").HorizontalAlignment = xlLeft

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[mike777]

Thanks, Skip.
We haved multiple versions of MS Office. Office 2000, Office 2002 (XP, I think) and Office 2003 (XP Pro maybe, not sure).
Anyway, with at least one of those versions

set xlWkb=xlApp.Workbooks.Open("c:\temp\someworkbook.xls")

doesn't work. In reality, I think that does work, but my real-life scenario is:

xlApp.Workbooks.OpenText "c:\temp\sometextfile.txt",,xlDelimited,xlWindows,false,false,false,false,"|" blahablahblah

I'm at home right now and can't remember the exact syntax for all of that. Anyway, it's the OpenText method that doesn't work with

set xlWkb=xlApp.Workbooks.OpenText "c:\temp\sometextfile.txt",etc..

The way I have it with xlApp.Workbooks.OpenText works with any version of Office.
Whew! Explaining myself into the ground here. Is this making sense? Additionally, while the way you have it looks more proper, I don't see a real problem with my method (unless, of course, I'm missing something).
Thanks for the help. If you have any further insight, I would really appreciate it.
-Mike