Excel Com Automation

  • Thread starter Thread starter Carlos Magalhaes
  • Start date Start date
C

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
 
Hi,

Use Saved property of workbook. Set it TRUE before closing the workbook.

--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Carlos Magalhaes" <[email protected]>, iletide sunu yazdi 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
 
Yeah I was HOPING that was not the only option, I really dont want to
write anything to that file I just want to apply the formula get the
data and close the file.

Not possible ??
 
Depends on the formula, but

Results = xlApp.Evaluate(StrFormula)

should work.

The formula must be specific in terms of the ranges it addresses.

Many Worksheetfunctions can be used in VBA directly.


Results=xlapp.Sum(Worksheets("Sheet1").Range("A1:A200"))
 
Finally the answer i was lookign for :-) thank you.

You said depends on the formula, what do I have to watch out for for?
i.e. when will the Evaluate function not run a strFormula i am passing
to it.

At the moment all my formulas have the cell and range ref in them.

Thank you once again

Carlos
 
I can't say there are any specific formulas it will not process - the best
would be to just test it. A formula could be

Sum(A1:A10)

That would default to the activesheet in evaluate which may or may not be
what you intended. I was thinking of things such as this that might be a
stumbling block.

Also, I don't think I have ever tested it with Analysis toolpak functions or
UDF's.
 
Something interesting:

Using the evaluate function on the worksheet, i pass it a string which
is the actual formula:

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

Now the interesting thing is that i am trying to determine the result.
the result can either be ONLY true or FALSE and i am doing this
Where formula = Dim formula as string =
"=IF(J17<SUM(G17:G25),TRUE,IF(ISBLANK(G26:G37),TRUE,FALSE))"

Dim Results As Boolean
Results = objSheeti.Evaluate(formula)

Now when the values in the excel spread sheet are correct and the
formula does in actual fact return a true value Results =
objSheeti.Evaluate(formula) works but as soon as the formula evaluates
to FALSE (i.e. it SHOULD be returning a false) i get an Array of
values and not just FALSE.... like when the value is right i just get
true:

i get:

- 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


Why is it returning this and not just FALSE? Is the formula wrong?
What am i doing wrong , or what is the reason for it returning this
value?

Thank you very much

CM
 
the problem is the isblank(G26:G37)

this returns an array of booleans

Put
=ISBLANK(G26:G37)

in a cell,

then select the IsBlank(G26:G37) and hit F9 and you will see it returns an
array (although the cell will display false since only the first value is
displayed).

You can you

CountBlank

or CountA instead of isblank.

These will return a single value though not boolean.


If(countblank(G26:B37)=12,True,False)

Might do what you want.
 
Back
Top