ExcelCOM UDF

[randallc]

@Blitzkrg

$colorme = _XLSetCellColor($app1,$asheet,"A"&$ini1&":AM"&$ini1,$color,1)

Sorry, have had to correct syntax. ? Now works

Best, Randall

@Dickb

Thanks; added and posted; please check OK

Best, Randall

[blitzkrg]

@Blitzkrg

Sorry, have had to correct syntax. ? Now works

Best, Randall

@Dickb

Thanks; added and posted; please check OK

Best, Randall

I just got around to trying to your 1st post (in reply to my post yesterday)

and it worked perfectly..

what changed since then?

it looks the same to me.

[randallc]

Sorry again!

You caught me just while changing; it should have a $row parameter in all these functions in case you want to do a single cell; yours probably won't work without that now; try again!

best, Randall

[blitzkrg]

Sorry again!

You caught me just while changing; it should have a $row parameter in all these functions in case you want to do a single cell; yours probably won't work without that now; try again!

best, Randall

Yes adding the row field did the trick for me..

Thanks!

[Dickb]

@Blitzkrg

Sorry, have had to correct syntax. ? Now works

Best, Randall

@Dickb

Thanks; added and posted; please check OK

Best, Randall

@Randallc

I've tested it (more or less) and it looks ok. Thanks for adding this.

Regards, Dick

[Simucal]

I'm happy with all the activity with the development of excelcom udf lately!

I have a couple more functions I'll post tonight.

[blitzkrg]

I'm getting an error with one of my scripts

Line 274 (file blah blah\excelcom.au3)

.activesheet.range($Range).select

.activesheet.range($Range)^ERROR

Error: The rested action with this object has failed..

i have not had time to look into it, any ideas of the top of your head?

thanks

Edited May 2, 2006 by blitzkrg

[Simucal]

It would be nice to know what function you were calling, with what text, parameters, etc.

[blitzkrg]

It would be nice to know what function you were calling, with what text, parameters, etc.

edit - i figured it out.

some how my ini file got deleted, so when i queried for the "row" (last used) it returned a null value (since it didnt exist)

and that hosed the script..

thanks for everyones work on this.. i'm glad it's moving forward as well..

Edited May 2, 2006 by blitzkrg

[blitzkrg]

Guys - I'm dealing with some new spreadsheets that have embedded controls in them

for example check boxes

CheckBox1, 2,3,4,5, etc etc etc.

is it possible to read the values of those check boxes?

thanks

[randallc]

Hi,

I don't know your answer; so far, I have been able to convert any Excel macro; can you show me how an Excel macro does this (ie example macro script?)?

An interim solution is always just to use AutoIt to run your excel macro, of course!

Thanks, Randall

[Simucal]

Some further work done to the ExcelCOM UDF.

Here are the changes:

New Functions added all together:

_XLDeleteRow

_XLDeleteColumn

Functions renamed:

_SheetAdd to _XLSheetAdd

_GetSheetName to _XLGetSheetName

_NameSheet to _XLNameSheet

General Header changes:

$XLCopyRange=_XLCopy changed to _XLCopy

$WorkbookPropArray=_XLSheetProps changed to _XLSheetProps

$s_XLArrayRange=_XLArrayWrite changed to _XLArrayWrite

$XLArray=_XLArrayRead changed to _XLArrayRead

Functions added to header that were missing:

_XLSetHorizontalAlign

_XLSetFontType

_XLSetBorders

Misc Changes:

_XLSetCellColor's variable name was $i_FontColor. Changed to $i_CellColor

_XLSetColumnWidth no longer requires a $i_row

Author(s) line updated to show myself and DickB.

Functions listed in header have been alphabatized.

@Randall, Let me know what you think.

-Simucal

ExcelCom.au3

Edited May 12, 2006 by Simucal

[randallc]

Hi,

Thanks again;

I'll have a try.

Best, Randall

[randallc]

Hi @SimuCal

All looks good ; Thanks for the tidying too!

[still some funcs not in Header...time..later]

I have left old names for changed funcs for back-compatabilty in addition; no other change;

Posted on post 1 of the thread.

Thanks again, Randall

_XLDeleteRow.au3

[Simucal]

Hi @SimuCal

All looks good ; Thanks for the tidying too!

[still some funcs not in Header...time..later]

I have left old names for changed funcs for back-compatabilty in addition; no other change;

Posted on post 1 of the thread.

Thanks again, Randall

No problem.. I use excelcom enough that I feel an obligation to contribute in some way.

[seandisanti]

Guys - I'm dealing with some new spreadsheets that have embedded controls in them

for example check boxes

CheckBox1, 2,3,4,5, etc etc etc.

is it possible to read the values of those check boxes?

thanks

kind of late on responding to this one, but yes, you can read checkboxes like so: (sorry my example doesn't use excelcom.au3)

$oEX = ObjCreate("excel.application")
$oWB = $oEX.workbooks.open(@ScriptDir & "\checkbox.xls")
$oEX.visible = True
$oSH = $oWB.sheets("Sheet1")
MsgBox(0,"Checkbox","The checkbox :" & @CRLF & $oSH.Checkbox1.Caption & @CRLF & "value is : " & $oSH.Checkbox1.Value)

i'll attach the spreadsheet the script uses. there are a couple of things to keep in mind to avoid frustration...

if the box is checked, Value will return -1, otherwise it will return 0. if you have a checkbox with a triplestate, (they can use the greyed out check for N/A or whatever) in VBA, the value for the grey check is Null, but through com it returns -1. so you cannot tell the difference programatically (unless you think of something i haven't, which is very possible) between an uchecked checkbox with a triplestate, and a grayed checkbox with a triplestate. the form that i was trying to automate was a call monitoring form, where some elements were not required, and if the box was greyed, it didn't affect the possible score because it was treated as an N/A. I tried using triplestate and value since it was returning -1 for grey or regular check, but with the 3 states of a triple state, the returns are:

Checked:

-1 Value

-1 Triplestate

Unchecked:

0 Value

-1 Triplestate

Grey Checked

0 Value

-1 Triplestate

for my situation i ended up doing most of the automation with vba this time.

[blitzkrg]

Thanks for this..

Can you post a way that will check an existing (already open) file.

I'm having problem when the script opens it, due to macro security.

I'd like to run it while the page is already open.

thanks

kind of late on responding to this one, but yes, you can read checkboxes like so: (sorry my example doesn't use excelcom.au3)

$oEX = ObjCreate("excel.application")
$oWB = $oEX.workbooks.open(@ScriptDir & "\checkbox.xls")
$oEX.visible = True
$oSH = $oWB.sheets("Sheet1")
MsgBox(0,"Checkbox","The checkbox :" & @CRLF & $oSH.Checkbox1.Caption & @CRLF & "value is : " & $oSH.Checkbox1.Value)

i'll attach the spreadsheet the script uses. there are a couple of things to keep in mind to avoid frustration...

if the box is checked, Value will return -1, otherwise it will return 0. if you have a checkbox with a triplestate, (they can use the greyed out check for N/A or whatever) in VBA, the value for the grey check is Null, but through com it returns -1. so you cannot tell the difference programatically (unless you think of something i haven't, which is very possible) between an uchecked checkbox with a triplestate, and a grayed checkbox with a triplestate. the form that i was trying to automate was a call monitoring form, where some elements were not required, and if the box was greyed, it didn't affect the possible score because it was treated as an N/A. I tried using triplestate and value since it was returning -1 for grey or regular check, but with the 3 states of a triple state, the returns are:

Checked:

-1 Value

-1 Triplestate

Unchecked:

0 Value

-1 Triplestate

Grey Checked

0 Value

-1 Triplestate

for my situation i ended up doing most of the automation with vba this time.

[seandisanti]

Thanks for this..

Can you post a way that will check an existing (already open) file.

I'm having problem when the script opens it, due to macro security.

I'd like to run it while the page is already open.

thanks

sure thing, it will just take me a couple of minutes to write it up. one other option you may consider (although i only mention it to be thorough, and strongly suggest against it) is to set the macro security level programmatically. yes it can be done, i actually stumbled on it in the VBA help file looking for something else a while back, and tried out their sample code which allowed me to set the macro security level. i set it to highest in excel, then closed excel, and set it to lowest via autoit. i won't display the code to do that here because of the potential for misuse (by others, not you), but it is an option you can find out more about in your help file.

now for your issue....

if ProcessExists("excel.exe") then 
    $myex = ObjGet("","excel.application")
    If Not IsObj($myex) Then 
        MsgBox(0,"error","attempt to sieze excel object failed.  exiting")
        Exit
    EndIf
EndIf
$mywb = $myex.workbooks("Workbookname")
$mysheet = $mywb.sheets("Sheetname")
MsgBox(0,"Demo","the value of checkbox1 is " & $mysheet.checkbox1.value)
#cs
you don't have to have objects for every level of the model.  i could have done this all with less typing, but it
may not have made as much sense.  the code above is the same as: (not doing error checking on this one either, which
cuts more code off)

$myex = ObjGet("","excel.application")
msgbox(0,"Demo,"the value of checkbox1 is: " & $myex.workbooks("WorkbookName").sheets("SheetName").Checkbox1.value)

#ce

[blitzkrg]

Thanks that will work for me i think.

It seems as though, i have to "enable macros" and then cancel when it ask it's first question (it's a form based spreadsheet)

If i disable macros from the start, i get an error in the script

soo as long as i follow that process it seems to work for me.

Thanks again!

edit: well damn.. seems that the tripplestate thing is screwing me...

hmm.. well mabye someone will come up with a way to detect the tripplestate

Edited June 5, 2006 by blitzkrg

[seandisanti]

Thanks that will work for me i think.

It seems as though, i have to "enable macros" and then cancel when it ask it's first question (it's a form based spreadsheet)

If i disable macros from the start, i get an error in the script

soo as long as i follow that process it seems to work for me.

Thanks again!

edit: well damn.. seems that the tripplestate thing is screwing me...

hmm.. well mabye someone will come up with a way to detect the tripplestate

there really isn't a way around it, because of the way the returns are re-formatted for com. in vba, grey check returns NULL, black check returns TRUE, no check returns FALSE. i ended up just using VBA to do my forms, as much as i hated to do that. if you want to pm me the details or show me the sheet you're working with, i'll be happy to help with a vba solution.