Jump to content

Copy & Paste from Excel In a Loop


Recommended Posts

HI! ... this is a big one (at least for me) :sweating:

You guys previously helped me copy the used range in column A and paste them into a Website one at a time in a loop. Cool! Now, for another function, I have 2 columns, A and B, and two input boxes in the Website. I'm having a hard time replicating the loop for the 2 columns. :frantics:

This is how I'm opening the Excel workbook (copied from the previous function that only had 1 column). I need to also get the used range in column B.

Func OpenExcelForCopy()

    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in column A:A
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:A"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
        $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i])
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")

    Finished()
EndFunc   ;==>OpenExcelForCopy

Then we eventually get here. I don't think anything needs to change here but I'm not sure. This is where I paste the data from Column A into an input field (which is a search tool in a website). If the search is good, then we get to the tricky part...

;~ OK, we logged in and we searched for a course. Lets COPY it!
Func CopyCourseBegin()
    Local $sResult
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)

;~  If the course was not found, do this.
    If $iSearchIndex = -1 Then
;~      MsgBox(4096, "Search Error", "Item not found")
        $sResult = "Source Not Found"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
;~  Now go back to the Excel sheet and search for the next one.

;~ If the course was found, begin the COPY process.
    Else
        For $i = 0 To UBound($aSearchItems) - 1
            $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i])
        Next
        $sResult = "Copied"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    EndIf
    Return $sResult
EndFunc   ;==>CopyCourseBegin

This is the "tricky part" where I'm confused. I can copy and paste what's in column A just fine, but I can't manage to replicate it for column B. I need to paste whats in Column B into "destinationCourseId"

;~ The course search was successful. COPY the course now.
Func CopyCourseNow($_sSearchResult)

;~  Navigate to the course copy page.
    _IENavigate($oIE, $urlBBCourseCopy)

;~  Copy the SOURCE course ID from the Excel sheet

;~ Paste whats copied from column A into the Source Course ID text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sSearchResult)

;~ Paste whats copied from column B into the Destination Course ID text box  ?!?!?!?!
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_sSearchResult)
    
 ;~ Just exit cause im stuck :(   
    _Exit()

EndFunc   ;==>CopyCourseNow

After I paste the data from column A into "sourceCourseId" and column B into "destinationCourseId", I'll make it do some stuff. Then I need it to loop around until the used ranges in column A & B is finished.

Does the entire code need to change now that there's two columns?

 

 

Link to post
Share on other sites

The array would become a 2d array $aSearchItems

$aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))

You can then obtain that information in a loop as follows:

For $i = 0 To Ubound($aSearchItems) - 1
    ;~ Column 0
    ConsoleWrite($aSearchItems[$i][0] & @CRLF)
    ;~ Column 1
    ConsoleWrite($aSearchItems[$i][1] & @CRLF)
    $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i][0], $aSearchItems[$i][1])
Next

For CopyCourseNow function use something like:

;~ The course search was successful. COPY the course now.
Func CopyCourseNow($_sourceCourseId, $_destinationCourseId)
Local $sResult
;~  Navigate to the course copy page.
    _IENavigate($oIE, $urlBBCourseCopy)

;~  Copy the SOURCE course ID from the Excel sheet

;~ Paste whats copied from column A into the Source Course ID text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sourceCourseId)

;~ Paste whats copied from column B into the Destination Course ID text box  ?!?!?!?!
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_destinationCourseId)
;~ .... Do stuff
Return $sResult
EndFunc   ;==>CopyCourseNow

You may need to post all your code, as the code is disjointed.

Edited by Subz
Link to post
Share on other sites

Yeaa I didn't do it right.. I'm getting errors with my SearchCourseForCopy function and CopyCourseNow function. It says its being called by a previous line with 2 args.

I'm unsure how to properly use functions like "CopyCourseNow($aSearchItems[$i])", like how it has arguments and stuff.. :huh2:

Here's everything

;~  The user clicked the "Copy Courses" button. This function will execute now.
Func _LoginForCopy()
;~  Disable the "Function" buttons again to prevent multiple processes.
    GUICtrlSetState($DeleteCourse, $GUI_DISABLE)
    GUICtrlSetState($CopyCourse, $GUI_DISABLE)
    GUICtrlSetState($CourseFunction3, $GUI_DISABLE)
    $CopyReady = MsgBox(52, "Ready to copy courses", "At any time, you can..." & @CRLF & "   • press the Pause key to pause the script (-__-)" & @CRLF & "   • press the F4 key to kill the app (x__x)" & @CRLF & @CRLF & "Shall we begin?  (o__o)")
    If $CopyReady == 6 Then
        GUICtrlSetBkColor($CopyCourse, 0x32CD32)
;~  Open a IE window and navigate to the login page.
        Global $oIE = _IECreate($urlBBLogin)
        _IELoadWait($oIE)
;~  Recognize the form on this page (login input boxes).
        Local $oForm = _IEFormGetObjByName($oIE, "login")
        Local $oTextLogin = _IEFormElementGetObjByName($oForm, "user_id")
        Local $oTextPass = _IEFormElementGetObjByName($oForm, "password")
;~  Enter the Automation user credentials into the form.
        _IEFormElementSetValue($oTextLogin, $bbUsername)
        _IEFormElementSetValue($oTextPass, $bbPassword)
;~  Click the Login button.
        _IEFormSubmit($oForm)
        _IELoadWait($oIE)
;~  Begin the next function. Go there.
        OpenExcelForCopy()
;~ If the user selects "No" then go back.
    Else
        GUICtrlSetState($DeleteCourse, $GUI_ENABLE)
        GUICtrlSetState($CopyCourse, $GUI_ENABLE)
        GUICtrlSetState($CourseFunction3, $GUI_DISABLE)
    EndIf
EndFunc   ;==>_LoginForCopy

;~  OK were logged in. Now copy the used range  in column A and column B from the Excel workbook.
Func OpenExcelForCopy()
    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B <---- it this right?
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
    $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i])
    Next
    For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
        ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
        ConsoleWrite($aSearchItems[$i][1] & @CRLF)
        $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i][0], $aSearchItems[$i][1])
;~ Were going to put the results of our work into Column C
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    Finished()
EndFunc   ;==>OpenExcelForCopy

;~  Now that were logged in, navigate to the course search page.
Func SearchCourseForCopy($aSearchItems[$i][0], $aSearchItems[$i][1])
    _IENavigate($oIE, $urlBBCourseSearch)
    _IELoadWait($oIE)
;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")
;~ Paste whats copied from Column A into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $_sSearchResult)
    _IEFormSubmit($oForm)
    _IELoadWait($oIE)
;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
;~  _ArrayDisplay($aBBTableData)
    Return CopyCourseBegin()
EndFunc   ;==>SearchCourseForCopy

;~ OK, we seacrhed for Column A...
Func CopyCourseBegin()
    Local $sResult
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)
;~  If the search was bad, go down to the next row in Column A.
    If $iSearchIndex = -1 Then
;~      MsgBox(4096, "Search Error", "Item not found")
        $sResult = "Source Not Found"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
;~  Now go back to the Excel sheet and search for the next one.

;~ If the search was good, begin the COPY process.
    Else
        For $i = 0 To UBound($aSearchItems) - 1
            $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i])
        Next
        $sResult = "Copied"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    EndIf
    Return $sResult
EndFunc   ;==>CopyCourseBegin

;~ The course search was successful. COPY now.
Func CopyCourseNow($_sourceCourseId, $_destinationCourseId)
    Local $sResult
;~  Navigate to the course copy page.
    _IENavigate($oIE, $urlBBCourseCopy)
;~ Paste whats copied from column A into the SOURCE Course ID text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sourceCourseId)
;~ Paste whats copied from column B into the DESTINATION Course ID text box  ?!?!?!?! <--- Need Help Here. I can only get Column A data
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_destinationCourseId)
;~ .... Do stuff

    MsgBox(0, "?", "Did it work?")
    Return $sResult

EndFunc   ;==>CopyCourseNow

 

Please let me know if any more info is needed. Thank you again for your support

Edited by XinYoung
Simplified the code
Link to post
Share on other sites
On 1/10/2019 at 2:16 PM, Subz said:

The array would become a 2d array $aSearchItems

$aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))

You can then obtain that information in a loop as follows:

For $i = 0 To Ubound($aSearchItems) - 1
    ;~ Column 0
    ConsoleWrite($aSearchItems[$i][0] & @CRLF)
    ;~ Column 1
    ConsoleWrite($aSearchItems[$i][1] & @CRLF)
    $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i][0], $aSearchItems[$i][1])
Next

For CopyCourseNow function use something like:

;~ The course search was successful. COPY the course now.
Func CopyCourseNow($_sourceCourseId, $_destinationCourseId)
Local $sResult
;~  Navigate to the course copy page.
    _IENavigate($oIE, $urlBBCourseCopy)

;~  Copy the SOURCE course ID from the Excel sheet

;~ Paste whats copied from column A into the Source Course ID text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sourceCourseId)

;~ Paste whats copied from column B into the Destination Course ID text box  ?!?!?!?!
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_destinationCourseId)
;~ .... Do stuff
Return $sResult
EndFunc   ;==>CopyCourseNow

You may need to post all your code, as the code is disjointed.

 

Theres a couple functions between OpenExcelForCopy() and CopyCourseNow(). If I do this, they get skipped 

 

Link to post
Share on other sites

OK I cleaned up the code a bit more so its easier to read

;~  This is where I am trying to copy the used range in column A and column B from Excel
Func OpenExcelForCopy()
    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B <---- it this right?
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
    $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i])
    Next
    For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
        ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
        ConsoleWrite($aSearchItems[$i][1] & @CRLF)
        $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i][0], $aSearchItems[$i][1])
;~ Were going to put the results of our work into Column C
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    Finished()
EndFunc   ;==>OpenExcelForCopy

;~  Then I go to IE and paste whats copied from column A into a search tool
Func SearchCourseForCopy($aSearchItems[$i][0], $aSearchItems[$i][1])
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $_sSearchResult)
    _IEFormSubmit($oForm)
    _IELoadWait($oIE)
;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
    Return CopyCourseBegin()
EndFunc   ;==>SearchCourseForCopy

;~ If the search in IE was bad, move onto the next row in Excel. If it was good, do the CopyCourseNow function
Func CopyCourseBegin()
    Local $sResult
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)
    If $iSearchIndex = -1 Then
        $sResult = "Source Not Found"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    Else
        For $i = 0 To UBound($aSearchItems) - 1
            $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i])
        Next
        $sResult = "Copied"
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    EndIf
    Return $sResult
EndFunc   ;==>CopyCourseBegin

;~ The search was good
Func CopyCourseNow($_sourceCourseId, $_destinationCourseId)
    Local $sResult
;~ Paste whats copied from column A into the SOURCE text box
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId")
    _IEFormElementSetValue($oSearchString, $_sourceCourseId)
;~ Paste whats copied from column B into the DESTINATION text box  ?!?!?!?! <--- Need Help Here. Not working. Cant paste the data from column B
    Local $oForm = _IEGetObjByName($oIE, "selectCourse")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId")
    _IEFormElementSetValue($oSearchString, $_destinationCourseId)
    
;~ .... Do stuff

    Return $sResult

EndFunc   ;==>CopyCourseNow

 

 

 

 

 

Link to post
Share on other sites

 

On 1/10/2019 at 2:16 PM, Subz said:

You can then obtain that information in a loop as follows:

For $i = 0 To Ubound($aSearchItems) - 1
    ;~ Column 0
    ConsoleWrite($aSearchItems[$i][0] & @CRLF)
    ;~ Column 1
    ConsoleWrite($aSearchItems[$i][1] & @CRLF)
    $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i][0], $aSearchItems[$i][1])
Next

 

I'm trying to implement this. I think I can get rid of the SearchCourseForCopy and CopyCourseBegin functions but I'm getting this

==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

Edited by XinYoung
Link to post
Share on other sites

See where the issue is ($aSearchResult, is a duplicate of $aSearchItems so you would also need to use $aSearchItems[$i][0]), however you could write it as follows:
nb: Its best practice not to declare variables in the Global scope within a function

Global $aBBTableData, $oExcel, $oWorkbook, $aSearchItems, $aSearchResult[0]
Func OpenExcelForCopy()
    $oExcel = _Excel_Open()
    $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B <---- it this right?
    $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Create the $aSearchResult array
    ReDim $aSearchResult[UBound($aSearchItems)]
    
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
        ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
        ConsoleWrite($aSearchItems[$i][1] & @CRLF)
        $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i][0], $aSearchItems[$i][1])
;~ Were going to put the results of our work into Column C
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
    Finished()
EndFunc   ;==>OpenExcelForCopy

 

Edited by Subz
Link to post
Share on other sites

Works fine for me, see test below:

#include <Array.au3>
#include <Excel.au3>
Global $aBBTableData, $oExcel, $oWorkbook, $aSearchItems, $aSearchResult[0]
$ChosenFileName = @ScriptDir & "\Test.xlsx"
OpenExcelForCopy()
Func OpenExcelForCopy()
    $oExcel = _Excel_Open()
    $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
    $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B <---- it this right?
    $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Create the $aSearchResult array
    ReDim $aSearchResult[UBound($aSearchItems)]

;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
    For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
        ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
        ConsoleWrite($aSearchItems[$i][1] & @CRLF)
        $aSearchResult[$i] = CopyCourseNow($i, $aSearchItems[$i][0], $aSearchItems[$i][1])
;~ Were going to put the results of our work into Column C
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
;~     Finished()
EndFunc   ;==>OpenExcelForCopy

Func CopyCourseNow($iIndex, $_sSearch, $sSearch1)
    Return Mod($iIndex, 2) = 0 ? "Odd" : "Even"
EndFunc

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Steviep
      Hi all,
      I've been using the following code for many years for the sole purpose of tracking my app usage via Google Analytics:
      ;GOOGLE ANALYTICS $AppStatsName = @ScriptName $GA = _IECreate("https://mywebserver/apps/stats/" & $AppStatsName & ".html", 0, 0, 0, 0) The app simply calls a blank .html page on my webserver which only contains the GA tracking code.
      The $iVisible parameter is set to "0" in my case, which means the IE browser is invisible to the user.
      I noticed in Windows 11, the URL is called in the Edge browser and is not invisible.
      So, I wonder if anyone out there has any suggestions on calling a URL invisibly on any operating system?
      Thanks!
    • By Rskm
      Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
      Tmp.xls
    • By mmoalem
      Hi all - I have created a simple script that turn on cctv software (contacam)  when my phone does not ping back (when I'm out of range of home network)
      #include <MsgBoxConstants.au3> $iPing = 0 For $i = 5 To 1 Step -1 ; this 5 loops cycle is just for testing - please ignnore - when complete it will be an endless loop Example() Sleep(120000) Next Func Example() ; Ping the phone. $iPing = Ping("192.168.1.102") If $iPing Then ; If a value greater than 0 was returned then turn off camera. _endCCTV() Else _startCCTV() ; If a value of 0 was returned then turn on camera. EndIf EndFunc ;==>Example Func _startCCTV() ShellExecute("C:\contacam capture\FJ Camera\CAMERA.bat", "on") Sleep(10000) ShellExecute("C:\contacam capture\FJ Camera\CAMERA_REC_SENSITIVITY.bat", "50") EndFunc Func _endCCTV() ShellExecute("C:\contacam capture\FJ Camera\CAMERA_REC_SENSITIVITY.bat", "0") Sleep(10000) ShellExecute("C:\contacam capture\FJ Camera\CAMERA.bat", "off") EndFunc  
       
      the problem I have is that in this format the script run the bat file that turn on the camera (or off) everytime the ping runs but obviously once the ping returns 0 and the camera starts I only want to run the bat on ping NOT 0 (turn off camera) 
      any ideas/advice?
    • By Hermes
      Hello, the script below will read column A from an excel file - and if a value matches in the browser, it will click the corresponding link and click on a specific button to paste the data, then writes "Completed" in Column B. It will continue to read from the excel file and do the same thing for all the remaining rows.
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <File.au3> #include <IE.au3> #include <Array.au3> #include <INet.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> #Include "WinHttp.au3" #Include "_HtmlTable2Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "table1.html") _WD_LoadWait($sSession) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") ;ConsoleWrite ("mat-table " & $sElement & @CRLF) Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(@class,'data')]", $sElement, True) sleep(1000) For $i = 0 to UBound($aArray1) - 1 $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text') Next ;_ArrayDisplay($aArray1) ;Email variables $SmtpServer = "" ; address for the smtp-server to use - REQUIRED $FromName = "Hermes" ; name from who the email was sent $FromAddress = "sender@gmail.com" ; address from where the mail should come $ToAddress = "recipient@gmail.com" ; destination address of the email - REQUIRED, use commas (,) to add more email addresses $Subject = "File not found" ; subject from the email - can be anything you want it to be $Body = "File not found!" ; the messagebody from the mail - can be left blank but then you get a blank mail $AttachFiles = "" ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed $CcAddress = "" ; address for cc - leave blank if not needed $BccAddress = "" ; address for bcc - leave blank if not needed $Importance = "High" ; Send message priority: "High", "Normal", "Low" $Username = "" ; username for the account used from where the mail gets sent - REQUIRED $Password = "" ; password for the account used from where the mail gets sent - REQUIRED $IPPort = 25 ; port used for sending the mail $ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS $tls = 0 ; enables/disables TLS when required Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters If FileExists($sWorkbook) Then ;Check if the file exist. Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $iIdx Local $Skipline = 0 ;0==> first line Do Local $temprf For $i = 0 To UBound($aArray2) - 1 $temprf &= $aArray2[$i] _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]") Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement, True) $iIdx = _ArraySearch($aArray1, $aArray2[$i]) If @error Then ContinueLoop _WD_ElementAction($sSession, $aElement[$iIdx], 'click') If $i < $Skipline Then ContinueLoop $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":XFD" & $i + 1) _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) ;Paste Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton") _WD_ElementAction($sSession, $oTest4, 'click') Sleep(1000) ;Save Button Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button") _WD_ElementAction($sSession, $save3, 'click') _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Completed", "B" & $i+1) sleep(1000) Next Until (Not @error) _Excel_Close($oWorkbook) Else _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls) Exit EndIf _WD_LoadWait($sSession) ;Attaching files to emails Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0) Local $objEmail = ObjCreate("CDO.Message") $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>' $objEmail.To = $s_ToAddress Local $i_Error = 0 Local $i_Error_desciption = "" If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress $objEmail.Subject = $s_Subject If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then $objEmail.HTMLBody = $as_Body Else $objEmail.Textbody = $as_Body & @CRLF EndIf $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer If Number($IPPort) = 0 then $IPPort = 25 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort ;Authenticated SMTP If $s_Username <> "" Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password EndIf ; Set security params If $ssl Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True If $tls Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendtls") = True ;Update settings $objEmail.Configuration.Fields.Update ; Set Email Importance Switch $s_Importance Case "High" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High" Case "Normal" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal" Case "Low" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low" EndSwitch $objEmail.Fields.Update ; Sent the Message $objEmail.Send $objEmail="" EndFunc ;==>_INetSmtpMailCom Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) Sleep(2000) For $i = 1 To $aDir[0] DirRemove($aDir[$i], $DIR_REMOVE) Next _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome If the excel file doesn't exists in the folder, it will send an email to a specific recipient.
      What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row
      Appreciate any help that I can get to achieve this.
      table1.html test.xlsx
    • By vinnyMS
      #Include <Array.au3> #include <Constants.au3> $s = FileRead("2.txt") Local $w = StringRegExp($s, '(?is)(\b\w+\b)(?!.*\b\1\b)', 3) _ArrayColInsert($w, 1) For $i = 0 to UBound($w)-1 StringRegExpReplace($s, '(?i)\b' & $w[$i][0] & '\b', $w[$i][0]) $w[$i][1] = @extended Next _ArraySort($w, 1, 0, 0, 1) _ArrayDisplay($w) i have this script that returns 3 columns  
       
      i need to copy the  Col 0 and Col 1 as text to paste on notepad or excel
      you will have to create a "copy" button if possible
      array.au3 2.txt
×
×
  • Create New...