How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
_Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
Has anyone come across this before?
$SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
So its imperative that the $value1, $value2, etc variables be used.
$FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input $value2 = $input $value3 = $input $value4 = $input _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next
I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program.
I don't want range to be specific to a workbook, or sheet, or set of cells.
I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C").
Could someone help me with this?
I've tried to write the framework: (edited)
#include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc
Hey. I'm working on a new project and was wondering if there is a better way to "update" my Column E array.
Here is my code:
Local $nI = 0 ;Creates a name index of 0: nI = Name index Local $nII = 1 ;Creates a name index of 1 for second loop: nII = Name Index 2 For $iN = 0 To $IndexRows Step 1 ;Checks the roster for any names that appear twice For $iN2 = 0 To $IndexRows Step 1 if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then Local $timeSheetName = _ArraySearch($e_Names, $d_Names[$nI], 0, 0, 0, 0, 1) ;MsgBox($MB_SYSTEMMODAL, "Found it", $d_Names[$nI] & " In column E on Row " & $timeSheetName) Local $eI = $timeSheetName + 1 ;ConsoleWrite($timeSheetName & @CRLF) ;ConsoleWrite($eI & @CRLF) ;ConsoleWrite(@CRLF) _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $eI & ":F" & $eI, $xlShiftDown) ;Inserts a empty cell in columns E and F. _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $eI) ;Fills the empty cell in columns E with the doubled name $aArray_Index = 2 ;Array element counter For $Index = 2 To $IndexRows Step 1 ;Loops through every row in the Excel file unto no rows are found or a null row is found $Array_Value_E = _Excel_RangeRead($OpenWorkbook, Default, "E"&$Index) $e_names[$aArray_Index] = $Array_Value_E ;While the code loops every value in column E is stored in the E array (updating the array) $aArray_Index += 1 Next ExitLoop EndIf Next $nI += 1 $nII += 1 Next Basically, It checks a roster for people whose name appears twice then inserts a new "row" for that person because they work in two different departments.
I have to find that name however in Column E if two appear in column D. My code works but I think it is not as efficient as it could be.
Any ideas on how to improve the "update" for my array?
(once it finds the double names in Column D it then searches for that name by going name by name in the Column E array and once it finds it inserts a new row. However, the E array doesn't have that new row stored in it so I have to "update" the array to properly find the next name)
Any and all tips would be greatly appreciated.
NOTE: Just assume I'm opening the excel file properly please do not add that code in, it only complicates your answer.