Excel Automation and .NumberFormat 1

[gbettle]

I'm really going crazy after failing all day to debug this.

Has anyone successfully used the .NumberFormat of Excel's Range function during automation?

I don't know why but I can't ... code to follow:

oExcel = CREATEOBJECT( [Excel.Application])
IF VARTYPE( oExcel) != [O]
 * could not instantiate Excel object
 RETURN .F.
ENDIF

oExcel.SheetsInNewWorkbook = 1
oExcel.Workbooks.CLOSE
oExcel.Workbooks.ADD
oExcel.VISIBLE = .F.
* lcLinest = [12.5,13,12.5,12,13,12.5,13,12.5,12,12.5,13]
lcLinest = [12.5,13,14]
lnLinest = 0
IF !EMPTY( lcLinest)
       lnWordCount = GETWORDCOUNT( lcLinest, [,])
       IF lnWordCount >= 3
               lcClipText = [=linest({] + lcLinest + [})]
               oExcel.Range("A1").Select
               oExcel.Range("A1").value = lcCliptext
               oExcel.Range("A1").NumberFormat = '0.00000'
               lnLinest = oExcel.Range("A1").value
       ENDIF
ENDIF
oExcel.DisplayAlerts = .F.
oExcel.workbooks.CLOSE
oExcel.QUIT
RELEASE oExcel

? lnLinest

The last line prints 0.7500000000 - I would have expected that .NumberFormat = '0.00000' would make it print 0.75000 instead.

Cheers,

Garry

 

[baltman]

It works in Excel. Look at it! If you want it rounded to format in VFP use:

lnLinest = ROUND(oExcel.Range("A1").value, LEN(GETWORDNUM(oExcel.Range("A1").NumberFormat,2,".")))

Brian

 

[gbettle]

Hi Brian,

Thanks for your post!

My biggest problem was when the code ran on a machine with Excel 2007 installed. I was using VFP9's CAST with no problem to return CAST( lnLinest as Numeric( 10, 5)). Then I had a situation that was returning a very, very small number on the Excel 2007 machine.

The code would return the linest of

12.5,13,12.5,12,13,12.5,13,12.5,12,12.5,13

as

-1.66542711967825E-32

CAST doesn't work on a number that small. It returns:

CAST( -1.66542711967825E-32 as Numeric( 10, 5))

= -1.665E-32 (!)

Thanks again.

Cheers,

Garry

 

[Mike Lewis]

Garry,

CAST doesn't work on a number that small.

This is not a limitation of CAST().

The problem is that you are trying to display a floating-point number that required exponential notation in a field just ten characters wide. Obviously, there will only be room for ten characters (that is, "-1.665E-32").

If you increased the width to 18 characters:

CAST( -1.66542711967825E-32 as Numeric( 18, 5))

you would get this:

-1.6654271196E-32

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[gbettle]

Hi Mike,

Hope this email finds you well - roll on the weekend!

I just would have expected CAST( -1.66542711967825E-32 as Numeric( 10, 5)) to return 0.00000. I guess it can't expand the exponential notation.

So, if you have -1.66542711967825E-32, how do you convert that to 5 decimals? ( I think this may be a, "How long is a piece of string?" question ...)

Is there any other VFP command that fits the bill?

Cheers,

Garry

 

[gbettle]

I think I should just be using ROUND and get on with it!

Thanks Brian & Mike.

Cheers,

Garr