Yet Another -- ExcelCOM UDF

[Locodarwin]

Thanks, big_daddy, for lending a hand in this thread.

@big_daddy: I have some of the documentation complete. I intend to get it all together and submit it...someday...soonish? By the way, I like your _Attach routine and it would make a good addition to the UDF, with your permission.

@maqleod: Thanks for the feedback. I'm happy to hear of your success story.

@litlmike: I can't see anything wrong with the code you posted. Can you submit a portion of code that reproduces the problem?

@Skizmata: Print functions have been included in a (yet-to-be-released) future version of the UDF. Here's a printing function extracted from it that may work for you:

expandcollapse popup

;===============================================================================
;
; Description:      Print a worksheet.
; Syntax:           _ExcelPrintSheet($oExcel, $vSheet, $iCopies = 1, $sActivePrinter = "", $fPrintToFile = False, _
;                                       $fCollate = False, $sPrToFileName = "")
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $vSheet - A sheet number or name to print
;                   $iCopies - How many copies to print (default = 1)
;                   $sActivePrinter - The URL and port of the printer to make active (default = "")
;                   $fPrintToFile - Flag, print to file instead of printer (default = False)
;                   $fCollate - Flag, to collate multiple copies (default = False)
;                   $sPrToFileName - String filename to print to when printing to file (default = "")
; 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 doesn't exist
;                       @error=3 - Specified sheet name doesn't exist
;                       @error=4 - $fPrintToFile out of range
;                       @error=5 - $fCollate out of range
;                       @error=6 - $iCopies must be between 1 and 255
;                       @error=7 - Trying to print to filename that is unspecified
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelPrintSheet($oExcel, $vSheet, $iCopies = 1, $sActivePrinter = "", $fPrintToFile = False, _
                        $fCollate = False, $sPrToFileName = "")
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fPrintToFile < 0 Or $fPrintToFile > 1 Then Return SetError(4, 0, 0)
    If $fCollate < 0 Or $fCollate > 1 Then Return SetError(5, 0, 0)
    If $iCopies < 1 Or $iCopies > 255 Then Return SetError(6, 0, 0)
    If $sActivePrinter = "" Then $sActivePrinter = $oExcel.ActivePrinter
    If $sPrToFileName = "" Then
        If $fPrintToFile = True Then Return SetError(7, 0, 0)
    EndIf
    If IsNumber($vSheet) Then
        If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)
    Else
        Local $fFound = 0
        Local $aSheetList = _ExcelSheetList($oExcel)
        For $xx = 1 To $aSheetList[0]
            If $aSheetList[$xx] = $vSheet Then $fFound = 1
        Next
        If NOT $fFound Then Return SetError(3, 0, 0)
    EndIf
    $oExcel.Sheets($vSheet).Activate
    $oExcel.ActiveSheet.PrintOut(Default, Default, $iCopies, False, $sActivePrinter, $fPrintToFile, $fCollate, $sPrToFileName)
    Return 1
EndFunc ;==>_ExcelPrintSheet

To use this function for what you're wanting to do, it'd look like this:

1. Open the document invisibly using _ExcelBookOpen().

2. Use the _ExcelSheetPrint() function above to print out whichever sheet(s) you need.

3. Close the file using _ExcelBookClose().

4. Iterate.

Good luck with your Excel endeavors!

-S

Edited September 25, 2007 by Locodarwin

[big_daddy]

@Locodarwin - You're always welcome to use any code I post. It could be expanded upon, and it definitely needs to be made more formal.

[gertsolo]

Thx Big Daddy,

More adam to ya.

GertSolo

[Skizmata]

Thanks so much Locodarwin it works wonderfully your fans eagerly await your next official release.

(In my opinion) A version of this UDF should be included in the AutoIt library. It is extremely useful.

[gertsolo]

Hey,

another one.

I'm using _ExcelFindInRange to find a certain value in a few cells. I know the value is stored in the array in $array[1][0].

Suppose the result was B32, how do I add a column to that B32 value in the array.

To be clear, the result was B32, but I want to write something in the cell next to that one, being C32.

thx,

[aslani]

I was having trouble opening a modified Excel Spread Sheet and the error message points me to here;

Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False)
    $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
    With $oExcel
        .Visible = $fVisible
        .WorkBooks.Open($sFilePath, Default, $fReadOnly)
        .ActiveWorkbook.Sheets(1).Select() ; <-----source of the error
    EndWith
    Return $oExcel
EndFunc ;==>_ExcelBookOpenoÝ÷ Ù(§qëaz«¨µ(^zÚ®¢Ø§,e³¬b~'(ê^ƧßÛlzW¶Ø^²µ*-+"³Z´bu«­¢+ÙÕ¹}á± ½½­=Á¸ ÀÌØíÍ¥±AÑ °ÀÌØíY¥Í¥±ôÄ°ÀÌØíI=¹±äô±Í¤($ÀÌØí½á°ô=©
ÉÑ ÅÕ½Ðíá°¹ÁÁ±¥Ñ¥½¸ÅÕ½Ðì¤(%%9=P%Í=¨ ÀÌØí½á°¤Q¡¸IÑÕɸMÑÉÉ½È Ä°À°À¤(%%9=P¥±á¥ÍÑÌ ÀÌØíÍ¥±AÑ ¤Q¡¸IÑÕɸMÑÉÉ½È È°À°À¤(%%ÀÌØíY¥Í¥±ÐìÄQ¡¸ÀÌØíY¥Í¥±ôÄ(%%ÀÌØíY¥Í¥±±ÐìÀQ¡¸ÀÌØíY¥Í¥±ôÀ(%]¥Ñ ÀÌØí½á°($$¹Y¥Í¥±ôÀÌØíY¥Í¥±($$¹]½É­    ½½­Ì¹=Á¸ ÀÌØíÍ¥±AÑ °Õ±Ð°ÀÌØíI=¹±ä¤($$ì¹Ñ¥Ù]½É­½½¬¹M¡ÑÌ Ä¤¹M±Ð ¤($$¹Ñ¥Ù]½É­½½¬¹Ñ¥ÙM¡Ð¹M±Ð ¤(%¹]¥Ñ (%IÑÕɸÀÌØí½á°)¹Õ¹ìôôÐí}á±    ½½­=Á¸

It works, for now, since it will not allow me to select from other sheets. :/

Is there another way of doing this?

What if "Sheet 1" was renamed to "Cover Page"? And "Sheet 2" was renamed to "Prospectus"?

I'm just starting to understand the Excel .xls structure so I'm a little slow.

[Wooltown]

Is there a way to set the cell content to numeric and set the number of decimals ?

Tried to look around for a solution, but didn't find any, looked at Microsoft's pages, but didn't get any idea how to solve the problem.

[Pfex]

Hello I have the following problem.

Until now I was using the Randallc's ExcelCom to read data out of a file that the users where using to keep track of some data. The thing is , that you did not have to save the excel file every time you added some data, and ExcelCom read the up-to-date information of the update cells. The only "problem" was that it returned an error when any cell was still edited.

I tried to recreate this using this new UDF. I Tried to open the file as normally ( not read ) and save it immediately, tried without success to overwrite it with the _ExcelBookSaveAs but was still unable to achieve anything. Please keep in mind that normally, the XLS file on that the users work and on that they use my small too is usually open all the time.

Any suggestions of things that I might try out?

[beginner]

Thanks locodarwin...

i really need it......

[sanchopanza]

Hi,

I´m trying to code a little script for searching values in an excel sheet.

I have a excel sheet and in the Range D2:D40 is the string "821940" I want to search.

#include <ExcelCOM_UDF.au3>
#include <Array.au3>

$Excel =  _ExcelBookOpen(@ScriptDir & "\L27.xls", 1,False)

$array = _ExcelFindInRange($Excel, "821940", "D2:D40",1,1,1,0,2,False,False)
_ArrayDisplay($array,"RESULT")

_ExcelBookClose($Excel)

It opens the excel sheet but I get always an error

F:\TEMP PLATTE\AutoIt\excel\ExcelCOM_UDF.au3 (1096) : ==> The requested action with this object has failed.: 
$oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default, $fMatchFormat) 
$oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default, $fMatchFormat)^ ERROR

I don´t know where the problem is - can anyone help me ?

[laffo16]

I have a table of data, an Index in column A. Records are constantly added to this table and i require a function to as quickly as possible determin how many records are in the table. simple enough, but which is the quickest way of doing this? currently i...

Func _find_row_max()
    Global $row_max = 0
    While 1
        $row_max = $row_max + 1
        $cell = _ExcelReadCell ($oExcel, "A" & $row_max)
        If $cell = "" Then
            ExitLoop
        EndIf
    WEnd
EndFunc

[smooth]

hi you may want to use this function; $array = _ExcelSheetUsedRangeGet($oExcel, $vSheet).

; 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

[laffo16]

thanks smooth, that way is much quicker. i havent had a look to see if their are any other excel udf's around, i'm quite happy with this one but do the others have any benifits?

i'm currently looking to increase the speed in which i'm capturing bulk data from excel. my (not full) code is as follows:

; _excelreadcell messures at 1.55 secs
        ;Global $allsec_draw[$row_max - 2][6]
        ;$i = 1
        ;For $i = 1 to $row_max - 3
        ;   $allsec_draw[$i][0] = _ExcelReadCell ($oExcel, "E" & $i + 2)
        ;   $allsec_draw[$i][1] = _ExcelReadCell ($oExcel, "F" & $i + 2)
        ;   $allsec_draw[$i][2] = _ExcelReadCell ($oExcel, "D" & $i + 2)
        ;   $allsec_draw[$i][3] = _ExcelReadCell ($oExcel, "H" & $i + 2)
        ;   $allsec_draw[$i][4] = _ExcelReadCell ($oExcel, "K" & $i + 2)
        ;   $allsec_draw[$i][5] = _ExcelReadCell ($oExcel, "L" & $i + 2)
        ;Next
        ;_ArrayDisplay($allsec_draw, "1")
        
        ; _excelreadarray messures at 1.4 secs
        $allsec_new1 = _ExcelReadArray($oExcel, 3, 5, $row_max - 3, 1, 1)
        $allsec_new2 = _ExcelReadArray($oExcel, 3, 6, $row_max - 3, 1, 1)
        $allsec_new3 = _ExcelReadArray($oExcel, 3, 4, $row_max - 3, 1, 1)
        $allsec_new4 = _ExcelReadArray($oExcel, 3, 8, $row_max - 3, 1, 1)
        $allsec_new5 = _ExcelReadArray($oExcel, 3, 11, $row_max - 3, 1, 1)
        $allsec_new6 = _ExcelReadArray($oExcel, 3, 12, $row_max - 3, 1, 1)
        Global $allsec_draw[$row_max - 2][6]
        $i = 1
        For $i = 1 to $row_max - 3
            $allsec_draw[$i][0] = $allsec_new1[$i]
            $allsec_draw[$i][1] = $allsec_new2[$i]
            $allsec_draw[$i][2] = $allsec_new3[$i]
            $allsec_draw[$i][3] = $allsec_new4[$i]
            $allsec_draw[$i][4] = $allsec_new5[$i]
            $allsec_draw[$i][5] = $allsec_new6[$i]
        Next
        ;_ArrayDisplay($allsec_draw, "2")

i will try to excelcopy method, see if it returns better results. any news/updates on the excelprint function?

[laffo16]

i know the code isnt v good but for reading bulk data, from excel, i would recommened _excelcopy (messured at 0.05 secs to complete the same task as above)

_ExcelCopy($oExcel, 3, 5, $row_max - 1, 5)
        $allsec_new1 = StringSplit(ClipGet(), @LF)
        $allsec_new1 = _ArrayTrim($allsec_new1, 1, 1, 1)
        _ExcelCopy($oExcel, 3, 6, $row_max - 1, 6)
        $allsec_new2 = StringSplit(ClipGet(), @LF)
        $allsec_new2 = _ArrayTrim($allsec_new2, 1, 1, 1)
        _ExcelCopy($oExcel, 3, 4, $row_max - 1, 4)
        $allsec_new3 = StringSplit(ClipGet(), @LF)
        $allsec_new3 = _ArrayTrim($allsec_new3, 1, 1, 1)
        _ExcelCopy($oExcel, 3, 8, $row_max - 1, 8)
        $allsec_new4 = StringSplit(ClipGet(), @LF)
        $allsec_new4 = _ArrayTrim($allsec_new4, 1, 1, 1)
        _ExcelCopy($oExcel, 3, 11, $row_max - 1, 11)
        $allsec_new5 = StringSplit(ClipGet(), @LF)
        $allsec_new5 = _ArrayTrim($allsec_new5, 1, 1, 1)
        _ExcelCopy($oExcel, 3, 12, $row_max - 1, 12)
        $allsec_new6 = StringSplit(ClipGet(), @LF)
        $allsec_new6 = _ArrayTrim($allsec_new6, 1, 1, 1)
        Global $allsec_draw[$row_max - 2][6]
        $i = 1
        For $i = 1 to $row_max - 3
            $allsec_draw[$i][0] = $allsec_new1[$i]
            $allsec_draw[$i][1] = $allsec_new2[$i]
            $allsec_draw[$i][2] = $allsec_new3[$i]
            $allsec_draw[$i][3] = $allsec_new4[$i]
            $allsec_draw[$i][4] = $allsec_new5[$i]
            $allsec_draw[$i][5] = $allsec_new6[$i]
        Next

[laffo16]

is their anyway to check if the document is currently being used and read only.

[laffo16]

sry, i found a solution here:

http://www.autoitscript.com/forum/index.ph...mp;hl=excom.au3

although it would be nice to get the same result without having to open the ss.

[everseeker]

Good idea, thanks. I have help documentation scattered about in text files; when I get the time I'll move them to HTML and compile them into a .chm (I have the AutoIt helpfile source templates). I'll also create the text files JdeB requires for UDF submission as time permits.

I keep a custom au3.user.calltips.api file going for my own use during development; I'll get that out the door soon as well.

Heck, I still have to publish more examples scripts.

-S

Has there been ...progress... on this front?

[laffo16]

i was wonderng if anyone can help me with this issue,

in the following code:

Send("{PAUSE}")
$file_path = "C:\Documents and Settings\dean\Desktop\GOLD\blah.xls"
Global $oExcel = _ExcelBookOpen ($file_path, 1)
_ExcelBookClose($oExcel, 1)
Send("{PAUSE}")

i want to be able to open excel doc's after the _ExcelBookClose($oExcel, 1), so i pause the script to attempt to do this, but i dont understand when on the 2nd Pause when i open the doc i get a transparrent looking window with no cell's. i will take screen shots if need be, but i was hoping someone may know how to fix this. (using excel 2000)

[laffo16]

To try and be more clear,

can someone else please try this, try to open any excel file after a _ExcelBookClose($oExcel, 1) function has been called, but before the autoit program has Exit (or finished), eg its locked in a loop. when the book is opened, no cells can be seen, the window is transparrent. after the autoit program Exits, books are restored to their normal view. help?

[PsaltyDS]

i was wonderng if anyone can help me with this issue,

in the following code:

Send("{PAUSE}")
$file_path = "C:\Documents and Settings\dean\Desktop\GOLD\blah.xls"
Global $oExcel = _ExcelBookOpen ($file_path, 1)
_ExcelBookClose($oExcel, 1)
Send("{PAUSE}")oÝ÷ Ú,Ûhmæëh¢§{ÚßÛ~׫¶ÅÇ¥$
Z,{Mú Lz]l¢*ZºÇ­ë®*m¶­µé©¶Ú¢Øb±»­Ú'¶éÝz»-jwpéèØ^ÚwOjë§^Ø^v"ëZ¶¶§²«­éí$x0whízzYwöÈ°YmjG¬r·Èh¶Èçmæî¶,²)x,¢g¨éÊIèÂ0¶âÆØb²ë"±qévÓM?ªê-z«¨µéÚ