473,287 Members | 1,663 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Excel Com automation - Formulas

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

Nov 20 '05 #1
3 2768
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


Nov 20 '05 #2
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


Nov 20 '05 #3
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



Nov 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Guy Incognito | last post by:
Hello, I've written an asp.net application that creates Excel documents. It works by creating an excel document in XML format. But I wonder if I'm reinventing the wheel. I know that there are...
1
by: Sarah | last post by:
Hello. I am using Microsoft.Office.Interop.Excel in a C# .NET project. I want to open an Excel application with a specific file name. I am currently opening it with this code: ...
1
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed...
1
by: barma16 | last post by:
I've hit a bit of a brick wall here, and could use some advice. I have an Access application whose output is a four-tab Excel spreadsheet where three of the four tabs are the result of database...
3
by: ggupta78 | last post by:
Hi, Currently in our application we use the MHTML way of exporting to Excel i.e we render the datagrid HTML and set the MIME type to Excel. This works great but has the following limitation: 1....
3
by: McKirahan | last post by:
I am using ASP to generate an HTML table from a database. I use FSO to output a file with an XLS extension then open it. I can set the column width and row height; I can set the font family,...
2
by: rene.marxis | last post by:
Hi I need some hints for the following problem. We have some customer that has one Excel file composed of about 20 sheets and he wants to convert this file to a small C# application. It's not...
8
by: Mel | last post by:
Can anyone tell me why I am getting this error? It bombs on this line of my VB code: Public appExcel As New Excel.Application It works when I test it on my machine but once I release the page...
0
by: Belsirk | last post by:
Hi, i'm using C# Visual Studio 2008 for made a apps able to take some information from the app and sending them to a excel template, i'm using Microsoft.Office.Interop.Excel namespace and i don't...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.