Accessing empty Excel cells via OLE automation from VB 3.0 
Author Message
 Accessing empty Excel cells via OLE automation from VB 3.0

I'm building a VB 3.0 application which starts Excel 5 and uses it's
services via OLE automation. I create an object to Excel and I can
reference cells with the expression xl.cells(i,j).value IF THE CELL AT
(i,j) IS NOT EMPTY. However, when the cell is empty, I get an error "OLE
Automation method did not return a value"

I have tried using the VB IsNull function but it gives the same error
message. I have been able to circumvent it by using On Error Goto, but
it is very kludgy.

The SQL language has a function NVL(exp,dflt), which returns dflt if exp
is null. Is there a similar construct in Excel OLE functions/properties
or in VB 3.0?

What is a clean way to circumvent the error?
--



Sat, 08 Aug 1998 03:00:00 GMT  
 Accessing empty Excel cells via OLE automation from VB 3.0
In comp.apps.spreadsheets on 20 Feb 96 15:59:19 EET,

Quote:

> I'm building a VB 3.0 application which starts Excel 5 and uses it's
> services via OLE automation. I create an object to Excel and I can
> reference cells with the expression xl.cells(i,j).value IF THE CELL AT
> (i,j) IS NOT EMPTY. However, when the cell is empty, I get an error "OLE
> Automation method did not return a value"
> I have tried using the VB IsNull function but it gives the same error
> message. I have been able to circumvent it by using On Error Goto, but
> it is very kludgy.
> The SQL language has a function NVL(exp,dflt), which returns dflt if exp
> is null. Is there a similar construct in Excel OLE functions/properties
> or in VB 3.0?
> What is a clean way to circumvent the error?
> --


I and others have had the same problems with OLE to Excel. A friend of
mine told me it works well on his computer, and he also told me that
he never has any gpf's. I'm going to make sure I have the latest OLE
dll's as well as eliminate random gpf's. I already cleaned up my
fonts, so I know that is not a problem. Here's what I'm trying (from
the Windows FAQ).

Start Windows with the command

win /d:xsv

if everything is ok, add the line

VirturalHDIRQ=off

to the [386]Enh section of the system.ini file. Next, try

win /d:xs

and if this eliminates gpf's, add

SystemROMBreakPoint=false

to the [386Enh] section of system.ini. The last one is

win /d:x

and if this eliminates gpf's add

EMMExclude=A000-EFFF

to the [386Enh] section of system.ini.
Dale Smith, Quantitative Analyst, ADAM Investment Services



Sun, 09 Aug 1998 03:00:00 GMT  
 Accessing empty Excel cells via OLE automation from VB 3.0
In comp.apps.spreadsheets on Thu, 22 Feb 96 08:29:21 PDT,

Quote:

> In comp.apps.spreadsheets on 20 Feb 96 15:59:19 EET,

> > I'm building a VB 3.0 application which starts Excel 5 and
> > uses it's services via OLE automation. I create an object to
> > Excel and I can reference cells with the expression
> > xl.cells(i,j).value IF THE CELL AT (i,j) IS NOT EMPTY.
> > However, when the cell is empty, I get an error "OLE
> > Automation method did not return a value"
> You can work around this using an 'on error' block.
> These five simple steps will keep your program from crashing
> when it reads a blank cell and will set your variable to a
> default value as well.
> Follow these steps:
> 1. Set the variable equal to the cell value just like you are
> doing right now.
> 2. intercept the error using an 'on error goto' block.
> 3. check the reserved variable "Err" to see what the error
> number is.
> 4. If Err=437 then the error was caused by trying to read a
> blank cell.  Insert code to set your variable to whatever value
> you want it to be if the cell is blank.
> 5. resume next
> good luck

Nice tip. My problem was in a loop-the first time through things were
fine, but the second time I could put numbers in the sheet, but
reading them generated a GPF. Maybe this is the answer, I'll try it.

If anyone else has any info, please post it!
-------
Dale Smith, Quantitative Analyst, ADAM Investment Services
Any opinions expressed here are mine & not my employer's

For files/info available via auto-respond email, send a message to



Mon, 10 Aug 1998 03:00:00 GMT  
 Accessing empty Excel cells via OLE automation from VB 3.0

In comp.apps.spreadsheets on 20 Feb 96 15:59:19 EET,

Quote:

> I'm building a VB 3.0 application which starts Excel 5 and
> uses it's services via OLE automation. I create an object to
> Excel and I can reference cells with the expression
> xl.cells(i,j).value IF THE CELL AT (i,j) IS NOT EMPTY.
> However, when the cell is empty, I get an error "OLE
> Automation method did not return a value"

You can work around this using an 'on error' block.
These five simple steps will keep your program from crashing
when it reads a blank cell and will set your variable to a
default value as well.
Follow these steps:

1. Set the variable equal to the cell value just like you are
doing right now.

2. intercept the error using an 'on error goto' block.

3. check the reserved variable "Err" to see what the error
number is.

4. If Err=437 then the error was caused by trying to read a
blank cell.  Insert code to set your variable to whatever value
you want it to be if the cell is blank.

5. resume next

good luck



Mon, 10 Aug 1998 03:00:00 GMT  
 Accessing empty Excel cells via OLE automation from VB 3.0

Quote:

> If anyone else has any info, please post it!
> -------

I almost forgot.  To learn all about the GPF problem read the
Visual Basic knowledge base article described below.  Microsoft
can fax it to you if you don't have it.

Document Q114000
Title BUG: Nesting OLE Automation Calls Causes GP Fault
Keywords kbole kbbuglist

This article is probably exactly what you want to know.  It
basically states that reading and writing to too many Excel
cells in one subroutine doesn't release memory or something and
causes the GPF.  They don't mention any kind of a workaround,
bummer.  BTW, I am assuming that you are using VB verison 3.0
which is what the article is about.
Good Luck.



Tue, 11 Aug 1998 03:00:00 GMT  
 Accessing empty Excel cells via OLE automation from VB 3.0
In comp.apps.spreadsheets on Fri, 23 Feb 96 08:43:24 PDT,

Quote:

> > If anyone else has any info, please post it!
> > -------
> I almost forgot.  To learn all about the GPF problem read the
> visual basic knowledge base article described below.  Microsoft
> can fax it to you if you don't have it.
> Document Q114000
> Title BUG: Nesting OLE Automation Calls Causes GP Fault
> Keywords kbole kbbuglist
> This article is probably exactly what you want to know.  It
> basically states that reading and writing to too many Excel
> cells in one subroutine doesn't release memory or something and
> causes the GPF.  They don't mention any kind of a workaround,
> bummer.  BTW, I am assuming that you are using VB verison 3.0
> which is what the article is about.
> Good Luck.

That's what I'm using, and your info comes at a good time. I've got to
use some of the statistical routines in Excel to check some code from
Numerical Recipies in Basic. This means I'll put the OLE calls in a
short subroutine which will force the release of memory at the end of
the sub. I'll either put it in the same form as the main sub or in a
module and see which works best.
-------
Dale Smith, Quantitative Analyst, ADAM Investment Services
Any opinions expressed here are mine & not my employer's

For files/info available via auto-respond email, send a message to



Tue, 11 Aug 1998 03:00:00 GMT  
 Accessing empty Excel cells via OLE automation from VB 3.0
In comp.lang.basic.visual.misc on Wed, 21 Feb 1996 14:45:10 GMT,

Quote:

> In comp.apps.spreadsheets on 20 Feb 96 15:59:19 EET,

> > I'm building a VB 3.0 application which starts Excel 5 and uses it's
> > services via OLE automation. I create an object to Excel and I can
> > reference cells with the expression xl.cells(i,j).value IF THE CELL AT
> > (i,j) IS NOT EMPTY. However, when the cell is empty, I get an error "OLE
> > Automation method did not return a value"
> > I have tried using the VB IsNull function but it gives the same error
> > message. I have been able to circumvent it by using On Error Goto, but
> > it is very kludgy.
> > The SQL language has a function NVL(exp,dflt), which returns dflt if exp
> > is null. Is there a similar construct in Excel OLE functions/properties
> > or in VB 3.0?
> > What is a clean way to circumvent the error?
> > --

> I and others have had the same problems with OLE to Excel. A friend of
> mine told me it works well on his computer, and he also told me that
> he never has any gpf's. I'm going to make sure I have the latest OLE
> dll's as well as eliminate random gpf's. I already cleaned up my
> fonts, so I know that is not a problem. Here's what I'm trying (from
> the Windows FAQ).
> Start Windows with the command
> win /d:xsv
> if everything is ok, add the line
> VirturalHDIRQ=off
> to the [386]Enh section of the system.ini file. Next, try
> win /d:xs
> and if this eliminates gpf's, add
> SystemROMBreakPoint=false
> to the [386Enh] section of system.ini. The last one is
> win /d:x
> and if this eliminates gpf's add
> EMMExclude=A000-EFFF
> to the [386Enh] section of system.ini.
> Dale Smith, Quantitative Analyst, ADAM Investment Services


I used the following information to eliminate these problems. My app
which calls Excel in a loop runs very slowly, of course, but it DOES
NOT GPF!

KB articles on OLE Automation, GPF's, and Excel using VB 3/4:
http://198.105.232.5:80/kb/developr/vb/q113438.htm
http://198.105.232.5:80/kb/developr/vb/q137028.htm
http://198.105.232.5:80/kb/developr/vb/q129838.htm
http://198.105.232.5:80/kb/developr/vb/q114000.htm

I put all OLE Automation code in a sub along with the shell toExcel
and the obj.Application.Quit. I use loops in the sub to put things in
cells (a 60x9 retangular range) and to get back answers once I use an
Excel 4 macro function (regress).

I have no problems with this at all, except for the speed issue.
-------
Dale Smith, Quantitative Analyst, ADAM Investment Services
Any opinions expressed here are mine & not my employer's.
This email may be used under the "fair use" part of
the US copyright law and international treaties/conventions,
including normal email and Usenet routing. Repost/resend as
needed for any friendly, informal use. Any other use is
strictly reserved by my employer. Don't put it on the Web
without prior written permission.

For files/info available via auto-respond email, send a

the *subject* field.



Fri, 21 Aug 1998 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. MSHFlexGrid to Excel via Automation/OLE?

2. OLE Automation Contorl of Excel via Visual Basic

3. OLE Access Excel (Datenimport von Excel in Access - muss nicht OLE sein)

4. Setting Excel cell formula via Access VBA

5. Access 2.0 Report Objects via OLE Automation?

6. Access via OLE Automation

7. OLE error when accessing Excel cell that is in edit mode

8. VB 3.0 & Word 6 OLE Automation problems

9. Problems with OLE Automation in VB 3.0

10. Problem w/ OLE Automation/VB 3.0/Win95

11. Need help with OLE automation in VB 3.0

12. Access-Excel Interoperability: Switching between two Application windows via automation (VBA)

 

 
Powered by phpBB® Forum Software