Upgrading App from VFP6 to 9 - Excel OLE Automation... 1

[GriffMG]

I just identified a problem with an application I've recently upgraded from VFP6 to VFP9.

The application does a little OLE Automation with Excel, which reads the contents of an existing spreadsheet (uploaded via a website) looking for anything entered
in a cell.

In VFP6 I was taking the cell value and dropping it into a variable - then testing to see if the variable was empty:

m.TEMPVAL = OEXCEL.CELLS(1,9).VALUE
IF !EMPTY(m.TEMPVAL) 
... do something
ELSE
... do something else
ENDIF

And it has worked for years... where there is something in the cell it isn't empty and does one thing, where it has nothing in it - it does the other thing, because the EMPTY()
test fails.

but after the upgrade, the temporary variable contains a .Null. value, which is interpreted differently... so I have had to change the code to look like this:

m.TEMPVAL = OEXCEL.CELLS(1,9).VALUE
IF !EMPTY(m.TEMPVAL) .AND. !ISNULL(M.TEMPVAL)
... do something
ELSE
... do something else
ENDIF

Testing for the .Null. value explicitly seems to get around the problem.

My thinking is that there is a 'SET' which is different by default, but I don't see one Set("NULL") returns OFF in both cases - does anyone have any ideas?

Martin



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[JRB-Bldr]

I don't know if this issue changed between VFP6 and VFP9, but regardless, look into your Help file for SET NULL

Good Luck,
JRB-Bldr

 

[GriffMG]

Hi JRB-Bldr

My thinking is that there is a 'SET' which is different by default, but I don't see one Set("NULL") returns OFF in both cases - does anyone have any ideas?

I had looked at that and it is the same in both versions.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Martin,

I'm fairly sure this isn't a VFP issue, and there's no setting in VFP that will affect it.

It's much more likely to be an Excel version issue. Is it possible that you (or your users) have changed to a new Excel about the same time you upgraded to VFP 9.0?

I no longer have VFP 6.0, but I have just tested it in both versions 7.0, 8.0 and 9.0, and I'm seeing the same results in every case.

JRB-Bldr,

SET NULL only affects SQL commands. It's got nothing to do with Automation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

Interesting Mike, I'll 'double test' it with VFP 6 and 9 on the same PC with the same data and the same version of Excel 2000.

I could be mistaken!


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[GriffMG]

Just for reference... I ran the code below on the same PC, with the same data and the same spreadsheet, using VFP9 and VFP6

				OEXCEL = CREATEOBJECT("Excel.Application")
				OEXCEL.VISIBLE = .T.
				OEXCEL.WORKBOOKS.OPEN("c:\$incoming\urs.xls")
				OEXCEL.RANGE("D9").SELECT
				m.TEMPVAL = OEXCEL.selection.value
				if empty(m.tempval)
					messagebox("empty")
				else
					messagebox("not empty")
				Endif

VFP6 yields 'empty'
VFP9 yields 'not empty'


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Which version of Excel did you use?

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

This is Excel 2000.

I have later versions I can test it on...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[GriffMG]

Same result in Office 2003

I will try tomorrow in Office 2008

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Martin, I'm seeing the same. I was probably wrong when I said it wasn't a VFP issue. Looks like a change in behaviour between versions 6.0 and 7.0.

Maybe you should write a function called IsNullOrEmpty(), and use that to test the cell.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

Yes, that would make sense.

Odd behaviour change.

Fortunately I don't have nay apps that READ an Excel file in this way (a couple of old VB ones perhaps) that I haven't now converted. I only noticed the problem yesterday when I was training people in an online system - and wasn't able to demo this bit! I only converted the apps a few months ago, no one else has noticed.

Make you wonder if there is anything else though!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

You can use a check of EMPTY(NVL(Cellvalue,"")), in fact NVL() is a good way of handling Null values and changing them to some deault value of the correct type needed.

Bye, Olaf.