Jump to content
Sign in to follow this  
XinYoung

Do...Until Excel sheet has no more data?

Recommended Posts

Why hello there! :D

I have a script reading an Excel sheet (currently only looking at A1), copying the data in that cell, and then searching for it in a Website's search tool. Then it does some stuff if it actually finds something.

After that, I need it to go back to the Excel sheet, write "yes" into column B if the search was good -- "no" if no results were found, then go down to A2 and do the whole thing all over again until column A has nothing left.

:frantics: I really suck at loops and have a hard time grasping how it works. Any help or guidance is really appreciated!

 

SearchCourse()

;~  Now that were logged in, navigate to the course search page.
Func SearchCourse()
    _IENavigate($oIE, $urlBBCourseSearch)

;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")

;~  Open the selected Excel file
    Local $oAppl = _Excel_Open()
    Local $oWorkbook = _Excel_BookOpen($oAppl, $ChosenFileName, Default, Default, True)

;~ Copy whats in the first cell (A1)
    _Excel_RangeCopyPaste($oWorkbook.Worksheets(1), "A1")
    Global $WhatsCopied = ClipGet()

;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $WhatsCopied)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    Global $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)

    DeleteCourseBegin()

EndFunc   ;==>SearchCourse

;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    $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")
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
    EndIf
EndFunc   ;==>DeleteCourseBegin

;~ The course search was successful. Delete the course now.
Func DeleteCourseNow()
;~ Click the checkbox located beside the course that has been found.
    Local $CheckBox = _IEGetObjByName($oIE, "ckbox")
    _IEAction($CheckBox, "click")
    _IELoadWait($oIE)

;~ Click the Delete button.
    _IELinkGetCollection($oIE)
    Local $oLinks = _IELinkGetCollection($oIE)
    For $oLink In $oLinks
        If $oLink.href = "javascript:validateRemove();" Then
            _IEAction($oLink, "click")
            ExitLoop
        EndIf
    Next

;~  Click the "Delete course, including all of its files" radio button.
    Local $RadioButton = _IEGetObjById($oIE, "removeAllFiles_t")
    _IEAction($RadioButton, "click")
    _IELoadWait($oIE)

;~ Click the "Submit" button
    Local $Submit = _IEGetObjByName($oIE, "bottom_Submit")
    _IEAction($Submit, "click")
    _IELoadWait($oIE)

;~  Now go back to the Excel sheet and search for the next one....?

EndFunc   ;==>DeleteCourseNow

 

 

Edited by XinYoung
Fixed some comments in the code

Share this post


Link to post
Share on other sites

If you do this after you make your workbook object, you will get the size of the excel workbook (know what the last line of the excel file will be).

#include <array.au3>

$array = _Excel_RangeRead($oWorkbook)
$size = Ubound($array)

Give adding in a loop a try now that you know the size. If you are still struggling with it, let us know!

Edited by Davidowicza
Grammar

Share this post


Link to post
Share on other sites

@XinYoung You can do this easily with _Excel_RangeWrite. Something like this should get you started:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test1.xlsx")
Local $oRange = _Excel_RangeRead($oWorkbook, Default, Default)

For $a = 1 To UBound($oRange)
    ;Do other Stuff
    _Excel_RangeWrite($oWorkbook, Default, "This is cell B" & $a, "B" & $a)
Next

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

If your columns have headers, you'll have to adjust by one.


"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites

It would be faster to just grab all of A:A into an array and loop through the results and create a second array to hold the "Yes/No" here is an example untested:

#include <Array.au3>
#include <Excel.au3>
Global $sChosenFileName = "Filename.xlsx"
Global $aBBTableData
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, $sChosenFileName)
;~ Get all used cells in column A:A
Global $aSearchItems = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
;~ Duplicate the $aSearchItems Array 
Global $aSearchResult = $aSearchItems
;~ Loop through $aSearchItems
;~ Since Excel_RangeRead returns a 0-based Array and since we only captured one column it is a 1 dimension array
;~ 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] = SearchCourse($aSearchItems[$i])
Next
_ArrayDisplay($aSearchItems, "Search Items")
_ArrayDisplay($aSearchResult, "Search Results")
;~ Paste the results back to Excel in column B1
_Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "B1")

Func SearchCourse($_sSearch)
    _IENavigate($oIE, $urlBBCourseSearch)

;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")

;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $_sSearch)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)

    DeleteCourseBegin()

EndFunc   ;==>SearchCourse

;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    $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 = "No"
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
        $sResult = "Yes"
    EndIf
    Return $sResult
EndFunc   ;==>DeleteCourseBegin

;~ The course search was successful. Delete the course now.
Func DeleteCourseNow()
;~ Click the checkbox located beside the course that has been found.
    Local $CheckBox = _IEGetObjByName($oIE, "ckbox")
    _IEAction($CheckBox, "click")
    _IELoadWait($oIE)

;~ Click the Delete button.
    _IELinkGetCollection($oIE)
    Local $oLinks = _IELinkGetCollection($oIE)
    For $oLink In $oLinks
        If $oLink.href = "javascript:validateRemove();" Then
            _IEAction($oLink, "click")
            ExitLoop
        EndIf
    Next

;~  Click the "Delete course, including all of its files" radio button.
    Local $RadioButton = _IEGetObjById($oIE, "removeAllFiles_t")
    _IEAction($RadioButton, "click")
    _IELoadWait($oIE)

;~ Click the "Submit" button
    Local $Submit = _IEGetObjByName($oIE, "bottom_Submit")
    _IEAction($Submit, "click")
    _IELoadWait($oIE)
;~  Now go back to the Excel sheet and search for the next one....?

EndFunc   ;==>DeleteCourseNow

 

Edited by Subz

Share this post


Link to post
Share on other sites

OK, I tried implementing it but I'm getting an error: SearchCourse() called by a previous line with 0 arg(s)

Because of the way I call upon the func...?

 

OpenExcel()

Func OpenExcel()
    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, Default, True)
;~ Get all used cells in column A:A
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through $aSearchItems
;~ Since Excel_RangeRead returns a 0-based Array and since we only captured one column it is a 1 dimension array
;~ 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] = SearchCourse($aSearchItems[$i])
    Next
    _ArrayDisplay($aSearchItems, "Search Items")
    _ArrayDisplay($aSearchResult, "Search Results")
;~ Paste the results back to Excel in column B1
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "B1")

    SearchCourse()

EndFunc   ;==>OpenExcel


;~  Now that were logged in, navigate to the course search page.
Func SearchCourse($aSearchResult)
    _IENavigate($oIE, $urlBBCourseSearch)

    ;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")

;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $aSearchResult)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)

    DeleteCourseBegin()

EndFunc   ;==>SearchCourse



;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    $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 = "No"
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        $aSearchResult
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
        $sResult = "Yes"
    EndIf
    Return $sResult
EndFunc   ;==>DeleteCourseBegin

;~ The course search was successful. Delete the course now.
Func DeleteCourseNow()
;~ Click the checkbox located beside the course that has been found.
    Local $CheckBox = _IEGetObjByName($oIE, "ckbox")
    _IEAction($CheckBox, "click")
    _IELoadWait($oIE)

;~ Click the Delete button.
    _IELinkGetCollection($oIE)
    Local $oLinks = _IELinkGetCollection($oIE)
    For $oLink In $oLinks
        If $oLink.href = "javascript:validateRemove();" Then
            _IEAction($oLink, "click")
            ExitLoop
        EndIf
    Next

;~  Click the "Delete course, including all of its files" radio button.
    Local $RadioButton = _IEGetObjById($oIE, "removeAllFiles_t")
    _IEAction($RadioButton, "click")
    _IELoadWait($oIE)

;~ Click the "Submit" button
    Local $Submit = _IEGetObjByName($oIE, "bottom_Submit")
    _IEAction($Submit, "click")
    _IELoadWait($oIE)
;~  Now go back to the Excel sheet and search for the next one....?

EndFunc   ;==>DeleteCourseNow

 

So, is this being caused by how I have everything residing within a Func? Because at the end of each func, I call upon another one. Is that not a good way to go about it? :huh2:

I can't do something like Func SearchCourse($aSearchResult) this way?

 

Share this post


Link to post
Share on other sites

OK, but still getting the same error (SearchCourse() called by a previous line with 0 arg(s)) because of...

$aSearchResult[$i] = SearchCourse($aSearchItems[$i])

^located in the OpenExcel() func :think:

Share this post


Link to post
Share on other sites

No The error means you've called SearchCourse without any parameter, so look for the following and remove it since its been called within the loop.

SearchCourse()

 

Share this post


Link to post
Share on other sites

OhHhHHhHHHh my bad! I get it. It was already there and I put it in again with 0 arg. amirite?

Alright that error is gone! :lol:

Now I'm getting Statement cannot be just an expression regarding $aSearchResult in the Else...

;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    $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 = "No"
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        $aSearchResult
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
        $sResult = "Yes"
    EndIf
    Return $sResult
EndFunc   ;==>DeleteCourseBegin

 

Share this post


Link to post
Share on other sites

Hmmm, still getting the same error

;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    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 = "No"
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        $aSearchResult
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
        $sResult = "Yes"
    EndIf
    Return $sResult
EndFunc   ;==>DeleteCourseBegin

image.thumb.png.524dd4e53ca41f00296096e748043913.png

Share this post


Link to post
Share on other sites

its working now :dance:

This is so cool. It doing the thing. :yes:

And when there's no more it puts a "0" into column B.

THANK YOU!!!!!!1

 

Share this post


Link to post
Share on other sites

"And when there's no more it puts a "0" into column B."

Do you mean it does have Yes or No in column B?  What does _ArrayDisplay($aSearchResult, "Search Results") return?

Share this post


Link to post
Share on other sites

This

image.png.2c285ca37891a34bc051f37522921fea.png

It doesn't enter "Yes" or "No" at any point. It only throws a bunch of zero's into column B at the very end.

 

Edited by XinYoung
Elaboration

Share this post


Link to post
Share on other sites

Sorry a bit difficult to write code without testing, but you should be able to fix this by changing the SearchCourse(..) function, adding Return DeleteCourseBegin

;~  Now that were logged in, navigate to the course search page.
Func SearchCourse($_sSearchItem)
    _IENavigate($oIE, $urlBBCourseSearch)

    ;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")

;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $_sSearchItem)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)

    Return DeleteCourseBegin()
EndFunc   ;==>SearchCourse

 

Share this post


Link to post
Share on other sites

Hey, um, what do I change here to make it always target a sheet named "DeleteCourses" in my Excel workbook? Sometimes the file was saved on a different sheet, which causes the wrong sheet to be Active.

Func OpenExcel()
    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, Default, True)
;~ Get all used cells in column A:A
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through $aSearchItems
;~ Since Excel_RangeRead returns a 0-based Array and since we only captured one column it is a 1 dimension array
;~ 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] = SearchCourse($aSearchItems[$i])
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "B1")
    Finished()
EndFunc   ;==>OpenExcel

Thanks again

Share this post


Link to post
Share on other sites
56 minutes ago, XinYoung said:

what do I change here to make it always target a sheet named "DeleteCourses"

 

Assuming that the sheet actually exists, the following line will make sure that the sheet is active:

$oExcel.Sheets("DeleteCourses").Activate

 

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 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 Zaoka
      Hi guys
      Need little help with filtering.
      I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :
       
      #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb") $oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error
      error: Array(): undefined function. Not sure how to resolve this.
    • By Rajat231
      I am trying this code to create multiple workbooks eachone  shall be copy of one worksheet from a workbook having multiple sheets ( keeing the name same)
      SavingWorksheets.au3
    • By Zaoka
      Hi guys,
      i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc )
      how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ?
      my junky try
      #include <Date.au3> #include <Excel.au3> Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1) Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb") Sleep (5000) $oExcel.ActiveWorkbook.RefreshAll Sleep (5000) $oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)  
      Error result
      $oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR  
×
×
  • Create New...