Sign in to follow this  
Followers 0
nitekram

Excel UDF

7 posts in this topic

#1 ·  Posted (edited)

I am having an issue - on the excel sheet (working in excel) the Formula works, but when I try to insert it via code no luck.

>Running: (3.2.1.12):C:\Program Files\AutoIt3\beta\autoit3.exe "F:\excel stuff\x_test.au3"

F:\excel stuff\excelcom_udf.au3 (407) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1 = $sFormula

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1 = $sFormula^ ERROR

>AutoIT3.exe ended.

>Exit code: 0 Time: 1.307

I have been able to get $formula2 to work so what gives?

#include "excelcom_udf.au3"
$forumla = "=VLOOKUP(B18,'X:\!All Customers\[Certificate_of_Liability_Insurance.xls]Sheet1'!$B$2:$V$500,5,TRUE)"
$forumla2 = "=SUM(B1:B16)"


If FileExists("F:\excel stuff\test.xls") Then FileDelete("F:\excel stuff\test.xls")


$oExcel = _ExcelBookOpen("F:\excel stuff\excel_test.xls",1,False) ; false - ready to write - true not able to write

_ExcelRowInsert($oExcel, 20,1)
If @error Then MsgBox("",'@error',@error)

_ExcelWriteCell($oExcel, "CERT", 20,1)
If @error Then MsgBox("",'@error',@error)

_ExcelWriteFormula($oExcel, $forumla, 20, 2)
 If @error Then MsgBox("",'@error',@error)

_excelBookSaveAs($oExcel, "F:\excel stuff\test.xls")
  If @error Then MsgBox("",'@error',@error)

_ExcelBookClose($oExcel,0) ; no save
  If @error Then MsgBox("",'@error',@error)


;ADDED THE FOLLOWING EDIT
#cs
; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")


$objWSC = ObjCreate("Demo.Scriptlet")

$MethodWSC_0 = $objWSC.Method1 = 0
$MethodWSC_1 = $objWSC.Method1 = 1

Msgbox(0,"Your Own COM object TEST", $MethodWSC_0)
Msgbox(0,"Your Own COM object TEST", $MethodWSC_1)

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

#ce
; need to see if this will show any more errors
Edited by nitekram

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites



*bump*


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

Ah, sorry - I'm slow to get to this one.

There's a slight issue with _ExcelWriteFormula() that might be causing your problem. It's something I'm meaning to get around to at some point.

When using A1 references in the formula you wish to write, you need to use the A1 reference scheme in the _ExcelWriteFormula() function.

This may solve the issue for you:

_ExcelWriteFormula($oExcel, $forumla, "B20")

If it does not, then you have a different problem that I will be unable to help you solve without more info. I can't run your example because in it you're referencing files I don't have.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Ah, sorry - I'm slow to get to this one.

There's a slight issue with _ExcelWriteFormula() that might be causing your problem. It's something I'm meaning to get around to at some point.

When using A1 references in the formula you wish to write, you need to use the A1 reference scheme in the _ExcelWriteFormula() function.

This may solve the issue for you:

_ExcelWriteFormula($oExcel, $forumla, "B20")

If it does not, then you have a different problem that I will be unable to help you solve without more info. I can't run your example because in it you're referencing files I don't have.

-S

I will try the cell reference on Monday - when I get back to work.

The cell in question just pulls a value from another excel file. Any file will do for the test - meaning the cell is not calculating any value it has its own value. It is a date - which means I will be formatting the cell later for the date to show correctly, but it all started with this formula. By the way - like I stated, it appears to be able to write the formula if you write to the cell - have you tried this?


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

By the way - like I stated, it appears to be able to write the formula if you write to the cell - have you tried this?

I'm afraid there were too many pronouns in the above quote. Have I tried what, exactly?

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

I'm afraid there were too many pronouns in the above quote. Have I tried what, exactly?

-S

Sorry about that. I guess when I reread what I wrote I got confused too. But I knew what I was talking about - lol.

I have been able to _ExcelWriteCell() the $formula to the cell that was used in my code - it worked - have you tried?

So rather than using _ExcelWriteFormula() to write the formula - I wrote my $formula to the cell with your _ExcelWriteCell() and it worked, it was able to calculate the formula.


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

Right, I get you now.

Excel was able to infer after the write that what you wrote was a formula, even though it was technically entered as a value. Most of the time Excel will infer a formula correctly when you enter it as a value. _ExcelWriteFormula() was introduced to force Excel to always consider the write string as a formula. When told to do this, you have the choice of writing it using R1C1 or A1 referencing. If you don't choose the one that matches the reference format of the formula you're writing, Excel will loudly exclaim blasphemous remarks about your mother. Well, my code doesn't yet take that into account.

_ExcelWriteFormula() attempts to infer which reference scheme you're using by how you've referenced the range in the formula parameters. If you used "A1" as your range, it'll presume you're using A1 referencing in your accompanying formula. If you use the row and column parameters instead (which you did in your script), then it'll presume your formula is R1C1 (which it isn't in your case).

It's a bug, and my intention is to fix it.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0