Jump to content
Zaoka

Filter Yesterday WeekDay PowerPivot

Recommended Posts

Posted (edited)

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

 

Edited by Zaoka

Share this post


Link to post
Share on other sites

$oExcel is the Application, so Application.Application won't really do anything. Try using $oWorkbook instead of $oExcel.Application :)


All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts

Share this post


Link to post
Share on other sites
Posted (edited)

No luck with that 😓

 

but menage to remove all filters,

$oWorkbook.Sheets("Sheet1").PivotTables("PivotTable1").ClearAllFilters

I think problem is with this part

PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)
Edited by Zaoka

Share this post


Link to post
Share on other sites

I made little progres, recorded macro with excel  and this is result

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]"). _
        ClearAllFilters
    Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]"). _
        CurrentPageName = "[Report 2].[WeekDAYS].&[Monday]"
End Sub

 

but when i insert it in autoit, only first part  "ClearAllFilters" is working, second part does not Filter anything

 

$oWorkbook.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]").ClearAllFilters
Sleep (1000)
$oWorkbook.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]").CurrentPage = "[Report 2].[WeekDAYS].&[Monday]"

 

some ideas or hints?

 

Share this post


Link to post
Share on other sites

You have CurrentPageName in your VBA, but only CurrentPage in AutoIt


All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts

Share this post


Link to post
Share on other sites

Yes Finali it works now, tnx

 

#include <Date.au3>
#include <Excel.au3>
Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1)

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb")


$oWorkbook.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]").CurrentPageName = "[Report 2].[WeekDAYS].&["&$sWEEKDAYYesterday&"]"

 

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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By undcover
      Hello, I'm automating part of the note taking ability of my old bad POS, I managed to do much of the heavy lifting in the past weeks, I can finally do everything i want and more.
      Now I have a form with two buttons that expand the form to show a note taking beast that can lets us escape the hell of the one way editing the POS actually support (no cursor just delete. want to change the time on that order better delete everything and start the note from scratch, well not anymore)
      Now i'm stuck, my form shows up as two buttons over the POS window, however I need it to go away when I minimized the POS or switch to a different page or application, I was able to do so by doing a while loop, it worked badly as it will repeat the show command infinitely and if i break the loop then there's no way to restart the loop if the user didn't interact with the buttons directly.
      I have many ways I could know when controls are visible and it worked, I just don't have a way of constantly checking for this without straining the CPU, I know if I work it somehow I could do a while loop that can work, but it'll be CPU intensive. (Bad POS entails BAD PC)
      Should I make another form that does the loop? can I make the loop slower ?
      I'm using  VB.net VS 2017 with AutoitX dll.
      EDIT: Hello anyone who searched for this, if you're and idiot like me and forgot that Timers exist then this will jog your memory
       
      Add a timer to your form, set the timer for 1 sec intervals (dealers choice)
      start your timer (within form load or manually)
      Timer1.start()
      then double click the timer to create a Timer tick (for my case the control visibility test i want to make each second)
      it should look something like this :
          Private Sub timer1_Tick(sender As Object, e As EventArgs) Handles timer1.Tick         dim visibleform = ait.ControlCommand("my app", "", "[NAME:wacontact]", "IsVisible", "")         If visibleform = 0 Then             Me.Hide() ElseIf visibleform = 1 And Me.Visible = False Then ' to prevent the timer ticks from interupting any sendkeys or something we put two conditions. Me.Show() End If     End Sub remarks: ait. is the call I set for Autoit DLL.
       
       
       
    • 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 BlueSkyMemory
      Hello guys! I'm a rookie in AutoIt lol.
      I've tried to looking up in MSDN and the UDFs, but it can only get the GUID of a usual partition and with the GUID to control it. Now I have no ways😥.
      Thanks a lot for your help!
    • 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.
×
×
  • Create New...