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)
$oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR
I have a strange behavior in an Autoit program.
The program works perfectly in the original environment I created the program for - for Windows 7 and Office 2010.
My workplace is migrating to Windows 10 with Office 2016. When I run this program in that new environment, the code actually executes without any errors, however, the excel process which was opened to read a spreadsheet/workbook does not close while the program is running. If you exit the program, the excel process ends at that point...
I could ignore this behavior - one little excel process hanging out there is not going to kill anything, however - it just bugs me !
While troubleshooting the issue, I placed a number of error checks after the excel close - and the close actually reports that it is successful.
I tried a few varieties of closing the excel process, and none of the methods tried seemed to actually work as well at the one in my code - the only downside being that it has to wait until the program finishes to actually close.
Any ideas on why an excel close would hang until program exit ?
Local $oExcel = _Excel_Open(False, False, False, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sUserRoleMenuXLS, False, true ) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening workbook '" & $sUserRoleMenuXLS & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel, False, False) Exit EndIf ; this section will find the user id in the first 3 columns of the user/menu spreadsheet, and if it finds it - returns the value stored in the cell 2 locations to the right... With $oExcel.ActiveSheet.Range("A:C").Find (@UserName) $Match = .Find (@UserName) If (Not IsObj($Match)) or (stringlen($Match.Offset(0, 2).value) = 0) Then MsgBox($MB_SYSTEMMODAL, 'UIPLauncher Error', 'No Menu assigned for user - Contact Support.') ; Display a warning if the script isn't compiled or the file doesn't exist. ;_Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, false, false) Exit Else Local $cResult = $Match.Offset(0, 2).value EndIf EndWith $oWorkbook.Saved = True _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, false, false) ;close does not seem to work on windows 10 and office 2016
$aTdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $LastRow) $aTDSLOC = _Excel_RangeRead($oWorkbook1, Default, "H1:H" & $LastRow) MsgBox(0, "TAKSY OUTPUT LAST ROW", $LastRow) _ArrayTrim($aTDSLOC, 1, 1) _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, $aTDSLOC, "H2") $oWorkbook1.Sheets("DS_List").Activate $mLastRow = $oWorkbook1.ActiveSheet.UsedRange.Rows.Count $aMdslist = _Excel_RangeRead($oWorkbook1, Default, "B1:B" & $mLastRow) MsgBox(0, "MASTER OUTPUT LAST ROW", $mLastRow) _Excel_RangeReplace($oWorkbook1, Default, "F2:F" & $mLastRow, "N/A", "n/a") ;check the requiremetn $aMLOC = _Excel_RangeRead($oWorkbook1, Default, "F1:F" & $mLastRow) ;LOC NUMBER NEEDS TO BE REPLACED WITH.. _ArrayDisplay($aTDSLOC) _ArrayDisplay($aMLOC) _ArrayDisplay($aMdslist) ;DATA COMPARISION BETWEEN TAKSY LIST WITH LOC FROM SAP TO MASTER LIST WITH LOC. $oWorkbook1.Sheets("OUTPUT").Activate For $i = 1 To $LastRow - 1 For $j = 1 To $mLastRow - 1 If $aTdslist[$i] == $aMdslist[$j] And $aTDSLOC[$i] <> $aMLOC[$j] Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 3 EndIf If $aTdslist[$i] == $aMdslist[$j] And $aMLOC[$j] == "n/a" And _Excel_RangeRead($oWorkbook1, Default, "I" & ($i + 2)) == "NO KIT" Then $oExcel.ActiveSheet.Range("H" & $i + 2).Interior.ColorIndex = 2 _Excel_RangeWrite($oWorkbook1, $oWorkbook1.ActiveSheet, "*", "H" & ($i + 2)) EndIf Next Next MsgBox($MB_SYSTEMMODAL, "Status", "Loc extraction, comparision and consolidation is Done", 2) DEAR TEAM, I am facing issue w.r.t subscript errors kindly help me in solving...
AutoIt3 Lua Wrapper
This is an AutoIt3 wrapper for the Lua scripting language. Consider it beta software, but since I will be using it in commercial product, expect it to evolve.
It has been developped with Lua 5.3.5. Updates will come for new Lua version.
Everything works just fine, except one (big) limitation: Anything that throws a Lua error (using C setjmp/longjmp functionality) will crash your AutoIt program. That means that it is impossible to use throw errors from an AutoIt function called by Lua (luaL_check*, lua_error...).
It is hosted in Github: https://github.com/matwachich/au3lua
#include <lua.au3> #include <lua_dlls.au3> ; Initialize library _lua_Startup(_lua_ExtractDll()) OnAutoItExitRegister(_lua_Shutdown) ; create new execution state $pState = _luaL_newState() _luaopen_base($pState) ; needed for the lua's print function $iRet = _luaL_doString($pState, 'print("Hello, world!")') If $iRet <> $LUA_OK Then ; read the error description on top of the stack ConsoleWrite("!> Error: " & _lua_toString($pState, -1) & @CRLF) Exit EndIf ; close the state to free memory (you MUST call this function, this is not AutoIt's automatic memory management, it's a C library) _lua_close($pState)
$oWorkbook = _Excel_BookAttach($sWorkbook) $oWorkbook.sheets("Sheet1").activate Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "") _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, $sMSN) can anyone help me here, excel filer is not working with the above code.. attached excel
Thank you in advance!!