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)
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
I'm trying to pass a nested array to a function, such that the function alters the inner array.
I was surprised to find that this minimal reproducible example, despite its use of ByRef, seems to pass a copy of the inner array to the function:
#include <Array.au3> ; a boring old array Local $aInnerArray = [1, 2, 3, 4, 5] ; a one-element array containing a reference to the other array Local $aOuterArray = [$aInnerArray] ; intention: take a nested array and alter its inner array ; reality: the inner array seems to be getting copied Func ChangeIt(ByRef $aOuter) Local $aInner = $aOuter $aInner = 0 EndFunc ; Expected: [1, 2, 3, 4, 5] ; Actual: [1, 2, 3, 4, 5] ✔ _ArrayDisplay($aInnerArray, 'Before') ; $aOuterArray passed by-ref, should receive reference to $aInnerArray ; Therefore should change $aInnerArray to [1, 2, 0, 4, 5] ChangeIt($aOuterArray) ; Expected: [1, 2, 0, 4, 5] ; Actual: [1, 2, 3, 4, 5] ✘ _ArrayDisplay($aInnerArray, 'After') I suspect that either:
the copy is taking place in the first line of the function (I couldn't find a way to access the inner array without first assigning it to a variable though); or ByRef doesn't propagate into inner levels of the data structure being passed, which seems less likely to me. Could someone please point me in the right direction to get this working as intended?
Update: the answer
; WRONG: ; a one-element array containing a reference to the other array Local $aOuterArray = [$aInnerArray] The assumption I made about this code is wrong—it actually copies $aInnerArray into $aOuterArray, so there are now two unrelated $aInnerArray instances. It is not possible to store arrays in other arrays by reference.
If it is necessary to refer to a mutable array in multiple places, consider holding it in a global variable. Where a collection of mutable arrays needs to be accessed in multiple places (as in my case), consider storing them in a global array and referring to each sub-array by index (also known as the Registry pattern).
is there any function in Auto it to Remove the repeating strings separated by "," which is present in the array.
thank you in advance!