Using AutoIT to pull specific numbers from spreadsheet

[water]

If the Workbook has 15 columns then you need to specify 14 because it is not the index (starts with 1) but the offset (starts with 0).

[JadeRae]

@iamtheky  I added that check, but I'm still getting an error on line 28.  I believe it has to do with the '6' that I have entered, but I'm not sure what that number references, so not sure what to change it to.

[water]

Can you please post the full error message you see in the SciTE output pane? We need the script line in error plus the error message.

[iamtheky]

That 6, is specifying Column G from spreadsheet (as @water mentioned the array is 0-based), and the $i is looping down the rows.  You should liberally use msgbox to check the contents of every variable you are unsure of.

[JadeRae]

Here is the error.  In the actual report the ANI data is in column 'P', so I have it marked as 15 in the script.

[JadeRae]

Just now, JadeRae said:

Here is the error.  In the actual report the ANI data is in column 'P', so I have it marked as 15 in the script.

 

Error.bmp

[water]

My bad
Line

_NewCustomer($aRecords[$i][6], $iCount)

should be

_NewCustomer($aRecords[$i-1][6], $iCount)

and I think you should change 6 to 15 as this is the column where the ANI number is stored.

[JadeRae]

Ok, it is running without errors now.  Thank you @water!!

So now my next question.  Is there a way that I can reference each of the 7 numbers that it is searching for to a name?  So that when I have it output how many of each number there is it will the name instead of the phone number?

 

[water]

Where do you store the names now? IIRC there is no name in the Excel file you posted.

Edit: Or are you talking about the agent name?

Edited May 17, 2016 by water

[JadeRae]

I just have the names written down right now, they are not stored anywhere in the Excel document.

[JadeRae]

I remember with VBS there was a way to 'tag' a number with a text string that it would then output instead of the number, it was a manual process, but since I won't be having to to change these numbers (hopefully...unless someone gets fired...) I don't mind doing a little more up front manual work in order to expedite the process long term.

[water]

I suggest you create a file in CSV format holding the number and the name: number, "name"
This way you can always add/remove/change entries without having to change the script.

 

Edited May 17, 2016 by water

[JadeRae]

Ok, CSV file has been created, that does make a lot more sense than having to change the script.  So now the last step, (I believe) is having it reference this CSV file, and then output the data in a readable format showing Name and # of times called as recorded in the spreadsheet.

Any pointers to get me started with this would be appreciated, you have already helped me a ton, I'm still waiting on our local IT to get back to me after submitting a request yesterday morning...at this rate I'll be completely done before they even get around to helping me haha

[water]

Fine

I will use code from post #19 to add the functionality.
Do you use "," or ";" as eparator in your CSV file?

[JadeRae]

I used ","

 

You're awesome

[JadeRae]

Here is the most current version of what I have:

expandcollapse popup

;Pull Phone Numbers and Print Data

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>

Local $aANINumbers[7] = ["6412200969", "6018740303", "3176174256", "4026460312", "9523220388", "3143717106", "6125702362"]
Local $sPrevNumber = "", $iCount = 0

    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Pioneer Phone Report", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;notify if cannot start Excel
        Exit
    EndIf

Local $oExcel = _Excel_Open() ; Start Excel or connect to a running instance
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\ENSXI\Desktop\sheet1.xlsx") ; Open the workbook

    If @error Then ;Notify if cannot find specific file
        MsgBox($MB_SYSTEMMODAL, "Pioneer Phone Report", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

Local $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array
If UBound($aRecords, 2) <16 Then; Notify if their are not at least 15 columns
        MsgBox ($MB_SYSTEMMODAL, "Pioneer Phone Report", "There are not at least 16 Columns." & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf
    _ArraySort($aRecords, 0, 0, 0, 15) ; Sort the array on column 16 (0 = first column)

For $i = 0 to UBound($aRecords, 1) - 1
    If $sPrevNumber <> $aRecords[$i][15] Then ; Number has changed
        If $sPrevNumber <> "" Then _NewCustomer($sPrevNumber, $iCount)
        $sPrevNumber = $aRecords[$i][15]
        $iCount = $iCount + 1
    EndIf
Next
_NewCustomer($aRecords[$i-1][15], $iCount) ; Process last customer
Exit

Func _NewCustomer($sANI, ByRef $iCount)
    $iIndex = 0
    For $iIndex = 0 to UBound($aANINumbers, 1) - 1
        If $aANINumbers[$iIndex] = $sANI Then
            ConsoleWrite("ANI: " & $sANI & ", Count: " & $iCount & @CRLF)
            ExitLoop
        EndIf
    Next
    $iCount = 0
EndFunc

 

[water]

Copied all your changes from above to my newest version. Hope I got it right

expandcollapse popup

;Pull Phone Numbers and Print Data

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <File.au3>

Local $aANINumbers[7] = ["6412200969", "6018740303", "3176174256", "4026460312", "9523220388", "3143717106", "6125702362"]
Local $sPrevNumber = "", $iCount = 0
Local $sWorkbook = "C:\Users\ENSXI\Desktop\sheet1.xlsx" ; Path of the workbook to be analyzed
Local $sANINames = "C:\Users\ENSXI\Desktop\ANINames.csv" ; Path of the CSV file with ANI numbers and names
Local $sScriptTitle = "Pioneer Phone Report" ; Script title for MsgBox
Local $aANINames
_FileReadToArray($sANINames, $aANINames, $FRTA_NOCOUNT, ",") ; Split the file into a 2D array. Separator is ","
If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error opening file '" & $sANINames & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
If UBound($aANINames, 0) <> 2 Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "File '" & $sANINames & "' is not a 2D array.")
Local $oExcel = _Excel_Open() ; Start Excel or connect to a running instance
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) ; Open workbook
If @error Then ; Notify if cannot find specific file
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error opening workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
Local $aRecords = _Excel_RangeRead($oWorkbook) ; Reads the whole workbook into the array
If @error Then
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error reading workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
If UBound($aRecords, 2) < 16 Then; Notify if there are not at least 15 columns
    MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "There are not at least 16 columns in workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
_ArraySort($aRecords, 0, 0, 0, 15) ; Sort the array on column 15 (0 = first column)
For $i = 0 To UBound($aRecords, 1) - 1
    If $sPrevNumber <> $aRecords[$i][15] Then ; Number has changed
        If $sPrevNumber <> "" Then _NewCustomer($sPrevNumber, $iCount)
        $sPrevNumber = $aRecords[$i][15]
        $iCount = $iCount + 1
    EndIf
Next
_NewCustomer($aRecords[$i-1][15], $iCount) ; Process last customer
Exit

Func _NewCustomer($sANI, ByRef $iCount)
    Local $iIndex = 0, $sName = "** No Name Found **"
    For $iIndex = 0 To UBound($aANINumbers, 1) - 1
        If $aANINumbers[$iIndex] = $sANI Then ; Only display results for selected ANINumbers
            For $iIndex2 = 0 To UBound($aANINames, 1) - 1 ; Grab the name for the ANINumber
                If $aANINames[$iIndex2][0] = $sANI Then
                    $sName = $aANINames[$iIndex2][1]
                    ExitLoop
                EndIf
            Next
            ConsoleWrite("ANI: " & $sANI & ", Name: " & $sName & ", Count: " & $iCount & @CRLF)
            ExitLoop
        EndIf
    Next
    $iCount = 0
EndFunc   ;==>_NewCustomer

 

[JadeRae]

Ok, so I copied the above, changed the file path's so that everything matched, and when I go to run it my excel file opens and then nothing happens.  No errors or anything.

[water]

Please add

AutoItSetOption("TrayIconDebug", 1)

at the top of your script. So we can see where the script loops.
When you move the mouse over the AutoIt icon in the taskbar you get the statement currently being executed.

[JadeRae]

So it's probably something I'm doing wrong, but I placed that at the beginning of the script and I'm not getting anything showing up in the tray.