Excel Com automation - Formulas

[Carlos Magalhaes]

Hey All,

I am doing some excel automation using the excel COM. I can do most of
the functions and its working well until I come across a formula.

I can run a formula and insert the formula value into a cell. BUT this
is where it comes "complex".

I am retrieving formulas as strings from a database; I then want to
run the formula against a specific spreadsheet and return the value to
my app.

What I am currently seeing is that I have to run a formula set the
value to a cell on the spread sheet then call that value back to the
app. Is there no way of just running the formula against the
spreadsheet and returning the value?

I would appreciate the answer, I have searched high and low but all
the example only show how to run a formula and set the value to a cell
then read the value. i.e.

ObjExcel.WorkSheet.Cell(1,1).formula = StrFormula

Dim srtvalue as string = ObjExcel.WorkSheet.Cell(1,1).Value

But that forces a write into the spread sheet and when you close it,
it asks to save etc, I simply just want to run the formula to return
the outcome of the formula.

I know you can choose to save the file but I would like to know if
there is a way around having to apply the formula to a cell or range
then reading it back from that cell or range. Just appy formula to
data in the Workbook and return the value.

Thank you All

Carlos

[Peter Huang]

Hi Carlos,

Based on my understanding, you want to call an excel function by its name,
and get its return value from VB.NET.

Here I write a simple sample for you.
Imports Microsoft.Office.Interop
Module Module1
Sub Main()
Dim args(2) As Object
Dim obj As Excel.Application
args(0) = 0.5
args(1) = 15
obj = CreateObject("Excel.Application")
Console.WriteLine(CallByName(obj.Application, "Sum",
CallType.Method, args))
obj.Quit()
obj = Nothing
End Sub
End Module

If I misunderstand your meaning, please post here.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

From: Carlos Magalhaes <ca*****@trencor.net>
Subject: Excel Com automation - Formulas
Date: Fri, 07 Nov 2003 19:26:45 +0200
Organization: http://groups.yahoo.com/group/ADSIANDDirectoryServices
Reply-To: ca*****@trencor.net
Message-ID: <va********************************@4ax.com>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: mail.trencor.net 195.75.154.163
Lines: 1
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:154710
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

Hey All,

I am doing some excel automation using the excel COM. I can do most of
the functions and its working well until I come across a formula.

I can run a formula and insert the formula value into a cell. BUT this
is where it comes "complex".

I am retrieving formulas as strings from a database; I then want to
run the formula against a specific spreadsheet and return the value to
my app.

What I am currently seeing is that I have to run a formula set the
value to a cell on the spread sheet then call that value back to the
app. Is there no way of just running the formula against the
spreadsheet and returning the value?

I would appreciate the answer, I have searched high and low but all
the example only show how to run a formula and set the value to a cell
then read the value. i.e.

ObjExcel.WorkSheet.Cell(1,1).formula = StrFormula

Dim srtvalue as string = ObjExcel.WorkSheet.Cell(1,1).Value

But that forces a write into the spread sheet and when you close it,
it asks to save etc, I simply just want to run the formula to return
the outcome of the formula.

I know you can choose to save the file but I would like to know if
there is a way around having to apply the formula to a cell or range
then reading it back from that cell or range. Just appy formula to
data in the Workbook and return the value.

Thank you All

Carlos

[Carlos Magalhaes]

Hey there Peter,

Thats ALMOST what i needed, the formula does not have a name its a
formula that we have created for example:

=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRUE ,FALSE))

I have tried to use the EVALUATE function i.e.
Dim strFormula =
"=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRU E,FALSE))"
objWorkSheet.Evaluate(strFORMULA) function to evaluate a specific
formula on a worksheet and return the value

Based on that formula, it should either return a TRUE value or FALSE
value, now i do:

Dim Mybol as Boolean = objWorkSheet.Evaluate(strFORMULA)

When the value in the workbook will make the formula = true then i get
the right value back from objWorkSheet.Evaluate(strFORMULA) which is a
true value ,BUT when i change the cells to make the value/formula
equate/return false the return value from
objWorkSheet.Evaluate(strFORMULA) is wierd:

- objSheeti.Evaluate(formula) {System.Array} Object
+ (1,1) False {Boolean} Boolean
FalseString "False" String
TrueString "True" String
- (2,1) False {Boolean} Boolean
FalseString "False" String
TrueString "True" String

I am not understanding what its doing, i thought if it was false it
would return me a FALSE value not that Array of values, when the
formula returns true it does not return an array of values???

Anyone know why its doing that and how to fix it.

On Mon, 10 Nov 2003 05:38:57 GMT, v-******@online.microsoft.com (Peter
Huang) wrote:

Hi Carlos,

Based on my understanding, you want to call an excel function by its name,
and get its return value from VB.NET.

Here I write a simple sample for you.
Imports Microsoft.Office.Interop
Module Module1
Sub Main()
Dim args(2) As Object
Dim obj As Excel.Application
args(0) = 0.5
args(1) = 15
obj = CreateObject("Excel.Application")
Console.WriteLine(CallByName(obj.Application, "Sum",
CallType.Method, args))
obj.Quit()
obj = Nothing
End Sub
End Module

If I misunderstand your meaning, please post here.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

From: Carlos Magalhaes <ca*****@trencor.net>
Subject: Excel Com automation - Formulas
Date: Fri, 07 Nov 2003 19:26:45 +0200
Organization: http://groups.yahoo.com/group/ADSIANDDirectoryServices
Reply-To: ca*****@trencor.net
Message-ID: <va********************************@4ax.com>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: mail.trencor.net 195.75.154.163
Lines: 1
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:154710
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

Hey All,

I am doing some excel automation using the excel COM. I can do most of
the functions and its working well until I come across a formula.

I can run a formula and insert the formula value into a cell. BUT this
is where it comes "complex".

I am retrieving formulas as strings from a database; I then want to
run the formula against a specific spreadsheet and return the value to
my app.

What I am currently seeing is that I have to run a formula set the
value to a cell on the spread sheet then call that value back to the
app. Is there no way of just running the formula against the
spreadsheet and returning the value?

I would appreciate the answer, I have searched high and low but all
the example only show how to run a formula and set the value to a cell
then read the value. i.e.

ObjExcel.WorkSheet.Cell(1,1).formula = StrFormula

Dim srtvalue as string = ObjExcel.WorkSheet.Cell(1,1).Value

But that forces a write into the spread sheet and when you close it,
it asks to save etc, I simply just want to run the formula to return
the outcome of the formula.

I know you can choose to save the file but I would like to know if
there is a way around having to apply the formula to a cell or range
then reading it back from that cell or range. Just appy formula to
data in the Workbook and return the value.

Thank you All

Carlos

[Peter Huang]

Hi Carlos,

=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRU E,FALSE)) The Excel help said
Function Returns TRUE if
ISBLANK Value refers to an empty cell.
That is to say ,the ISBLANK will deal with an cell a time, ISBLANK(G26:G37)
will return an array, each element for each cell.
You may verify it by call the similar code in VBA of Excel.

I think to you may try to deal with the returned array yourself, since in
the range G26:G37, perhaps some are blank, some are not.

Dim obj As Excel.Application
Dim strFormula As String
strFormula =
"=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G28),TRU E,FALSE))"
obj = CreateObject("Excel.Application")
obj.Visible = True
obj.Workbooks.Add()
Dim rt As Object
rt = obj.Evaluate(CType(strFormula, Object))
For Each e As Boolean In rt
Console.WriteLine(e.ToString())
Next
obj.Quit()
obj = Nothing

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------From: Carlos Magalhaes <ca*****@trencor.net>
Subject: Re: Excel Com automation - Formulas
Date: Mon, 10 Nov 2003 13:09:38 +0200
Organization: http://groups.yahoo.com/group/ADSIANDDirectoryServices
Reply-To: ca*****@trencor.net
Message-ID: <o8********************************@4ax.com>
References: <va********************************@4ax.com> <pR*************@cpmsftngxa06.phx.gbl>X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: mail.trencor.net 195.75.154.163
Lines: 1
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftn gp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:155445
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

Hey there Peter,

Thats ALMOST what i needed, the formula does not have a name its a
formula that we have created for example:

=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRU E,FALSE))

I have tried to use the EVALUATE function i.e.
Dim strFormula =
"=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TR UE,FALSE))"
objWorkSheet.Evaluate(strFORMULA) function to evaluate a specific
formula on a worksheet and return the value

Based on that formula, it should either return a TRUE value or FALSE
value, now i do:

Dim Mybol as Boolean = objWorkSheet.Evaluate(strFORMULA)

When the value in the workbook will make the formula = true then i get
the right value back from objWorkSheet.Evaluate(strFORMULA) which is a
true value ,BUT when i change the cells to make the value/formula
equate/return false the return value from
objWorkSheet.Evaluate(strFORMULA) is wierd:

- objSheeti.Evaluate(formula) {System.Array} Object
+ (1,1) False {Boolean} Boolean
FalseString "False" String
TrueString "True" String
- (2,1) False {Boolean} Boolean
FalseString "False" String
TrueString "True" String

I am not understanding what its doing, i thought if it was false it
would return me a FALSE value not that Array of values, when the
formula returns true it does not return an array of values???

Anyone know why its doing that and how to fix it.

On Mon, 10 Nov 2003 05:38:57 GMT, v-******@online.microsoft.com (Peter
Huang) wrote:

Hi Carlos,

Based on my understanding, you want to call an excel function by its name,and get its return value from VB.NET.

Here I write a simple sample for you.
Imports Microsoft.Office.Interop
Module Module1
Sub Main()
Dim args(2) As Object
Dim obj As Excel.Application
args(0) = 0.5
args(1) = 15
obj = CreateObject("Excel.Application")
Console.WriteLine(CallByName(obj.Application, "Sum",
CallType.Method, args))
obj.Quit()
obj = Nothing
End Sub
End Module

If I misunderstand your meaning, please post here.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.--------------------

From: Carlos Magalhaes <ca*****@trencor.net>
Subject: Excel Com automation - Formulas
Date: Fri, 07 Nov 2003 19:26:45 +0200
Organization: http://groups.yahoo.com/group/ADSIANDDirectoryServices
Reply-To: ca*****@trencor.net
Message-ID: <va********************************@4ax.com>
X-Newsreader: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: mail.trencor.net 195.75.154.163
Lines: 1
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:154710
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

Hey All,

I am doing some excel automation using the excel COM. I can do most of
the functions and its working well until I come across a formula.

I can run a formula and insert the formula value into a cell. BUT this
is where it comes "complex".

I am retrieving formulas as strings from a database; I then want to
run the formula against a specific spreadsheet and return the value to
my app.

What I am currently seeing is that I have to run a formula set the
value to a cell on the spread sheet then call that value back to the
app. Is there no way of just running the formula against the
spreadsheet and returning the value?

I would appreciate the answer, I have searched high and low but all
the example only show how to run a formula and set the value to a cell
then read the value. i.e.

ObjExcel.WorkSheet.Cell(1,1).formula = StrFormula

Dim srtvalue as string = ObjExcel.WorkSheet.Cell(1,1).Value

But that forces a write into the spread sheet and when you close it,
it asks to save etc, I simply just want to run the formula to return
the outcome of the formula.

I know you can choose to save the file but I would like to know if
there is a way around having to apply the formula to a cell or range
then reading it back from that cell or range. Just appy formula to
data in the Workbook and return the value.

Thank you All

Carlos