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 Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
    • By sudeepjd
      I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 
      It has the following functions :
      _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post.
      Please do let me know if I can improve or add additional functions to it.
      A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached.
      #include "ExcelPivot.au3" $oExcel = _Excel_Open() $oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx") ;Create a Sheet to put the pivot into $pSheet = _Excel_SheetAdd($oBook, -1, False, 1) $pSheet.Name = "Pivot" ;Get the cache for the pivot table $pCache = _ExcelPivot_CreateCache($oBook, "Data") ;Add in the Pivot Table from the Cache _ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot") ;Add in the Fields into the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1) ;Add in a Running total to the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product") ;Filter only the fruits _ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit") ;Draw a Paretto Chart $chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570) $chart.Chart.FullSeriesCollection(1).ApplyDataLabels $chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph $chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis $chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max  
       
      ExcelPivot.au3 Example.zip
    • By Deshanur
      Am trying to automate injecting credential on the login form for all kind of Web application for IE. I know how to identify the form name by viewing the source code and using the method - _IEFormGetObjByName($ie, $form_Name).
      I would like to know how to identify or get the form object for the web app where there is no form name tag for example below, for the is I have used - _IEFormGetCollection($ie, 0) to get the form object.
      My Question is does it work for all kind of application "_IEFormGetCollection($ie, 0)" how to identify Index value? is it always 0? is there any better solution?
      The final solution am looking for is find out form object, get the username, password field and inject credential and submit the form.
      How to find out index value? for the forms which does not have form name field.
      $login_form = _IEFormGetCollection($ie, 0) $email_field = _IEFormElementGetObjByName($login_form, $form_UserName) $pass_field = _IEFormElementGetObjByName($login_form, $form_password) $login_button = _IEFormElementGetObjByName($login_form, $form_submitbutton) _IEFormElementSetValue($email_field, $CmdLine[2]) _IEFormElementSetValue($pass_field, $CmdLine[3]) ControlSend($hwnd, "", "[CLASS:Internet Explorer_Server; INSTANCE:1]","{Enter}") OR This works fine if the form has form name. $login_form = _IEFormGetObjByName($ie, $form_Name) $email_field = _IEFormElementGetObjByName($login_form, $form_UserName) $pass_field = _IEFormElementGetObjByName($login_form, $form_password) $login_button = _IEFormElementGetObjByName($login_form, $form_submitbutton) _IEFormElementSetValue($email_field, $CmdLine[2]) _IEFormElementSetValue($pass_field, $CmdLine[3]) ControlSend($hwnd, "", "[CLASS:Internet Explorer_Server; INSTANCE:1]","{Enter}")
    • By Page2PagePro
      Excel VBA's IDE registers a Control-y as "cut this line of code".
      For those prone to Undo/Redo (Ctrl+Z/Ctrl+Y) you may find frustration when your code in the editor does not redo, but in fact clears your active line of code while killing redo history.
      Though not perfect, I keep this tool running in background on startup.
      The purpose is to allow Cltr+Y to act normally throughout Windows and Office and only interact *differently* with the "Microsoft Visual Basic for Applications" window that is active.
      If the Standard Menu bar exists, it'll try to click the ReDo (Blue Arrow to the right), else "Alt+e, r" keystrokes (less desired).
       
      Here's the code:
      Opt('MustDeclareVars', 1) Opt("WinTitleMatchMode", 1) HotKeySet("^y", "TriggerRedo") While 1 Sleep(10) WEnd Func TriggerRedo() ConsoleWrite("TriggerRedo()" & @CRLF) Local $title = "Microsoft Visual Basic for Applications - " Local $hWnd If WinExists($title) And WinActive($title) Then ;~ Parent Window Handle $hWnd = WinGetHandle($title) Local $aWindowPos = WinGetPos($hWnd) ;~ Control Bar Handle, Position and If Visible Local $sControlID = "[CLASS:MsoCommandBar; TEXT:Standard;]" Local $hStandardBar = ControlGetHandle($hWnd, "", $sControlID) Local $bIsVisible = ControlCommand($hWnd, "", $sControlID, "IsVisible") If $hStandardBar And $bIsVisible Then ConsoleWrite("Using Mouse Click." & @CRLF) ;~ Determine Redo button location on visible Control Bar Local $aBarPos = ControlGetPos($hWnd, "", $sControlID) Local $mX = $aWindowPos[0] + $aBarPos[0] + 217 + Int(23/2) Local $mY = $aWindowPos[1] + $aBarPos[1] + 27 + Int(22/2) MouseClick("Left", $mX, $mY, 1, 0) Else ConsoleWrite("Using VBA Send Keys." & @CRLF) $sControlID = "[CLASS:MsoCommandBar; TEXT:Menu Bar;]" Local $hMenuBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hMenuBar, "!e") ;~ Send("r") $sControlID = "[CLASS:MsoCommandBarPopup; TEXT:Edit;]" Local $hPopupBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hPopupBar, "r") EndIf Else ConsoleWrite("Using NATIVE Send Keys." & @CRLF) HotKeySet("^y") Send("^y") ;~ may cause "yyy..." when held HotKeySet("^y", "TriggerRedo") EndIf EndFunc ;==>TriggerRedo Hope this inspires someone.
       
       
    • By siawpo
      Hi,
      I'd like to change different colors for different portion of text in same cell of Excel application.
      Neither character length nor cell might not fixed.
      Here's the code I've tried to put together but not manage to pull it off.
      I'm appreciate it for any suggestion, thank you.
      $oExcel = ObjCreate("Excel.Application") With $oExcel ; open new workbook .Visible = True .WorkBooks.Add .ActiveWorkbook.Sheets(1).Select() EndWith $oExcel.Cells.Font.Color = 0x000000 $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveCell.Selection.Font.Color = 0x000000 Send ("'I like this sentence to be black'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red again'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) Send("{ENTER}")  
×
×
  • Create New...