Yet Another -- ExcelCOM UDF

[Flandry]

This time I have read the syntax-description very well! ;-)

I was not able to use the sort functions. I got everytime errors. An other sort command published by Locodarwin in this forum (without using the udf) has worked for me.

http://www.autoitscript.com/forum/index.php?showtopic=35679

Maybe the problem is caused by the different languages too as described by MrDev.

Greetings

[litlmike]

It was just missing from the Syntax comment line. Thanks for bringing it to my attention.

The $sScreenTip parameter displays a text tip when the user hovers the mouse over the hyperlink. It can be any reasonably-sized string.

-S

Oh ok. Thanks for the info.

[MRDev]

@Flandry

For $sKey you have to use a range string ("B1" or "A3" ...). With a single char ("B") the script will end with an error. I tested it with Office 2003 for both sort functions. It has nothing to do with the language.

@Locodarwin

Could this be because of different Office versions?

[Locodarwin]

This behavior is as designed. My comment header contains an error in syntax. You have to use a single cell range for the key - a column or row designation is not enough. This is true no matter what version of Excel you're working with.

I think it has to do with the algorithm Excel uses to decide which cell contains the header, and how many compound parts of the range are to be sorted. If a header is found, and you've requested it be used, Excel has to know which particular cell then begins/becomes the key for the full range.

Just a guess.

-S

[JohnBailey]

LocoDarwin, thanks for the great UDF!!

I've tweaked it in a few place for my purposes, but one may help everyone. I couldn't get close to work. I looked at the function and noticed two things about the _ExcelBookClose Application was after $oExcel and DisplayAlerts = true and ScreenUpdating = true were before quit which was causing it to display the alerts before quiting (just backwards).

Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    $oExcel.DisplayAlerts = $fAlerts
    $oExcel.ScreenUpdating = $fAlerts
    If $fSave Then
        $oExcel.ActiveWorkBook.Save
    EndIf
    $oExcel.Quit
    $oExcel.DisplayAlerts = True
    $oExcel.ScreenUpdating = True
    Return 1
EndFunc ;==>_ExcelBookClose

BTW, I took .Application out because I didn't understand why it was there. There may be a very good reason. I just couldn't find any clues on MSDN.

Edited March 21, 2007 by JohnBailey

[JohnBailey]

Three new parameter additions to consider:

UpdateLinks

Security

fpassword

expandcollapse popup

;===============================================================================
;
; Description:      Opens an existing workbook and returns its object identifier.
; Syntax:           $oExcel = _ExcelBookOpen($sFilePath, $fVisible = 1)
; Parameter(s):     sFilePath - Path and filename of the file to be opened
;                   $fVisible - Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1)
;                   $fReadOnly - Flag, whether to open the workbook as read-only (True or False) (default=False)
;                   $security - Flag, whether to run macros, deny macros, or ask (1 = allow, 2 = ask, 3 = deny) (default=2)
;                   $updateLinks - Flag, whether to ask for updatelinks or automatically update (1 = ask, 2 = automatically update, 0 = no update) (default=1)
;                   $fpassword - String, the password required to open a password protected excel file (default='')
; Requirement(s):   None
; Return Value(s):  On Success - Returns new object identifier
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - File does not exist
; Author(s):        SEO <locodarwin at yahoo dot com> 
; Editor(s):        JohnBailey <john at schnitmangroup dot com> and cameronsdad <http://www.autoitscript.com/forum/index.php?act=Msg&CODE=4&MID=6554>
; Note(s):          I (John) recommend setting AutomationSecurity back to the user's default when finished.
;
;===============================================================================
Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $security = 2,$updateLinks = 1, $fpassword = '')
    $oExcel = ObjCreate("Excel.Application")
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0
    If $fReadOnly = 1 Then $fReadOnly = True
    If $fReadOnly = 0 Then $fReadOnly = False
    If $updateLinks = 1 Then $updateLinks = True
    If $updateLinks = 0 Then $updateLinks = True
    If $updateLinks = 2 Then $updateLinks = False
    With $oExcel
        .Visible = $fVisible
        .AutomationSecurity = $security
        .AskToUpdateLinks = $updateLinks
        If $fVisible = 0 AND $security = 1 Then
            .ScreenUpdating = False
        EndIf
        If $updateLinks = 1 or 2 Then
            .WorkBooks.Open($sFilePath, True, $fReadOnly, Default, $fpassword)
        Else 
            .WorkBooks.Open($sFilePath, False, $fReadOnly, Default, $fpassword)
        EndIf
        .ActiveWorkbook.Sheets(1).Select()
        .AskToUpdateLinks = true
    EndWith
    Return $oExcel
EndFunc ;==>_ExcelBookOpen

locoDarwin, you can remove my name from the editor(s) area. I don't care, but I do give mad props to cameronsdad.

[L01c]

Yes, unfortunately I can't predict how my collection will behave on non-English installations. Sorry about that.

Since your code change takes into account both German and English installations, I'll use it. That might be the only function that requires such a change, since in other situations I merely look for numbers vs. letters and not a particular letter. Thanks for the submission!

-S

... Except that it now probably doesn't work properly with other languages where localized "Column" starts with C... As in French & Spanish.

Got that from here:

Language      Cell Item Name 
English       R1C1 
French         L1C1 
Dutch           R1K1 
Spanish       F1C1 
German         Z1S1

[DjDeep00]

How can I read the entire excel file into one array? I tried creating my own array by placing the _ExcelReadArray() into a for loop. But I need to know how many rows and columns the excel file has. Maybe someone already has done this? Please help.

[DjDeep00]

Locodarwin? Anyone? Please help.

[DjDeep00]

This is what I have but not sure if this is the best way?

$Excel_File=_ExcelBookOpen(@ScriptDir & "\Billing.xls",0)

$i=0

For $x=65 to 90

$i=$i+1

$Data=_ExcelReadCell($Excel_File,Chr($x) & "1",1)

If $x>65 and $Data="" then

For $j=1 to 5000

$Data=_ExcelReadCell($Excel_File,"A" & $j,1)

If $Data="" then ExitLoop

Next

ExitLoop

EndIf

Next

Dim $New_Excel_Array[1]

For $y=1 to $j-1

$Excel_Array=_ExcelReadArray($Excel_File, $y, 1, $i-1, 0,1)

_ArrayAdd($New_Excel_Array,StringReplace(_ArrayToString($Excel_Array,"|"),$i-1 & "|",""))

Next

_ArrayDisplay($New_Excel_Array,"")

_ExcelBookClose($Excel_File,0)

[mikeytown2]

I would use this function to get the used range of the excel worksheet

;===============================================================================
;
; Description:    Return the last cell of the used range in the specified worksheet.
; Syntax:          $array = _ExcelSheetUsedRangeGet($oExcel, $vSheet)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                  $vSheet - The sheet name or number to be checked.
; Requirement(s):   None
; Return Value(s):  On Success - Returns an array of used range values:
;                       $array[0] - The last cell used, in A1 format (if 0 is returned, worksheet is blank)
;                       $array[1] - The last cell used, in R1C1 format
;                       $array[2] - The last column used, as an integer
;                       $array[3] - The last row used, as an integer
;                  On Failure - Returns 0 (as non-array numeric value) and sets @error on errors:
;                   @error=1 - Specified object does not exist
;                   @error=2 - Invalid sheet number
;                   @error=3 - Invalid sheet name
; Author(s):        DaLiMan, SEO <locodarwin at yahoo dot com>
; Note(s):        Upon return, $array[0] will equal numeric value 0 if the worksheet is blank
;
;===============================================================================
Func _ExcelSheetUsedRangeGet($oExcel, $vSheet)

And if you want to grab all the info from all the worksheets i would use this function to list the worksheets

;===============================================================================
;
; Description:    Return a list of all sheets in workbook, by name, as an array.
; Syntax:          _ExcelSheetList($oExcel)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; Requirement(s):   None
; Return Value(s):  On Success - Returns an array of the sheet names in the workbook (the zero index stores the sheet count)
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
;===============================================================================
Func _ExcelSheetList($oExcel)

Then use this to activate the worksheet that was found

;===============================================================================
;
; Description:    Activate the specified sheet by string name or by number.
; Syntax:          _ExcelSheetActivate($oExcel, $vSheet)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $vSheet - The sheet to activate, either by string name or by number
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Specified sheet number does not exist
;                       @error=3 - Specified sheet name does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
;===============================================================================
Func _ExcelSheetActivate($oExcel, $vSheet)

Then using all of the data that was gathered from above, i would use the excel read array function

;===============================================================================
;
; Description:    Create an array from a row or column of the active worksheet.
; Syntax:          _ExcelReadArray($oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0)
; Parameter(s):  $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $iStartRow - The table row to start reading the array from
;                   $iStartColumn - The table column to start reading the array from
;                   $iNumCells - The number of cells to read into the array
;                   $iDirection - The direction of the cells to read into array (0=right, 1=down)
;                   $fIndexBase - Specify whether array created is to have index base of either 0 or 1
; Requirement(s):   None
; Return Value(s):  On Success - Returns an array with the specified cell contents
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Parameter out of range
;                           @extended=0 - Row out of range
;                           @extended=1 - Column out of range
;                       @error=3 - Invalid number of cells
;                       @error=4 - Invalid direction parameter
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
;===============================================================================
Func _ExcelReadArray($oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0)

One thing to note about this is it only reads 1 row or one Column, as stated in the $iDirection variable. so you need to use this multiple times if you have more then 1 row or column of data.

EDIT

Almost forgot the ExcelSheetUsedRangeGet function requires the name of the excel sheet, i would use this function to get that.

;===============================================================================
;
; Description:    Return the name of the active sheet.
; Syntax:          $string = _ExcelSheetNameGet($oExcel)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; Requirement(s):   None
; Return Value(s):  On Success - Returns the name of the active sheet (string)
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
;===============================================================================
Func _ExcelSheetNameGet($oExcel)

Also this code will require multiple loops, otherwise it won't work too well.

Edited April 16, 2007 by mikeytown2

[randallc]

Hi,

If you don't want to do looping, see my array read func in ExcelCom (link in my sig)

I doubt the new Locodarwin function needs to loop, actually, but check with him re syntax?

Berst, randall

[Locodarwin]

I apologize for not getting back to you sooner.

Mikeytown's solution is recommended.

Randallc is right, in that you do not need to loop the _ExcelReadArray() function. However, the function only supports one dimension of data reading, so if you have a table (as opposed to a list) then you'll need to perform one _ExcelReadArray() for each additional row or column to be read. At some point in the future, the function will likely be expanded to include 2-dimensional array capture, but for now it does not. If Randallc's function supports 2-dimensions (he'd have to chime in for confirmation), and that's what you need, then by all means put it to good use.

L01c: I may someday expand the associated functions to support multiple languages; as it stands, I can't really justify spending the time on it other than on a case by case basis. Thanks for your input.

-S

[randallc]

If Randallc's function supports 2-dimensions (he'd have to chime in for confirmation),

Hi,

Yes, the examples with arrays show 2d funcs; examples show use with "usedrange" as well; one call only,m so fairly fast and easy

Best, Randall

[DjDeep00]

Thanks alot everyone.

[EasyGoing]

My statement works if I change the variable to a numberic value. The issue is in the string or format that the clipboard is in.

IF i change the "test" to 1 and run the script is works. What do you thing it is? Everything else works. copy paste variable all works. It just will not read the char type or string type.

WinActivate("Microsoft Excel - question.xls")

WinWaitActive("Microsoft Excel - question.xls")

Send("^c")

Sleep(100)

Dim $occupation

$occupation = ClipGet()

If $occupation = "test" Then

Send("{DOWN}")

Sleep(1000)

send($occupation)

Else

Send("{DOWN}")

Sleep(1000)

Send($occupation)

Send("did not work")

EndIf

[gcriaco]

Minor UDF typo in ExcelCOM_UDF.au3, _ExcelBookClose function header:

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

Sorry for bothering you and many thanks for your Job

Peppe

P.S.: what about the inclusion of your UDF in the Scite4AutoIt package?

I read the roadmap. BTW, hope you'll finish the UDF ASAP. Thanks again.

Edited April 30, 2007 by gcriaco

[livewire]

Are these lines supposed to be in _ExcelBookClose()?

$oExcel.Application.DisplayAlerts = True

$oExcel.Application.ScreenUpdating = True

I think they should be removed

-Livewire

[Leykis101]

I love your Excel UDF, except one thing. No matter what I do, I cannot get it to sort. I am hoping you can help me with this. Below is the code I have tried, as you can see I tried two different ways to sort it. I am using Excel 2007, but I can try it on 2003 if that is the problem. I have tried sorting before on 03 though and could never get it to work. I have also included links to the 2 .xls files im trying to get to sort. It is nothing but a bunch of numbers, but thats what I need to do in the end. Please help me if you can. Thanks

Test Excel File.xls

Test Excel File2.xls

CODE

#NoTrayIcon

#include <_ExcelCOM_UDF.au3>

#include <Array.au3>

Dim $oExcel1, $oExcel2, $fVisible, $fSave, $fAlerts, $vSheet, $aTemp, $errorret

$oExcel1 = _ExcelBookOpen( @MyDocumentsDir & "\Test Excel File.xls", $fVisible = 1)

$oExcel2 = _ExcelBookOpen( @MyDocumentsDir & "\Test Excel File2.xls", $fVisible = 1)

_ExcelShow($oExcel1)

_ExcelShow($oExcel2)

$aTemp1 = _ExcelSheetUsedRangeGet($oExcel1, 1)

$aTemp2 = _ExcelSheetUsedRangeGet($oExcel2, 1)

_ExcelCopy($oExcel1, 1, 1, $aTemp1[3], $aTemp1[2])

_ExcelPaste($oExcel2, $aTemp2[3]+1, 1)

Sleep ( 1000 )

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

;~ _ExcelSort($oExcel2, 1, 1, 1, 10, 10)

MsgBox ( 0, "", $errorret & " " & @error & " " & @extended )

[livewire]

I love your Excel UDF, except one thing. No matter what I do, I cannot get it to sort. I am hoping you can help me with this. Below is the code I have tried, as you can see I tried two different ways to sort it. I am using Excel 2007, but I can try it on 2003 if that is the problem. I have tried sorting before on 03 though and could never get it to work. I have also included links to the 2 .xls files im trying to get to sort. It is nothing but a bunch of numbers, but thats what I need to do in the end. Please help me if you can. Thanks

Test Excel File.xls

Test Excel File2.xls

CODE

#NoTrayIcon

#include <_ExcelCOM_UDF.au3>

#include <Array.au3>

Dim $oExcel1, $oExcel2, $fVisible, $fSave, $fAlerts, $vSheet, $aTemp, $errorret

$oExcel1 = _ExcelBookOpen( @MyDocumentsDir & "\Test Excel File.xls", $fVisible = 1)

$oExcel2 = _ExcelBookOpen( @MyDocumentsDir & "\Test Excel File2.xls", $fVisible = 1)

_ExcelShow($oExcel1)

_ExcelShow($oExcel2)

$aTemp1 = _ExcelSheetUsedRangeGet($oExcel1, 1)

$aTemp2 = _ExcelSheetUsedRangeGet($oExcel2, 1)

_ExcelCopy($oExcel1, 1, 1, $aTemp1[3], $aTemp1[2])

_ExcelPaste($oExcel2, $aTemp2[3]+1, 1)

Sleep ( 1000 )

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

;~ _ExcelSort($oExcel2, 1, 1, 1, 10, 10)

MsgBox ( 0, "", $errorret & " " & @error & " " & @extended )

It's key first then range.

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