P: n/a

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  
Share this Question
P: n/a

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 ReplyTo: ca*****@trencor.net MessageID: <va********************************@4ax.com> XNewsreader: Forte Agent 1.93/32.576 English (American) MIMEVersion: 1.0 ContentType: text/plain; charset=usascii ContentTransferEncoding: 7bit Newsgroups: microsoft.public.dotnet.languages.vb NNTPPostingHost: 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 XTomcatNG: 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
 
P: n/a

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 ReplyTo: ca*****@trencor.net MessageID: <va********************************@4ax.com> XNewsreader: Forte Agent 1.93/32.576 English (American) MIMEVersion: 1.0 ContentType: text/plain; charset=usascii ContentTransferEncoding: 7bit Newsgroups: microsoft.public.dotnet.languages.vb NNTPPostingHost: 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 XTomcatNG: 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
 
P: n/a

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 ReplyTo: ca*****@trencor.net MessageID: <o8********************************@4ax.com> References: <va********************************@4ax.com>
<pR*************@cpmsftngxa06.phx.gbl>XNewsreader: Forte Agent 1.93/32.576 English (American) MIMEVersion: 1.0 ContentType: text/plain; charset=usascii ContentTransferEncoding: 7bit Newsgroups: microsoft.public.dotnet.languages.vb NNTPPostingHost: 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 XTomcatNG: 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 ReplyTo: ca*****@trencor.net MessageID: <va********************************@4ax.com> XNewsreader: Forte Agent 1.93/32.576 English (American) MIMEVersion: 1.0 ContentType: text/plain; charset=usascii ContentTransferEncoding: 7bit Newsgroups: microsoft.public.dotnet.languages.vb NNTPPostingHost: 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 XTomcatNG: 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
  This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 2279
 replies: 3
 date asked: Nov 20 '05
