Jump to content

Copy & Paste from Excel In a Loop

XinYoung

By XinYoung,
in AutoIt General Help and Support

 Share Followers 0

Recommended Posts

XinYoung

XinYoung 1

Posted
Posted

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
Subz

Subz 826

Posted
Posted (edited)

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
XinYoung

XinYoung 1

Posted
  • Author
Posted (edited)

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
XinYoung

XinYoung 1

Posted
  • Author
Posted
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
XinYoung

XinYoung 1

Posted
  • Author
Posted

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
XinYoung

XinYoung 1

Posted
  • Author
Posted (edited)

 

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
Subz

Subz 826

Posted
Posted (edited)

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
Subz

Subz 826

Posted
Posted

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
 Share
Followers 0
Go to topic listing

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • JohnnyTries
      aArray headers Always transferred to existing Excel spreadsheet.
      By JohnnyTries
      Hi Fellow Automators,
      Long time listener, first time caller. I've resisted posting on the forums as long as possible for fear of public lynching, but I'm stuck and could really use some help.
      Note: If this is the wrong side of the forum for this topic, I apologize.
      I've built a GUI and script to make our lab data collection easier. The app has a number of input boxes and a 'record' button. The user fills out the input boxes with various notes and then presses 'record', which in turn presses 'record' on two other, separate apps simultaneously, pulls those recordings together into one folder, and then takes the text from the input boxes and adds it in a new row at the bottom of an existing .xlsx spreadsheet. Everything works great, except that every time I open the app to start collecting data for the day, the 'headers' for the $aArray are added to a new row and then the text is added below it. Now, if I don't close the app between collections, subsequent 'recordings' are added to the spreadsheet as expected. If I close the app and open it, the 'first' recording of the day adds the headers to a new row. I don't need new 'header' info because I've already got that in row 1 of the spreadsheet.
      If someone could tell me where I'm !#$%ing up, I would greatly appreciate it. 
       
      #RequireAdmin #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <MsgBoxConstants.au3> #include <WindowsConstants.au3> #include <String.au3> #include <Process.au3> #include <FileConstants.au3> #include <WinAPIFiles.au3> #include <DirConstants.au3> #include <Array.au3> #include <AutoItConstants.au3> #include <File.au3> #include <WinAPIShPath.au3> #include <Excel.au3> ;Declaring the $aArray and location of the speadsheet at the top of the script Local $aArray[1][9] = [["TestID","DateTimeStamp","Tamb_C","BGTemp_C", "GasType", "TrueFlow_slm", "Lens-BGDist_in", "Lens-LeakDist_In", "AddNotes"]] Local $sDataFilePath = @ScriptDir & "\Notes\DualCaptureNotes.xlsx" Func CaptureVideo() Global $TestID = GuiCtrlRead($TestIDInput) Global $timestamp = @YEAR & "-" & @MON & "-" & @MDAY & "-" & @HOUR & "-" & @MIN & "-" & @SEC Global $Cam1Dir = "C:\Archive" Global $Cam2Dir = "C:\ALD" ;Capture Cam1 Data WinActivate("Cam1 App") ControlFocus("Cam1 App", "Save",'WindowsForms10.Window.8.app.0.2099316_r7_ad114') ControlSend("Cam1 App", "Save", 'WindowsForms10.Window.8.app.0.2099316_r7_ad114', "{SPACE}") ;Capture Cam2 Data WinActivate("Cam2 App") ControlClick("Cam2 App", "", 'WindowsForms10.Window.8.app.0.1b0ed41_r7_ad122', '', 1, 10, 10) ;Wait to ensure data files have been fully written to their default locations Sleep(2000) ;Self-explanatorily named functions MoveData() RecordNotesToArray() RecordArraytoExcel() WinActivate("DualCapture") EndFunc ;==>CaptureVideo ;Skipping ahead to the .xlsx part.... Func RecordNotesToArray() _ArrayAdd($aArray, GUICtrlRead($TestIDInput) & "|" & $timestamp & "|" & GUICtrlRead($Tamb_CInput) & "|" & GUICtrlRead($BGTemp_CInput) & "|" & GUICtrlRead($GasTypeInput) & "|" & GUICtrlRead($TrueFlow_slmInput) & "|" & GUICtrlRead($Dist_BG_inInput) & "|" & GUICtrlRead($Dist_Leak_inInput) & "|" & GUICtrlRead($AddNotesInput)) EndFunc ;==>RecordNotesToArray() Func RecordArraytoExcel() Local $oExcel = _Excel_Open() Local $oWorkBook If Not FileExists($sDataFilePath) Then $oWorkBook = _Excel_BookNew($oExcel) Else $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath) EndIf $oWorkBook.Worksheets("DataTable").Columns("A:I").AutoFit $LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row $Rowrange = "A"&$LastRow+1 Consolewrite($Rowrange & @crlf) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, $Rowrange) If FileExists($sDataFilePath) Then _Excel_BookSave($oWorkBook) Else _Excel_BookSaveAs($oWorkBook, $sDataFilePath) EndIf _Excel_BookClose($oWorkBook) _Excel_Close($oExcel) EndFunc ;==>RecordArrayToExcel() I appreciate your time and any help you can provide.
       
      Best,
      Johnny
       
    • phoenixhuynh09
      [SOLVED] - How to tick the checkbox in the table (Internet Explorer)
      By phoenixhuynh09
      Hi everybody
      Currently I need to tick a checkbox that runs on IE. But I can't click on it. Although the correct object has been specified. Here is my code:
      #include <IE.au3> $oIE = _IEAttach("WEB") $oLinks = _IETagNameGetCollection($oIE, 'span') For $oLink In $oLinks $a = String($oLink.classname) == 'x-column-header-text' $b = StringLeft(String($oLink.id), 10) == 'gridcolumn' If $a And $b Then _IEAction($oLink, "click") ConsoleWrite("Founded" & @CRLF) ExitLoop Else ConsoleWrite("Not Found" & @CRLF) EndIf Next After inspecting the element it shows only 1 line of code:
      <div class="x-column-header x-column-header-checkbox x-column-header-align-left x-box-item x-column-header-default x-unselectable x-column-header-first x-grid-hd-checker-on" id="gridcolumn-1588" style="margin: 0px; left: 0px; top: 0px; width: 24px; right: auto; border-top-width: 1px; border-bottom-width: 1px; border-left-width: 1px;"> <div class="x-column-header-inner" id="gridcolumn-1588-titleEl"> <span class="x-column-header-text" id="gridcolumn-1588-textEl"></span> </div> </div> Here is an image of the checkbox:

      I used more ways to check:
      - _IEGetObjById => IEAction($oLink, "click") not working
      - _IETableGetCollection => _IETableWriteToArray gives an error
      - _IEImageClick
      All are not working.
      Hope to get a response from everyone.
      Thank you very much.
    • Sachs
      Script Pauses when run in Java, but okay in Script Editor or cmd
      By Sachs
      I have a few questions:
      BACKGROUND
      I am using Oracle Application Testing Suite's OpenScript 13 (Eclipse IDE), Oracle Java 6, and AutoIt.
      My goal is to access a given Internet Explorer browser window (`$ieTitle`), send the keystroke of "shift-control-s" for "Save As" functionality to be invoked, so that I can download a (PDF) file to a given location ($fileName).
      The code within the script `DownloadPdfFile.au3` is
      $ieTitle = $CmdLine[1] $ieControl = "AVL_AVView31" $fileName = $CmdLine[2] ControlFocus($ieTitle, "", $ieControl) ControlSend($ieTitle, "", $ieControl, "+^s") ; Save as dialog $winTitle = "Save As" ; wait for Save As window WinWait($winTitle) ; activate Save As window If Not WinActive($winTitle) Then WinActivate($winTitle) ControlFocus($winTitle,"","Edit1") ControlSetText($winTitle,"","Edit1",$fileName) Sleep(2000) ControlClick($winTitle,"","Button3") Exit 0 So I compiled it with SciTE-Lite (32-bit Version 4.4.6 , creating `DownloadPdfFile.exe`, and so within my Java code, I have
      String command = autoItExePath + " " + scriptPath + " \"" + winTitle + "\" " + directoryPath.toFile().toString() + "\\Form9Report" + sdf_ddmmmyyyy.format(new Date()) + ".pdf"; try { Process process = Runtime.getRuntime().exec(command); process.waitFor(); } catch (Exception e) { logger.error("Exception " + e.getMessage(), e); } The output would be like `C:\Program Files (x86)\AutoIt3\AutoIt3.exe C:\...\AutoItScripts\DownloadPdfFile.au3 "https://****.com/****.exe?temp_id=**** - Internet Explorer" C:\...\Report05Apr2022.pdf"` which does run without the $cmdLine successfully. 
      When executed by Java, I see in the taskbar an icon, which I right-click has "[Check] Script Paused" and "Exit".
      Questions:
      (1) How do I unpause the script?
      (2) How do I avoid having the script paused?
      Any help is appreciated.
    • goku200
      Seperating filename into coumns in Excel
      By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • SkysLastChance
      _Excel_RangeWrite Formula with "" and '' included
      By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
×
×
  • Create New...