-
Recently Browsing 0 members
No registered users viewing this page.
-
Similar Content
-
By roeselpi
hello again,
it has been a long time since i have been here and a long time since i last used autoit. ever so often when the time allows me to, then i follow up on an idea that i had a long time ago. i have done all the work on paper but now it is up to writing it in autoit and i keep stumbling over many little issues here and there. sometimes after a few days i will try again and get a step further but sometimes it just will not help no matter how long i try and think about a solution. for most of you it will be the basics but for me it is not all that easy, but at least i give it a try.
right, down to business:
here is my code:
#include <MsgBoxConstants.au3> #include <StringConstants.au3> #include <Array.au3> #include <String.au3> ; ; PART 1: define replacements and check with msgbox ; Global $y, $z $y = "Yes" $z = "No" MsgBox(0,"replacements", $y & @CRLF & $z) ;the replacements in a message box ; ; PART 2: set the texts and check via console and msgbox ; Global $my1string = "abab" ;the first specified text MsgBox(0,"my1string", $my1string) ;the message box to output the first specified text Global $my2string = "icic" ;the second specified text MsgBox(0,"my2string", $my2string) ;the message box to output the second specified text ; ; PART 3: transform the strings to individual arrays ; $my1array = StringSplit($my1string, "") $my1array[0] = "" _ArrayDelete($my1array, 0) _ArrayDisplay($my1array, "my1array") ;the display of the first specified array $my2array = StringSplit($my2string, "") $my2array[0] = "" _ArrayDelete($my2array, 0) _ArrayDisplay($my2array, "my2array") ;the display of the first specified array ; ; PART 4: create an empty array for filling ; Global $OutputArray[4] $OutputArray[0] = "" _ArrayDisplay($OutputArray, "OutputArray") ;the display of the first specified array ; ; PART 5: compare & fill empty OutputArray with data after evaluation ; Global $i, $j, $k For $i = 0 to UBound($my1array) -1 For $j = 0 to UBound($my2array) -1 For $k = 0 to UBound($OutputArray) -1 If $my1array[$i] = "a" And $my2array[$j] = "i" Then $OutputArray[$k] = $y Else $OutputArray[$k] = $z EndIf Next Next Next _ArrayDisplay($OutputArray, "OutputArray") ;the display of the Newly filled Array In "Part 2" i make a string that is converted to an array in "Part 3" ... Now, I know that "a" and "i" are always in the exact same spot in both arrays and so i wanted to compare this and make a further array to document my findings by saying "yes" or "no" ... however my new array keeps saying just "no" allthough i can clearly see and know that it should say:
yes no yes no my guess is that there is something wrong within my for-loops and that the counting is somehow "off" i guess that when the first for-loop is finished it reaches the second whilst the second for-loop is checking the first which would explain why it always says "no" instead of seeing the obvious.
so my question would be: what is wrong with my for-loop? or where am i making an error that ultimately gives me the wrong results?
help is much appreciated.
kind regards
roeselpi
PS: sorry for my not so great english spelling ... stupid german sitting here trying out intermediate english skills.
-
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 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.
-