Need data from different cells in Excel to populate into a looping script

[rac93]

Greetings all.  I've got an AutoIT script that "works", but it's not very efficient.  This is what I'm trying to do:

I've got an Excel spreadsheet with computer names (Column A) and a description of that computer (Column B).  I need to add this data (from cells A2 and B2, for example) to another program, using a function.  I would like this particular function to repeat, using the next two cells on the spreadsheet (A3 and B3), and so on, until there are no more computer names on the list to add.

The way that I have it working at this time is by copying the same functions (under a different name each time), for every line in Excel.  (In a nutshell, I have 25 copies of the same functions with minor changes, to add 25 PCs.)  This makes the script way longer than it should be and it's a pain, if I have to change something in a particular function, since I have to make the change 25 times...

Also, the script adds data from the added PCs to a comma separated text file, so I know which PC was added where.

Note:  The script starts with a GUI, because when I first started, my thought was to add one PC at a time, using the GUI.  (That worked pretty well.)  I later decided to get the data directly from Excel, but I left the (modified) GUI in place.

Attached is a part of the script.  I appreciate any help that you can provide.  I've spent at least 10 hours trying to figure this out.

 

Add PC Script.au3

[argumentum]

;-----------------------------------------------------------
;  *** SAVE FILE DATA ***
;-----------------------------------------------------------

Func PC_Log_A2($SaveLocation, $DataFromUser_1, $Facility, $DataFromUser_3)
    If StringInStr($DataFromUser_1, ",") Then $DataFromUser_1 = '"' & $DataFromUser_1 & '"'
    If StringInStr($Facility, ",") Then $Facility = '"' & $Facility & '"'
    If StringInStr($DataFromUser_3, ",") Then $DataFromUser_3 = '"' & $DataFromUser_3 & '"'

    Local $sCSV_string = $DataFromUser_1 & ', ' & $Facility & ', ' & $DataFromUser_3 & @CRLF

    FileWrite($SaveLocation, $sCSV_string)

;~  FileWrite($SaveLocation, $DataFromUser_1)
;~  FileWrite($SaveLocation, ', ')
;~  FileWrite($SaveLocation, $Facility)
;~  FileWrite($SaveLocation, ', ')
;~  FileWrite($SaveLocation, $DataFromUser_3)
;~  FileWrite($SaveLocation, @CRLF)

;~  StartMT_A3()

EndFunc   ;==>PC_Log_A2

..yes, there are things that could be written more efficiently.  You should read up about for loops and function parameters.

[ioa747]

since the GUI is not necessary I removed it for simplicity, but I was missing $Radio_1, $Radio_2 data
so I integrated them into Excel (Column C)

PcName  PcDescripion    Site
\\PC_01 computer  01    DAL SITE
\\PC_02 computer  02    GSW SITE
\\PC_03 computer  03    DAL SITE
\\PC_04 computer  04    DAL SITE
\\PC_05 computer  05    GSW SITE

 

examine the following script as to the methodology, i.e. how it can work in a loop with the same functions

expandcollapse popup

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

HotKeySet("^c", "SC_Exit")

AutoItSetOption("SendKeyDelay", 50)

Global $var = @ScriptDir & "\PC_Names.xlsx"        ;"C:\Users\Public\MT Script\PC_Names.xlsx"
Global $SaveLocation = @ScriptDir & "\PC_Log.txt"  ;"C:\Users\Public\MT Script\PC_Log.txt"
Global $oExcel_A2 = _Excel_Open()
Global $oWorkBook = _Excel_BookOpen($oExcel_A2, $var)
Global $aExcel = _Excel_RangeRead($oWorkBook, 1)
;~ _ArrayDisplay($aExcel, "$aExcel")

For $i = 1 To UBound($aExcel) - 1
    ConsoleWrite($i & ") " & $aExcel[$i][0] & " - " & $aExcel[$i][1] & @CRLF)
    StartMT($aExcel[$i][0], $aExcel[$i][1], $aExcel[$i][2])
    PC_Log($aExcel[$i][0], $aExcel[$i][2])
Next

Exit

;--------------------------------------------------------------------------------------------
Func StartMT($sPcName, $sPcDescripion, $sSite)   ;  *** APP INTERACTION ***  (REPEATS BELOW)

    Local $hGCR = WinActivate("GCR")

    If WinActive($hGCR) Then
        Sleep(2000)
        Send($sPcName) ;In the copies, I replace this with "$ExcelRead_A3" or "$ExcelRead_A4", etc.
        Sleep(1000)
        Send("{ENTER}")
        Sleep(2000)
        Send("Y")
        Sleep(1000)
        Send("{ENTER 2}")
        Sleep(1000)
        Send($sPcDescripion) ;In the copies, I replace this with "$ExcelRead_B3" or "$ExcelRead_B4", etc.
        Sleep(1000)
        Send("{ENTER}")
        Sleep(1000)
        Send("SUMMER 24")
        Sleep(1000)
        Send("{ENTER 7}")
        Sleep(1000)
        Send("Y")
        Sleep(1000)
        Send("{ENTER 2}")
        Sleep(1000)
        Send("Y")
        Sleep(1000)
        Send("{ENTER}")
        Sleep(1000)
        Send($sSite)
        Sleep(1000)
        Send("{ENTER}")
        Sleep(1000)
        Send("DFW METROPLEX")
        Sleep(1000)
        Send("{ENTER}")
        Sleep(1000)
        Send("LOGIN")
        Sleep(1000)
        Send("{ENTER 2}")
        Sleep(1000)
        Send("Y")
        Sleep(1000)
        Send("{ENTER}")
        Sleep(1000)
        Send("2")
        Sleep(1000)
        Send("{ENTER 2}")

    Else
        MsgBox(16, "!! @error !!", "there not exist GCR window")
        Exit
    EndIf

EndFunc   ;==>StartMT
;--------------------------------------------------------------------------------------------
Func PC_Log($sPcName, $sSite)                    ;  *** SAVE FILE DATA ***

    Local $Facility
    Switch $sSite
        Case "DAL SITE"
            $Facility = "Dallas"
        Case "GSW SITE"
            $Facility = "Fort Worth"
    EndSwitch

    FileWrite($SaveLocation, $sPcName & ', ' & $Facility & ', ' & $sSite & @CRLF)

EndFunc   ;==>PC_Log
;--------------------------------------------------------------------------------------------
Func SC_Exit()                                   ;  *** EMERGENCY EXIT MESSAGE ***
    MsgBox(16, "Force Quit", "The script has been CTRL+C terminated.")
    Exit
EndFunc   ;==>SC_Exit
;--------------------------------------------------------------------------------------------

 

[rac93]

Thank you argumentum and ioa747.  I sincerely appreciate your response and assistance with this issue.

ioa747, I tried to get the code improvements that you provided to work.  I'm not understanding what I'm missing / doing wrong.  I've read various AutoIT pages where they explain a function.  Some pages make sense to me, but others don't.  (I feel like a dummy...)  I'm going to search and see if there are any videos online that can better explain some of the parts that I'm having trouble with.  A video might be easier to follow along with.

[ioa747]

feel free to ask anything you need further clarification

[rac93]

1 hour ago, ioa747 said:

feel free to ask anything you need further clarification

Will do.  Thanks again!