Yet Another -- ExcelCOM UDF

[Ruigerock]

I'm trying to use your script for the first time, because i can really use it to create an Excel sheet reader

I downloaded the ExcelCOM_UDF.au3 and tested it.

I got errors running the script, what is missing ? I tried 3.1.1.110 beta en 3.1.1.0.

C:\Rick\ExcelCOM_UDF.au3(110,52) : ERROR: SetError() [built-in] called with wrong number of args.
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Rick\ExcelCOM_UDF.au3(111,57) : ERROR: SetError() [built-in] called with wrong number of args.
    If NOT IsNumber($fVisible) Then Return SetError(2, 0, 0)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Rick\ExcelCOM_UDF.au3(140,52) : ERROR: SetError() [built-in] called with wrong number of args.
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Rick\ExcelCOM_UDF.au3(141,60) : ERROR: SetError() [built-in] called with wrong number of args.
    If NOT FileExists($sFilePath) Then Return SetError(2, 0, 0)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Rick\ExcelCOM_UDF.au3(146,38) : ERROR: syntax error
        .WorkBooks.Open($sFilePath, Default,

etc etc .. all seterror are returning an error

[Alodar]

Quick hopeful mention. A lot of the excel sheets I end up using have no names, they're just from an email and have book1 or book2, and I don't want to save them, I don't use them that much. Instead of _ExcelBookOpen, or rather, along with that, what about a function that's like _ExcelActiveWorkBook, or _ExcelActiveWorkSheet?

[Locodarwin]

tns1: I'll have a look at the function when I get a chance. I wrote it with Excel 2003 in mind but I suppose it hasn't been tested as deeply as I'd like.

Ram: I assume you mean you want to read other sheets in a workbook. Have a look at the comment header for _ExcelSheetActivate().

Ruigerock: The SetError() problem you're running into probably has to do with the fact that you're using an older version of AutoIt. You should consider upgrading to 3.2.x.

Alodar: Can you give me an example of what you need? Are you wanting to attach to a currently running instance of Excel, or just open a new, toss-away file? If the latter, check out _ExcelBookNew().

-S

[Alodar]

What I want is to attach to a currently open excel file. I often have three or four books open, and one sheet that I'm using for 'temp' work on, which I end up running scripts on.

So something like

Func _ExcelActiveWorkSheet($fVisible = 1, $fReadOnly = False)
    $oExcel = ObjGet("","Excel.Application")
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0
    With $oExcel
        .Visible = $fVisible
    EndWith
    Return $oExcel
EndFunc

Maybe? I think that this works. I'm not sure that would select the sheet I have visible, or even the right workbook. I didn't want to edit your whole UDF just for a function at the moment.

Although, I'm finding that your read array function is great, but limited in that it only does one row, so that i need to call it n times for n rows/columns of data, and that since there's no native nested array solution in AutoIT (i.e. $MainArray[1[3]] doesn't work) it can be difficult.

For all else though, this stuff is super useful and awesome, and thanks so much for your effort and work in making this up!

[Paulie]

Loco, This is pretty awesome!

I used this at work, and got big brownie points so thanks.

I'm sure the documentation of 60+ functions will be hard work, but i would love to see it done so that this can be included into Autoit.

If there is anything I can help you do, I'm pretty available, drop me a PM or IM(aim) at "TheGayFruitFly." I'm not sure how much help i can be, but i'm willing to help in any way i can.

Keep up the great work!

[LukeSchafer]

Hi guys,

Great UDF btw.

I'm getting an error when I try to use this on one machine, but it is fine when running it on all others - what do you think might be the problem with that one machine?

• There is no specific error message, but the hex dump says something about the excell.dll i think. I ran a vbscript on the machine to do a CreateObject("Excel.Application") and it worked fine.
• It has the same version of office installed (2003)
• Excel actually opens, it just errors
• Subsequent attempts to open excel say it wasn't shut down properly. Telling this error to restart Excel will open it, but without loading the sheet was specified before

Does anyone have any ideas? I can investigate further to see if I can get any more information if you need it

Thanks

Luke

[JohnBailey]

I don't want to reinvent the wheel. Has anyone already created a function for obtaining the column count? If not I totally don't mind doing it, I've already started the research for it.

[schurl85]

I also do have a problem. i want to sort some columns. lets say A5:J11 sort by A Ascending

i get this output on the console:

C:\Program Files\AutoIt3\Include\excel.au3 (987) : ==> The requested action with this object has failed.: 
$oExcel.Range($sRangeOrRowStart).Sort ($oExcel.Range($sKey), $iDirection) 
$oExcel.Range($sRangeOrRowStart).Sort ($oExcel.Range($sKey)^ ERROR

this is my sourcecode:

_ExcelSort($oExcel, "A", "A6:J" & $i,1,1,1,1)

i tried it with excel 2003 and 2007

i have autoit 3.2.6.0 and the UDF 1.32. Would be nice if someone could help

greets

schurl

[Locodarwin]

JohnBailey: use _ExcelSheetUsedRangeGet($oExcel, $vSheet). It returns an array which includes, among other things, the last column used in the sheet. Check out the comment header for the function.

schurl85: Try this:

_ExcelSort($oExcel, "A1", "A6:J" & $i,1,1,1,1)

Good luck, both of you.

-S

Edited August 31, 2007 by Locodarwin

[schurl85]

Thx for your help Locodarwin, it now does work without a problem.

[Buey]

Can you use add-ins from Excel COM? I wanted to use Fourier in the Data Analysis toolkit, but I couldn't find anything about it in Microsoft's weak API or in this UDF.

Thanks!

[litlmike]

Anyone had issues with _ExcelRowDelete()? I have this simple code that works perfectly some of the time, and other times not at all. I can't seem to debug if the issue is with the UDF or with the script.

Func Delete_Rows ($Column = 9)
    $iNumRows = 1
    For $Row = 2 To $Row2
        $iRow = $Row
        If $oExcel.ActiveWorkBook.ActiveSheet.Cells($Row,$Column).Value = "No" Then
        ; Delete a number of rows from the active worksheet.
        ; Syntax:           _ExcelRowDelete($oExcel, $iRow, $iNumRows)
        ;$iRow - The specified row number to delete
        ;$iNumRows - The number of rows to delete
        _ExcelRowDelete($oExcel, $iRow, $iNumRows)
    Next
EndFunc

[sstanfie]

Is there a help file yet for this UDF?

Thank you.

[Skizmata]

Thank you so much for this handy UDF.

I have a small request.

I would love a print function that had the visibility options your other functions have.

A long winded explanation as to why this would help me is below.

<LongWindedBS>

The company I work for uses Excel documents to house our product specification sheets these sheets are printed off a file server at our corporate office each time a product is taken to the production floor to be ran. We have upwards of 3,000 specification sheets on this server. So to make them easier to find/print they are in a directory structure based on what facility they are used in and so on. One of my current projects has me looking up specification sheets often to check on things. My lack of familiarity with all our products makes it hard for me to find these files in the maze of directories. So AutoIt to the rescue! I wrote a little application called Spec. Pull that indexes the directory structure and saves it as and ini with each part number associated with the location of the specification sheet for it, with a simple GUI I can type a list of part numbers and press a button and it starts printing them. Using this line

ShellExecuteWait("N:\" & $SpecToPrint, "", "", "print", @SW_HIDE)

Unfortunatly whatever magic AU3 does to ask this window to stay hidden dosent work so if someone prints 60 files they have to watch excel open and close 60 times. Your functions seem to have no problem keeping excel quite in the background.

</LongWindedBS>

Your help would be greatly appreciated.

-Skizmata

[big_daddy]

@Skizmata - Here is the PrintOut Method that can be used to print. If you call it with no parameters it prints the active sheet on the default printer.

[maqleod]

no requests or bugs at this point, just saying that I used this for a script for my work and it worked great, thanks for the great UDF!

[big_daddy]

@Locodarwin - I hope you haven't given up on writing the documentation for this? It would be great to see this library added to the standard udfs.

[gertsolo]

Hey,

probably a stupid one, but if I want to call _ExcelWriteCell in an already open sheet, what should I use for $oExcel since I did not open the sheet with _ExcelBookOpen() or _ExcelBookNew().

one more: how do I select another worksheet (not a workbook) in an open workbook. (you know, the clickable tabs at the bottom of a workbook.

thx,

[Skizmata]

@Skizmata - Here is the PrintOut Method that can be used to print. If you call it with no parameters it prints the active sheet on the default printer.

Thanks so much this seems like it could lead me in the right direction. Unfortunately I am to inexperienced to really know how to use this information. An example of this printing a .xls would be wonderful and help send me on the path of understanding the MSDN.

[big_daddy]

one more: how do I select another worksheet (not a workbook) in an open workbook. (you know, the clickable tabs at the bottom of a workbook.

_ExcelSheetActivate() or _ExcelSheetNameActivate()

probably a stupid one, but if I want to call _ExcelWriteCell in an already open sheet, what should I use for $oExcel since I did not open the sheet with _ExcelBookOpen() or _ExcelBookNew().

This should work.

expandcollapse popup

$sSearch = "Test.xls"
$sMode = "FileName"

;~ $sSearch = "c:\Test.xls"
;~ $sMode = "FilePath"

;~ $sSearch = "Microsoft Excel - Test.xls  [Compatibility Mode]"
;~ $sMode = "Title"

$oExcel = _ExcelAttach($sSearch, $sMode)
If @error Then
    ConsoleWrite("No open workbooks matched your search string." & @CR)
Else
    ConsoleWrite("Attachment was successful." & @CR)
EndIf

;===============================================================================
;
; Function Name:    _ExcelAttach()
; Description:      Attach to the first existing instance of Microsoft Excel where the
;                   search string matches based on the selected mode.
; Parameter(s):     $s_string   - String to search for
;                   $s_mode     - Optional: specifies search mode
;                                   FileName    = Name of the open workbook
;                                   FilePath    = (Default) Full path to the open workbook
;                                   Title       = Title of the Excel window
; Requirement(s):   AutoIt3 Beta with COM support (post 3.1.1)
;                   On Success  - Returns an object variable pointing to the Excel.Application object
;                   On Failure  - Returns 0 and sets @ERROR = 1
; Author(s):        Bob Anthony (big_daddy)
;
;===============================================================================
;
Func _ExcelAttach($s_string, $s_mode = "FilePath")
    $s_mode = StringLower($s_mode)

    Local $o_Result, $o_workbook, $o_workbooks

    $o_Result = ObjGet("", "Excel.Application")
    If @error Or Not IsObj($o_Result) Then
        ConsoleWrite("Unable to attach to existing Excel.Application object." & @CR)
        Return SetError(1, 0, 0)
    EndIf

    $o_workbooks = $o_Result.Application.Workbooks
    If Not IsObj($o_workbooks) Or $o_workbooks.Count = 0 Then
        ConsoleWrite("There were no open excel windows." & @CR)
        Return SetError(1, 0, 0)
    EndIf

;~  ConsoleWrite($o_workbooks.count & @CR)
    For $o_workbook In $o_workbooks

        Switch $s_mode
            Case "filename"
;~              ConsoleWrite($o_workbook.Name & @CR)
                If $o_workbook.Name = $s_string Then
                    $o_workbook.Activate
                    Return $o_workbook.Application
                EndIf
            Case "filepath"
;~              ConsoleWrite($o_workbook.FullName & @CR)
                If $o_workbook.FullName = $s_string Then
                    $o_workbook.Activate
                    Return $o_workbook.Application
                EndIf
            Case "title"
;~              ConsoleWrite($o_workbook.Application.Caption & @CR)
                If ($o_workbook.Application.Caption) = $s_string Then
                    $o_workbook.Activate
                    Return $o_workbook.Application
                EndIf
            Case Else
                ; Invalid Mode
                ConsoleWrite("You have specified an invalid mode." & @CR)
                Return SetError(1, 0, 0)
        EndSwitch
    Next
    Return SetError(1, 0, 0)
EndFunc   ;==>_ExcelAttach