Yet Another -- ExcelCOM UDF

[friends]

hi all. I'm new to EXCELCOM stuffs, but i wanted to try it out...

I already downloaded the file(s).... but i wonder if the UDF has the the help feature in SCiTE 4 AutoIT ?

Like for AUTOIT3 functions, when we type "MsgBox (", SCiTE will show the TIPS of of the MsgBox.

Does SCiTE support for EXCELCOM UDF ?

Pls help... cos i need to know that. Thanks

[Locodarwin]

I have not submitted this collection of functions to be included with AutoIt yet. No help file or code completion has been published for it. However, if you open the UDF file and look through the comment headers for each function, you'll find plenty of information on how to use them.

-S

[livewire]

This needs to be included with AutoIt. I noticed Word is in the latest betas. This ExcelCOM UDF needs to be made stable and submitted.

-Livewire

[DaleHohm]

This needs to be included with AutoIt. I noticed Word is in the latest betas. This ExcelCOM UDF needs to be made stable and submitted.

-Livewire

Well how nice of you to offer to write the documentation for all of the routines and ready it for submission Livewire! Er, that IS what you meant right? Because if it isn't, that sounded a lot like a *demand* you were making of a volunteer that has already freely given more of his time thatn you could ever know...

Dale

[Leykis101]

It's key first then range.

$errorret = _ExcelSortExtended($oExcel2, "A1", "A1:Z999", 1, $xlYes, False, $xlSortRows, $xlSortNormal)

Still doesn't do squat for me

[livewire]

Well how nice of you to offer to write the documentation for all of the routines and ready it for submission Livewire! Er, that IS what you meant right? Because if it isn't, that sounded a lot like a *demand* you were making of a volunteer that has already freely given more of his time thatn you could ever know...

Dale

I do realize a lot of work was put into this, that is why I feel it is worthy of being included. I really have no idea what it takes to get this included into Autoit. I assume it takes someone writing up html or creating a script to do it. I am willing to help, but I have no experience writing html in compiled files like that. I could help with testing this UDF and making it stable and write a Scite .api file.

My post was not supposed to come off like a demand...more of an opinion. Probably a bad choice of words.

-Livewire

Edited May 14, 2007 by livewire

[Locodarwin]

Livewire,

I appreciate your strong desire to see this collection become part of AutoIt's standard UDF catalog. As Dale can attest to, it's no easy feat, especially when your collection has over 60 functions and counting. There's a lot of work left to do, mostly in documentation. Since taking on a new job in December, I've had less "fun" time to work on this UDF. You know, the usual sob story.

I took your comments not as a demand but as an urgent request. I thought that it might have the opposite effect on me than intended, but the truth is, the demonstration of urgent need I've seen in you and in the large number of private messages I've received asking for support have rekindled my spirit toward the project somewhat.

I'll see what I can do.

In the meantime, if you're serious about working on a SciTE .api file for this collection, by all means take the lead. I know a lot of people who could use that functionality immediately. I would be happy to collaborate with anyone on this project.

-S

[DaleHohm]

I do realize a lot of work was put into this, that is why I feel it is worthy of being included. I really have no idea what it takes to get this included into Autoit. I assume it takes someone writing up html or creating a script to do it. I am willing to help, but I have no experience writing html in compiled files like that. I could help with testing this UDF and making it stable and write a Scite .api file.

My post was not supposed to come off like a demand...more of an opinion. Probably a bad choice of words.

-Livewire

Good, I'm glad. It is a lot of work to get the library ready from a code standpoint and a whole new effort to get the documentation written, debugged and submitted.

I really don't know how long it would have taken me to get IE.au3 into the distribution if Bob (big_daddy) hadn't stepped in and taken charge of the documentation. I was able to focus on writing the examples while he took what I had prepared in the function headers, embellished and formatted to meet the requirements of the automated helpfile build process. Jos (JdeB) was a critical player in getting test builds of the helpfile created for debugging and final submission as well.

Don't know if LocoDarwin's situation is similar to what mine was, but I suspect it might be.

If interested, here is some starter info for the documentation. Standards are in a sticky at the top of this (Examples) forum.

Dale

Here is an example of the doc file for _IEAttach (which is stored in a file called _IEAttach.txt). Examples are stored in a file called _IEAttach.au3

expandcollapse popup

###User Defined Function###
_IEAttach

###Description###
Attach to the first existing instance of Internet Explorer where the search string sub-string matches based on the selected mode.

###Syntax###
#include <IE.au3> 
_IEAttach ( $s_string [, $s_mode = "Title"] )

###Parameters###
@@ParamTable@@
$s_string
    String to search for (for "embedded" or "dialogbox", use Title sub-string or HWND of window)
$s_mode
    Optional: specifies search mode
    Title = (Default) sub-string of main document title
    WindowTitle = sub-string of full window title (instead of document title)
    URL = sub-string or url of the current page
    Text = sub-string in text from the body of the current page
    HTML = sub-string in html from the body of the current page
    HWND = hwnd of the browser window
    Embedded = title sub-string or hwnd of of the window embedding the control
    DialogBox = title sub-string or hwnd of modal/modeless dialogbox
@@End@@

###ReturnValue###
@@ReturnTable@@
Success:    Returns an object variable pointing to the IE Window Object
Failure:    Returns 0 and sets @ERROR
@Error: 0 ($_IEStatus_Success) = No Error
    5 ($_IEStatus_InvalidValue) = Invalid Value
    7 ($_IEStatus_NoMatch) = No Match
@Extended:  Contains invalid parameter number
@@End@@


###Remarks###
_IEAttach provides the "DialogBox" parameter to attach to modal and modeless dialogs created by the browser.  It is important to note that not all dialogs created through browser interaction can be attached to and controlled in this way.  Many of these dialogs are actually standard windows and can be controlled through the traditional AutoIt window functions.  A reliable way to tell the difference between these types of windows is to use the "AutoIt Window Info" tool to examine it -- if the window contains a control called "Internet Explorer_Server1" then you can attach to it with this function, if it does not it is a standard window and traditional AutoIt windows functions must be used to control it.

The "embedded" option must be used to attach to HyperTextApplication (.hta) windows.


###Related###
_IECreate, _IECreateEmbedded, _IEQuit


###Example###
@@IncludeExample@@

Edited May 14, 2007 by DaleHohm

[big_daddy]

There's no telling how long I spent writing documentation for IE.au3. Some of the most time consuming parts of it was digging through the code, understanding what it was doing, then covering every possible return code for the documentation. In the end it was definitely worth it though!

For anyone wanting to write documentation I'd suggest taking a look at this script. It will help keep you from making errors while generating the *.txt files that Dale mentioned.

[randallc]

Hi, thanks,

I have been in the midst of doing 2 helpfiles for UDFs that probably won't be used anyway.

Your script looks useful; would it be easy to get it to read the info from the UDF itself and generate multiple .txt prepare files from the UDF documentation headers?

I had presumed there was already such a beast, but I cannot find one?

randall

[big_daddy]

Hi, thanks,

I have been in the midst of doing 2 helpfiles for UDFs that probably won't be used anyway.

Your script looks useful; would it be easy to get it to read the info from the UDF itself and generate multiple .txt prepare files from the UDF documentation headers?

I had presumed there was already such a beast, but I cannot find one?

randall

I'm not sure about easy, but it would definitely be doable.

[litlmike]

I think I found a typo in your UDF:

; Syntax: _ExcelCloseDoc($oExcel, $fSave = 1, $fAlerts = 0)

Should be

; Syntax: _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)

Also, when I use _ExcelBookClose($oExcel, 0, 0) it still asks me if I want to save the file.

Thanks for the UDF

[nobbe]

hi

i added a "quick" excel print command (its far from perfect :-) i have to evaluate this more deeply, but it works for me now..

;

;

; test for printing

_ExcelPrint($oExcel);

; .. and in the UDF add

;

; print current active worksheet

;

Func _ExcelPrint($oExcel)

If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)

; for information about currrent active printer

; MsgBox(1, "printer" , $oExcel.ActivePrinter);

;$oExcel.ActivePrinter="Adobe PDF auf NE03:" ; e.g for printing into PDF on my machine ..

$oExcel.ActiveWorkbook.PrintOut() ;

Return 1

EndFunc

Edited June 18, 2007 by nobbe

[tns1]

ExcelCOM 1.32 with AutoIt v3.2.4.9, Excel 2000, WinXP

Can't get the _ExcelReplaceInRange() to work. I just get a general error dialog with no hint of the problen on this line:

$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase, Default, $fMatchFormat, $fReplaceFormat)

Tried a few different things - some test code:

$oExcel = _ExcelBookOpen("my.xls", 1, False)

;$msg = _ExcelReplaceInRange($oExcel, "FIND", "REPLACED", 1, 1, 10, 10, 2, True, False, False)

$msg = _ExcelReplaceInRange($oExcel, "FIND", "REPLACED", "A1:T20" , 1, 1, 1, 2, True, False, False)

If $msg = 0 Then

MsgBox(0, "Error", "@error = " & @error)

EndIf

_ExcelBookClose($oExcel, 1, 0)

See anything?

[exodius]

Quick question, and this may have been addressed before, using this UDF is entirely dependent on having Microsoft Excel installed, right?

[big_daddy]

Quick question, and this may have been addressed before, using this UDF is entirely dependent on having Microsoft Excel installed, right?

That's correct.

[tns1]

ExcelCOM 1.32 with AutoIt v3.2.4.9, Excel 2000, WinXP

Can't get the _ExcelReplaceInRange() to work. I just get a general error dialog with no hint of the problen on this line:

$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase, Default, $fMatchFormat, $fReplaceFormat)

Tried a few different things - some test code:

$oExcel = _ExcelBookOpen("my.xls", 1, False)

;$msg = _ExcelReplaceInRange($oExcel, "FIND", "REPLACED", 1, 1, 10, 10, 2, True, False, False)

$msg = _ExcelReplaceInRange($oExcel, "FIND", "REPLACED", "A1:T20" , 1, 1, 1, 2, True, False, False)

If $msg = 0 Then

MsgBox(0, "Error", "@error = " & @error)

EndIf

_ExcelBookClose($oExcel, 1, 0)

See anything?

The range selection is working, just not the replace.

Just to be clear on this, is a beta release still needed for the ExcelCOM_UDF?

Autoit v3.2.4.9 has everything that Beta 3.2.1.12 had doesn't it?

[tns1]

ExcelCOM 1.32 with AutoIt v3.2.4.9, Excel 2000, WinXP

Can't get the _ExcelReplaceInRange() to work. I just get a general error dialog with no hint of the problen on this line:

$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase, Default, $fMatchFormat, $fReplaceFormat)

Tried a few different things - some test code:

$oExcel = _ExcelBookOpen("my.xls", 1, False)

;$msg = _ExcelReplaceInRange($oExcel, "FIND", "REPLACED", 1, 1, 10, 10, 2, True, False, False)

$msg = _ExcelReplaceInRange($oExcel, "FIND", "REPLACED", "A1:T20" , 1, 1, 1, 2, True, False, False)

If $msg = 0 Then

MsgBox(0, "Error", "@error = " & @error)

EndIf

_ExcelBookClose($oExcel, 1, 0)

See anything?

I got it working by removing the last 3 parameters from the call:

$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase) ; , Default, $fMatchFormat, $fReplaceFormat)

I first created a macro in Excel to do the same thing and then examined it in VBE - it didn't show those extra parms. Does this mean they don't exist in my version of Excel or what?

Working code:

$oExcel = _ExcelBookOpen("my.xls", 1, False)

_ExcelReplaceInRange($oExcel, "FIND", "REPLACED", "A1:T20", 1, 1, 1, 2, True, False, False)

_ExcelBookClose($oExcel, 1, 0)

[tns1]

CODE

;===============================================================================

;

; Description: Returns Excel Version String

; Syntax: $sVersion = _ExcelBookNew($oExcel)

; Parameter(s): None

; Requirement(s): None

; Return Value(s): On Success - Returns 8,9,10,11

; On Failure - Returns 0 and sets @error on errors:

; @error=1 - Specified object does not exist

; Author(s):

; Note(s): None

;

; _ExcelVersion()

;===============================================================================

Func _ExcelVersion($oExcel)

If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)

$iVer = Int(Number($oExcel.Application.Version))

;Version 8 = '97, 9 - 2000, 10 - XP, 11 - 2003

If $iVer >=8 AND $iVer <=11 Then

Return $iVer

Endif

Return 0

EndFunc ;==>_ExcelVersion

In my limited testing, there appear to be some different behaviors and differences in parameter requirements between different versions of Excel.

For instance in the FindReplace

CODE

;version difference between 2000 and 2003

If _ExcelVersion($oExcel) = 9 Then

$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase)

Else

$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase, Default, $fMatchFormat, $fReplaceFormat)

ElseIf

Also 2003 will produce a warning dialog if a replace string is not found, where 2000 does not. Can this be suppressed?

[Ram]

Is it possible to read sheet2 data using ExcelCOM UDF? if so how.. Please let me know..