Error writing formular to excel cell

(0) Share

Report

Posted on by JanTheofel

Hello,

I try to write the formular

=COUNTIFS(Kontaktiert!A:A;A2)

to an excel cell using the "Write to Excel worksheet" function. (A2 will later become a dynamic value.)

But I get the following error message:

Microsoft.Flow.RPA.Desktop.Robin.SDK.ActionException: Failed to write into Excel. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC
--- End of inner exception stack trace ---
at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.Flow.RPA.Desktop.Modules.Excel.Actions.ExcelActions.SetProperty(Object o, String propertyName, Object value)
at Microsoft.Flow.RPA.Desktop.Modules.Excel.Actions.ExcelActions.WriteToExcel(Variant excelInstance, Variant startCol, Variant startRow, Variant valueToWrite, Int32 writeMode)
--- End of inner exception stack trace ---
at Microsoft.Flow.RPA.Desktop.Modules.Excel.Actions.WriteToExcel.Execute(ActionContext context)
at Microsoft.Flow.RPA.Desktop.Robin.Engine.Execution.ActionRunner.Run(IActionStatement statement, Dictionary`2 inputArguments, Dictionary`2 outputArguments)

I also tried to use the German function name (which I have to enter in Excel when creating formulars there). No difference.

Something very different like "=A2" works fine. Is seems to be the function I call. But why?

Do you have any ideas?

THANKS!
Jan

Categories:

Power Automate Desktop

All responses (9)

Answers (0)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hey Jan,

I think you want a "," after the column, not a ";"

This error is apparently caused by anything wrong in the formula itself. I was trying to replicate it, and Sheet2 was written by the code as "Sheet '2'" which gave me the same error.

Best of luck!
JanTheofel 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello,

thanks! Yes, it has to be something with the formular itself. In fact the same formular works absolutly fine when I enter it directly in excel in the same file and field: (With the german function name here.)

I guess blanks and other special characters in the sheet name might cause serious trouble. This is why I removed all of them.

Jan
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Wait, so the ";" is legit over there to use between parameters?

If that is the case, write the Excel formula to a text file so we can see what it looks like.
JanTheofel 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Michael,

thanks so much for your help. Yes looks like this is language dependent.

This is the value for my vairable %ExcelFormular% right now: (With the , instead of 😉
=SUMMEIFS(Kontaktiert!A:A,A2)

Now I do not get an PAD error. Excel converts this to: (Now with the ; instead of the , )
=@SUMMEIFS(Kontaktiert!A:A;A2)

So obviously PAD needs the , instead of the ; altough Excel replaces it afterwards...

But instead of a value I get the #NAME? in the field as a result.
One fixed, one to go.

After that I tried the german function name with , instead of ;

Input in PAD:
=ZÄHLENWENNS(Kontaktiert!A:A,A2)

Excel makes this:
=@ZÄHLENWENNS(Kontaktiert!A:A;A2)

The same #NAME? error again.

And now for the final strange thing:
I removed the @ at the begin of the formular in Excel. That work's!
I added the @ again - and it still works.

Now I got rid of the PAD error but still have this Excel issue which is totaly strange...

Jan
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I have only seen the "@" recently, and I don't like whatever it is doing. I also have only seen it in csv files; when I converted the file to xlsx, this issue went away. I hope you are able to find a solution that works. I would just try the English formula =COUNTIFS with a "," and see if that works. Good luck!
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @JanTheofel

The comma or the semicolon for formulas depends on the List separator character specified in the Windows regional settings.
https://www.excel-exercise.com/comma-or-semicolon-in-excel-formula/

I had a hard time figuring out when Excel was no longer accepting comma :).

Hope you have no issues now and got it all sorted out.
JanTheofel 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello!

Yes that separator issue is solved and is country dependent. But Excel still adds an @ befor the COUNTIFS which still does not create the planned result.

Jan
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Didn't find @ getting added anywhere in the formula

Also tried using a variable instead of direct formula in "Value to Write" but worked fine.

Would be so kind to share some screenshots of what is the input, what is happening in the output and the PAD flow actions?
It looks to be some setting changes in your Excel or Regional settings or in the input data.
ThomasFonn 273 on at

Like
a
(1)

Report
Copy link

Link copied!

The "@" symbol gets added to formulas after the "=" when you write formulas in your local language (that isn't english) in PAD and using the "Write to Excel Worksheet"
However, using the american-english function names (with commas instead of semicolons as argument separators)
works just fine