Sign in to follow this  
Followers 0
treefingers

ExcelCOM_UDF question

8 posts in this topic

Hello,

I have a script that I'm working on, and have had previous help before on, and would like some more help on :).

First off, here's what I have:

CODE
MsgBox(64, "Notice","Navigate to the Completed Order Re-Print Screen in STORIS Before Continuing")

$fileVariableName = FileOpen("invoices.txt", 0)

If $fileVariableName = -1 Then

MsgBox(0,"Failure","Failed to open file")

Exit

EndIf

While 1

For $x = 1 to 50

$lineVariableName = FileReadLine($fileVariableName)

If @error = -1 Then ExitLoop 2

WinActivate("Completed Order Re-Print")

Send($lineVariableName)

Sleep(500)

Send("{ENTER}")

Sleep(500)

Send("!a")

Sleep(1000)

Next

Send("!r")

Sleep(3500)

Send("{SPACE}")

Sleep(16000)

Send("{Space}")

Sleep(10000)

Send("COR")

Send("{ENTER}")

Sleep(5000)

Send("{TAB}")

WEnd

FileClose($fileVariableName)

Basically what this does is open our POS (Point of sale, but yes, the other acronym is correct) software, and input a bunch of tax exempt invoices to reprint.

Our software actually creates an Excel document containing all the invoice numbers that need to be reprinted. What I have been doing is opening this document, copying the first column, pasting to "invoices.txt", then saving the file, closing excel, and running the script. What I would like to do is have autoit read directly from the excel document.

I looked through the ExcelCOM_UDF, and found the _ExcelReadArray Function, which I imagine is a good place to start. What I'm not sure I understand, is how to modify the function to read an unknown number of entries. There could be 40 invoices (rows) or 500. I need it to read how ever many are there, save it to an array, then feed the array into the script that does this tedious work.

Any advice?

Thanks!

Share this post


Link to post
Share on other sites



Assuming $oExcel is your Excel object, you can find the number of rows used using COM in the following manner:

$LastRow = $oExcel.ActiveSheet.UsedRange.Rows.Count

Perhaps that should get you started, but let me know if I can be of further assistance.

Share this post


Link to post
Share on other sites

You could try this:

#include "ExcelCOM_UDF.au3"
#include 

$Obj = _ExcelBookOpen(@ScriptDir & "\test.xls", 0, False)

For $X = 1 to $Obj.ActiveSheet.UsedRange.Rows.Count
    ;Object,StartRow,StartColumn,NumCells,Direction,Index
    $ARRAY = _ExcelReadArray($Obj, $X, 1, 2, 0, 0)  
    _ArrayDisplay ($ARRAY)
Next

_ExcelBookClose($Obj)

My xls has only 2 columns:

23525245 Bob

32423252 Steve

24532534 George

etc...

Share this post


Link to post
Share on other sites

Hello,

I have a script that I'm working on, and have had previous help before on, and would like some more help on :).

First off, here's what I have:

Basically what this does is open our POS (Point of sale, but yes, the other acronym is correct) software, and input a bunch of tax exempt invoices to reprint.

Our software actually creates an Excel document containing all the invoice numbers that need to be reprinted. What I have been doing is opening this document, copying the first column, pasting to "invoices.txt", then saving the file, closing excel, and running the script. What I would like to do is have autoit read directly from the excel document.

I looked through the ExcelCOM_UDF, and found the _ExcelReadArray Function, which I imagine is a good place to start. What I'm not sure I understand, is how to modify the function to read an unknown number of entries. There could be 40 invoices (rows) or 500. I need it to read how ever many are there, save it to an array, then feed the array into the script that does this tedious work.

Any advice?

Thanks!

This works as example of using the ExcelCOM_UDF functions to read one entire column ('B' in the example) to an array. Others have posted examples using the object references more directly, this uses only the _Excel* functions and has lots of error reporting added for help in debugging:

#include <array.au3>
#include <ExcelCOM_UDF.au3>

$sXLS = @ScriptDir & "\Test2.xls"

; Open Excel book
$oExcel = _ExcelBookOpen ($sXLS)
If Not @error Then
    $avExtent = _ExcelSheetUsedRangeGet ($oExcel, 1)
    If Not @error Then
        $avColData = _ExcelReadArray ($oExcel, 1, 2, $avExtent[3], 1, 1)
        If Not @error Then
            _ArrayDisplay($avColData, "Debug: $avColData")
        Else
            ConsoleWrite("Debug: Failed to read column, @error = " & @error & @LF)
        EndIf
    Else
        ConsoleWrite("Debug: Failed to get extent, @error = " & @error & @LF)
    EndIf
Else
    ConsoleWrite("Debug: Failed to open Excel book: " & $sXLS & @LF)
EndIf

;)


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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Okay, here's what I've got so far, thanks to all the help:

#include <array.au3>
#include <ExcelCOM_UDF.au3>

$sXLS = @ScriptDir & "\TaxExempt.xls"

; Open Excel book
$oExcel = _ExcelBookOpen ($sXLS)
$avExtent = _ExcelSheetUsedRangeGet ($oExcel, 1)
$avColData = _ExcelReadArray ($oExcel, 2, 1, $avExtent[3], 1, 1)


While 1
For $xx = 1 To Ubound($avColData) - 1
    For $x = 1 to 50
        WinActivate("Completed Order Re-Print")
        Send($avColData[$xx])
        Sleep(500)
        Send("{ENTER}")
        Sleep(500)
        Send("!a")
        Sleep(1000)
    Next
    Send("!r")
    Sleep(3500)
    Send("{SPACE}")
    Sleep(16000)
    Send("{Space}")
    Sleep(10000)
    Send("COR")
    Send("{ENTER}")
    Sleep(5000)
    Send("{TAB}")   
WEnd

It is working, however, it is only inputting the first line of the array, 50 times. How to I make it sequentially read the array column, in sets of 50?

Edited by treefingers

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Okay, here's what I've got so far, thanks to all the help:

While 1
For $xx = 1 To Ubound($avColData) - 1
    For $x = 1 to 50
        ;
    Next
WEnd

It is working, however, it is only inputting the first line of the array, 50 times. How to I make it sequentially read the array column, in sets of 50?

You are showing two For's and only one Next, all inside a While/WEnd loop. What was the plan with those nested loop?

:)

Edited by PsaltyDS

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

Share this post


Link to post
Share on other sites

You are showing two For's and only one Next, all inside a While/WEnd loop. What was the plan with those nested loop?

:)

Yeah, now i see the lack of the second next..

Forgive my lack of programming skills- really, i'm not entirely sure.

I need the array to be 'sent' sequentially, in increments of 50. Our POS software crashes if i try to print more than 50 documents at a time.

Let me try and do kind of a step by step.

#include <array.au3>
#include <ExcelCOM_UDF.au3>

$sXLS = @ScriptDir & "\TaxExempt.xls"

; Open Excel book
$oExcel = _ExcelBookOpen ($sXLS)
$avExtent = _ExcelSheetUsedRangeGet ($oExcel, 1)
$avColData = _ExcelReadArray ($oExcel, 2, 1, $avExtent[3], 1, 1)oÝ÷ Ù8b²Z(¦wvËhmçh觱ël¡þtjYmì!W¬z«Ø«y§ba{Mújð¨6­iÖ­i«­¢+ÙM¹ ÅÕ½ÐìÌÌíÈÅÕ½Ðì¤)M±À ÌÔÀÀ¤)M¹ ÅÕ½ÐííMA
ôÅÕ½Ðì¤)M±À ÄØÀÀÀ¤)M¹ ÅÕ½ÐííMÁôÅÕ½Ðì¤)M±À ÄÀÀÀÀ¤)M¹ ÅÕ½Ðí
=HÅÕ½Ðì¤)M¹ ÅÕ½Ðíí9QIôÅÕ½Ðì¤)M±À ÔÀÀÀ¤)M¹ ÅÕ½ÐííQ ôÅÕ½Ðì

After every 50 orders, that process needs to be run (which actually prints the set of 50).

I'm not really sure how to loop all that.. so I kind of.. guessed. Heh.

Share this post


Link to post
Share on other sites

OK, that makes sense...

#include <array.au3>
#include <ExcelCOM_UDF.au3>

$sXLS = @ScriptDir & "\TaxExempt.xls"

; Open Excel book
$oExcel = _ExcelBookOpen ($sXLS)
$avExtent = _ExcelSheetUsedRangeGet ($oExcel, 1)
$avColData = _ExcelReadArray ($oExcel, 2, 1, $avExtent[3], 1, 1)

$iCnt = 0
For $x = 1 To UBound($avColData) - 1
    $iCnt += 1
    WinActivate("Completed Order Re-Print")
    Send($avColData[$x])
    Sleep(500)
    Send("{ENTER}")
    Sleep(500)
    Send("!a")
    Sleep(1000)
    If $iCnt = 50 Then
        _RePrint()
        $iCnt = 0
    EndIf
Next
If $iCnt Then _RePrint()


; -----------------------
; Function _RePrint()
; -----------------------
Func _RePrint()
    Send("!r")
    Sleep(3500)
    Send("{SPACE}")
    Sleep(16000)
    Send("{Space}")
    Sleep(10000)
    Send("COR")
    Send("{ENTER}")
    Sleep(5000)
    Send("{TAB}")
    Sleep(2000)
EndFunc   ;==>_RePrint

Every time $iCnt gets to 50, it runs _RePrint() and resets $iCnt to 0. After the loop is done, if $iCnt indicates more were done since the last 50, then it runs _RePrint() one more time.

:)


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

Share this post


Link to post
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
Sign in to follow this  
Followers 0