water Posted May 19, 2016 Share Posted May 19, 2016 OK, quick and dirty: The result gets displayed using _Arraydisplay. When you press "Run user Func" the array gets written to a new sheet in the open Excel workbook and the workbook gets displayed. expandcollapse popup; Extract Contact Data v2 ; Pull Phone Numbers and Print Numbers of times they called in #include <Excel.au3> #include <MsgBoxConstants.au3> #include <Array.au3> #include <File.au3> AutoItSetOption("TrayIconDebug", 1) ; CONFIGURATION Local $sScriptTitle = "Pioneer Phone Report" ; Script title for MsgBox Local $sPrevNumber = "", $iCount = 0, $aANINumbers, $iResultCount = 0, $aResultArray[1000][3], $sFuncName = _CopyToExcel Local $iCol = 15 ; Offset of the column where ANI is stored (0 = column A) Local $sWorkbook = @ScriptDir & "C:\Users\ENSXI\Desktop\sheet1.xlsx" ; Path of the workbook to be analyzed Local $sANINames = @ScriptDir & "C:\Users\ENSXI\Documents\AutoIT\Scripts\Contact Names.csv" ; Path of the CSV file with technician numbers and names Local $aANINames Local $sANINumbers = InputBox($sScriptTitle, "Please enter the ANI numbers to process separated by a space:", "6125702362 9523220388 4026460312 3176174256 3143717106 6018740303 6412200969", "", 500, 130) If @error Then Exit ; Cancel button pressed $aANINumbers = StringSplit($sANINumbers, " ", $STR_NOCOUNT) ; Read User Names _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.") ; OPEN EXCEL WORKBOOK Local $oExcel = _Excel_Open(False) ; Start Excel in the background or connect to a running instance If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error starting Excel." & @CRLF & "@error = " & @error & ", @extended = " & @extended) 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 ; CHECK DATA APPEARS VALID If UBound($aRecords, 2) < $iCol + 1 Then ; Check the minimum number of needed columns MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "There are not at least " & $iCol + 1 & " columns in the workbook '" & $sWorkbook & "'.") _Excel_Close($oExcel) Exit EndIf ; SORT THE DATA SET _ArraySort($aRecords, 0, 1, 0, $iCol) ; Sort the array on the column with ANI (ignore heading line) ;Error if unable to sort If @error Then Exit MsgBox($MB_SYSTEMMODAL, $sScriptTitle, "Error sorting the input data." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; LOOP THROUGH ALL CALL RECORDS For $i = 1 To UBound($aRecords, 1) - 1 ; Ignore heading line If $sPrevNumber <> $aRecords[$i][$iCol] Then ; Number has changed If $sPrevNumber <> "" Then _NewCustomer($sPrevNumber, $iCount) $sPrevNumber = $aRecords[$i][$iCol] EndIf $iCount = $iCount + 1 Next _NewCustomer($aRecords[$i - 1][$iCol], $iCount) ; Process last customer ReDim $aResultArray[$iResultCount][3] ; Resize the result array _Excel_Close($oExcel, False) ;close out the excel window _Arraydisplay($aResultArray, $sScriptTitle, "", 64, Default, "ANI|Name|Count", Default, Default, $sFuncName) Exit Func _NewCustomer($sANI, ByRef $iCount) Local $iIndex = 0, $sName = "** No Name Found **" For $iIndex = 0 To UBound($aANINumbers, 1) - 1 ; loops through technicians 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 ; find technician's name $sName = $aANINames[$iIndex2][1] ; set the call record's name to be the technician's name ExitLoop EndIf Next ; Write the record to the result array $aResultArray[$iResultCount][0] = $sANI $aResultArray[$iResultCount][1] = $sName $aResultArray[$iResultCount][2] = $iCount $iResultCount = $iResultCount + 1 ExitLoop EndIf Next $iCount = 0 EndFunc ;==>_NewCustomer Func _CopyToExcel($aArray, $aSelectedItems) Local $aHeader[][] = [["ANI", "Name", "Count"]]; Add a sheet to the Excel workbook Local $oResultSheet = _Excel_SheetAdd($oWorkbook, -1, False, 1, "Result") ; Write the result to the new worksheet _Excel_RangeWrite($oWorkbook, $oResultSheet, $aHeader, "A1") _Excel_RangeWrite($oWorkbook, $oResultSheet, $aArray, "A2") ; Make Excel visible again $oExcel.Visible = True EndFunc coffeeturtle 1 My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
JadeRae Posted May 19, 2016 Author Share Posted May 19, 2016 (edited) Bahhh! It works! Thank you so much @water! One question What is the difference between copy data & HDR/row and Copy Data Only? edited: asked a dumb question... Edited May 19, 2016 by JadeRae Link to comment Share on other sites More sharing options...
water Posted May 19, 2016 Share Posted May 19, 2016 There are no dumb questions, only unasked questions coffeeturtle 1 My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
JadeRae Posted May 19, 2016 Author Share Posted May 19, 2016 haha, you had already 'Run User Func' does, and I asked it anyways...so perhaps it was more of a 'negligent' question. Thank you for all of your help @water. Seriously, this forum is awesome, my local IT STILL hasn't responded to me...and I asked them on Monday. I'm sure they'll follow up tomorrow afternoon just as I'm about to leave for the weekend... I do have one other question, that isn't really a big deal, since what I needed for the client I now have...but...for my own sake I was wondering: with the script that we have now, if I were to enter a much larger listing of numbers into the CSV file, and then only pull certain ones via the InputBox prompt would it only return results for the ones that I entered in the message box while still having a much larger repository of matched name/numbers in the CSV file? Link to comment Share on other sites More sharing options...
water Posted May 19, 2016 Share Posted May 19, 2016 Glad to be of service The script processes the whole file, sorts it on column P (the ANI number) and when the number changes, calls function _NewCustomer. If it is one of the customers you want to pull via InputBox then it is added to the array. Else it is ignored. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
JadeRae Posted May 19, 2016 Author Share Posted May 19, 2016 Perfect, that makes it very useful further down the road as well. (I'm sure in the future there will be more requests like this.) Now to run it on the BIG document!! Once again, thank you for all of you help on this, you have made my job 1000 times easier Link to comment Share on other sites More sharing options...
water Posted May 19, 2016 Share Posted May 19, 2016 If performance is too slow then there are other ways to process the workbook: Use _Excel_RangeFind to only select the rows you need Use ADO and process the workbook like an SQL database My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now