Jump to content
Sign in to follow this  
XinYoung

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?

 

 

Share this post


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

Share this post


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

Share this post


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 

 

Share this post


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

 

 

 

 

 

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

Hmmm.. the script just stops once it reaches the ReDim line. The loop and the CopyCourseNow func never runs

I put a msgbox before and after the ReDim line. The 2nd msgbox never appears :think:

Edited by XinYoung

Share this post


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

 

Share this post


Link to post
Share on other sites

OhHhHHhh i see what i did wrong. When we moved the Global variables out of the function I put it in the wrong place and they were never being read. :doh:.

Thank u again so much Subz!! Its working. Now I can make it 'do stuff'

Share this post


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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By FUD
      hello 
      please i need help 
      i need to open link in default browser only one windows without duplicate if i try to open the same link 
       
      thanks 
    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By Mariog2000
      I've tried for a few days to simply open a url in IE and click an IE object that I can't "TAB" to and don't want to use a mouse-click if at all possible.  I've read up on things like using IEAction and IETagNameGetCollection to find the object but not smart enough to figure out how that works with my url or how to tell if my url is "basic" or a "form", "frameset", "iframe", etc.
      The other problem is I've tried using the "Run" and "ShellExecute" commands to open the browser, which works, but it opens it in a way that doesn't include my currently logged in user account.  For example, if I just click IE or Chrome for that matter and open my default homepage of google, the page that opens knows I'm logged in.  When I use "Send" "ShellExecute" or "_IE_Create" they always open pages generically which would require a login.
      So questions are, if I have a URL I'm trying to open, that's passes the currently logged on users credentials when opening the page (no clue where that happens)
      1.  Are there any code examples someone is willing to share that opens my IE maximized, with logged in credentials, and
      2.  Once that works, can someone suggest a technique a non-developer can find which IE Objects I need to send a "click" to so it opens the correct menu, pop-up, url, etc., associated with that object.  I've copied what doesn't work and sure I'm way off but it's not for a lack of effort. I'll keep trying and post updates but not looking good 🙂
      #include <IE.au3>
      Call ("selection")
      Func selection ()
      Global $oIE = _IECreate ("https://internal-webpage.aspx")
      Local $selectid = _IEGetObjByName ($oIE, "signageNo")
      Local $button = _IEGetObjByName ($oIE, "GO")

      _IEFormElementOptionSelect ($selectid, "3")
      _IEAction ($button,"click")
      EndFunc
      #include <IE.au3> Call ("selection") Func selection () Global $oIE = _IECreate ("https://internal-webpage.aspx") Local $selectid = _IEGetObjByName ($oIE, "signageNo") Local $button = _IEGetObjByName ($oIE, "GO") _IEFormElementOptionSelect ($selectid, "3") _IEAction ($button,"click") EndFunc 1. 
    • By singh54
      Hello All,

      I am new to auto It  and coding. Have only tried few automated logging for different websites.  The login pages of the websites which I have worked on were having form name with input type as text and it seems straight forward to automate their login using "_IEFormElementSetValue".
      I have got a url, it does not have any form in the source page. On further analyzing I found that It does call some login page separately as below.
      function showLogin(arg) {
        Global.pollingDialogDoc = null;
        var fresh = jQuery.isValidString(arg) && "fresh" == arg ? !0 : !1, appFrame = $("#appFrame");
        if ($("#modalFrame").show(), $("iframe").hide(), fresh || !appFrame.attr('src').match("html/login.html")) appFrame.attr('src', baseURL + 'html/login.html'), 
        appFrame.on('load', function() {
          setTimeout(function() {
            $("#modalFrame").hide(), appFrame.show();
          }, 1);
        }); else try {
          window.frames.appFrame.updatePageFromIndex();
        } catch (e) {}
      }
      I can simply use "send" and "mouse click" Method to automate the login but that doesn't seems very reliable. Is there any separate way to fill the user ID and password to the respective fields by having reference by frames or something. 
      Appreciate if any one can point me to correct document or help in any way...!
×
×
  • Create New...