Jump to content

Yet Another -- ExcelCOM UDF


Locodarwin
 Share

Recommended Posts

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:

;===============================================================================
;
; 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 by Locodarwin
(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites

  • Replies 379
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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,

The more you learn, the less you know.

Link to comment
Share on other sites

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.

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Link to comment
Share on other sites

  • 4 weeks later...
  • 2 weeks later...

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?

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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?

Everseeker

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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]*ZºÇ­ë®*m¶­µé©¶Ú¢Øb±»­Ú'¶éÝz»-jwpéèØ^ÚwOjë§^Ø^v"ëZ¶¶§²«­éí$x0whízzYwöÈ°YmjG¬r·Èh¶Èçmæî¶,²)x,¢g¨éÊIèÂ0¶âÆØb²ë"±qévÓM?ªê-z«¨µéÚ

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...