
soonyee91
-
Posts
78 -
Joined
-
Last visited
Community Answers
-
soonyee91's post in Match values in Array with values in Excel was marked as the answer
drbyte,
The concept of the solution for your problem is the same like what we discuss before. Use _arraysearch to check whether there is a match. If there is mark X on the corresponding column.
You can refer to my code below:
#include <File.au3> #include <Array.au3> #include <Excel.au3> ; ------------------------------------------------------------------------------------ ; create pdf files in @scriptdir \test\ for testing ; ------------------------------------------------------------------------------------ Local $result[16] $result[0] = "Could be any name - ABCD001.pdf" $result[1] = "Could be any name - ABCD003.pdf" $result[2] = "Could be any name - ABCD005.pdf" $result[3] = "Could be any name - ABCD007.pdf" $result[4] = "Could be any name - ABCD009.pdf" $result[5] = "material-1a_124567.pdf" $result[6] = "material-1b_12345_589752467.pdf" $result[7] = "material-1c_12345_nesting.pdf" $result[8] = "material-1d_us_04030-2010-09-03.pdf" $result[9] = "material-1e_us_Certificaat.pdf" $result[10] = "material-1f_z35_SL1020_01.pdf" $result[11] = "material-1g_harden_W1305050.pdf" $result[12] = "material-1h_veredelen_SL1025_02.pdf" $result[13] = "material-1k_painting.pdf" $result[14] = "material-1i_ndt_RM0199.pdf" $result[15] = "material-1j_ndt_Certificaat.pdf" For $1 = 0 To UBound($result) - 1 FileOpen(@ScriptDir & '\test\' & $result[$1], 10) Next ; end creation pdf file's ; list all .pdf type files from a specific directory $atemp_Files = _FileListToArray(@ScriptDir & '\test\', '*.pdf', 1) _ArrayDisplay($atemp_Files, "Directory loaded") ; Remove all file names with "material-" in the pdf file For $i = UBound($atemp_Files) - 1 To 0 Step -1 If StringInStr($atemp_Files[$i], "material-", 0) Then _ArrayDelete($atemp_Files, $i) EndIf Next _ArrayDisplay($atemp_Files, "Material pdf's removed") ; create final result array with 2 dimensions Local $aFiles[UBound($atemp_Files)][2] ; populate 1ST dimension of final result array with complete file name (not path) For $1 = 0 To UBound($atemp_Files) - 1 $aFiles[$1][0] = $atemp_Files[$1] Next ; populate 2ND dimension of final result array with stripped out file name Local $aTmp For $1 = 1 To UBound($aFiles) - 1 $aTmp = StringRegExp($aFiles[$1][0], '[a-zA-Z]{1,4}[0-9]{1,3}', 3) If IsArray($aTmp) Then $aFiles[$1][1] = $aTmp[0] Next _ArrayDisplay($aFiles, "2nd Array filled") _ArrayDelete($aFiles, 0) ;this is just to let the workbook looks like your snapshot example Local $oExcel=_ExcelBookNew() _ExcelWriteCell($oExcel,"Number",1,1) _ExcelWriteCell($oExcel,"Report",1,2) _ExcelWriteCell($oExcel,"ABCD001",2,1) $oExcel.Range("A2").Select $oExcel.Selection.AutoFill($oExcel.Range("A2:A21"), 0) ; this is the code for matching with the column A with the array you got. For $i=0 to 19; based on the example you give: ABCD001 ~ ABCD020 if _arraysearch($aFiles,$oExcel.Cells($i+2,1).value,0,0,1,2,1,1) <>-1 Then _ExcelWriteCell($oExcel,"X",$i+2,2) EndIf Next -
soonyee91's post in New project advise was marked as the answer
Try this method:
For $1 = 0 To UBound($aFiles) - 1 Select Case StringInStr($aFiles[$1][0], '-1a') > 0 ConsoleWrite('Found '&$aFiles[$1][0]&' in element ' & $1 & ' of array' & @LF) Case StringInStr($aFiles[$1][0], '-1b') > 0 ConsoleWrite('Found '&$aFiles[$1][0]&' in element ' & $1 & ' of array' & @LF) Case StringInStr($aFiles[$1][0], '-1c') > 0 ConsoleWrite('Found '&$aFiles[$1][0]&' in element ' & $1 & ' of array' & @LF) Case StringInStr($aFiles[$1][0], '-1d') > 0 ConsoleWrite('Found '&$aFiles[$1][0]&' in element ' & $1 & ' of array' & @LF) Case StringInStr($aFiles[$1][0], '-1e') > 0 ConsoleWrite('Found '&$aFiles[$1][0]&' in element ' & $1 & ' of array' & @LF) Case StringInStr($aFiles[$1][0], '-1f') > 0 ConsoleWrite('Found '&$aFiles[$1][0]&' in element ' & $1 & ' of array' & @LF) EndSelect Next Or you can try this simplified version using _ArrayFindAll()
local $criteria, $array,$count, $write For $count=0 to 5 ; loop from a to f $array=_ArrayFindAll($aFiles,"-1"&stringlower(Chr(65 + $count)),0,0,1,1,0) ; use this function to retrieve the index similar criteria in array form For $write=0 to Ubound($array)-1 ; write to the console fo each of them ConsoleWrite('Found '& $aFiles[$array[$write]][0] &' in element ' & $array[$write] & ' of array' & @LF) Next Next ; Above function will be more simpler than using select, case method Both function will produce the same outcome.
-
soonyee91's post in Another Excel question from me was marked as the answer
Bearpocalpse,
I face the same problem like you last time. If you use _arraydisplay() and the results are in correct order that means your array is sorted in that order. The problem you face is when you want to display in listbox it help you sort again. The best way is you disable sorting in your listbox. I believe you create your listbox with default value (default value contain sorting which will sort your item but will not affect your array)
In my previous attempt. I used this code:
$hListBox = GUICtrlCreateList("", 10, 20, 390, 150, BitOR($WS_BORDER, $WS_VSCROLL, $WS_HSCROLL))
you can check help file for certain listbox criteria
you must use BitOr() to specify which listbox criteria you want.
As you can see my lisbox criteria is I want vertical and horizontal scroll and thin line border only
Hope my explaination helps you!
-
soonyee91's post in Need help to convert vba code to autoit code was marked as the answer
Water,
Thanks so much!!!! I know the intersect will return range object but never know how to deal with it. Learn a new thing from you!!!!
Problem solved!
The success converted code is as follow: (Hope this help others! This function will delete/hide everything outside of printarea of excel.)
Func _ClearOutsidePrintArea() ;place this code in a worksheet loop if you want to perform this function for the every excel sheet Local $oPrintarea=$oExcel.Activesheet.PageSetup.PrintArea ; Obtain print area from the active excel sheet If $oPrintarea="" Then $oPrintarea=$oExcel.Activesheet.UsedRange.Address ; Obtain Default excel print area if user never specify the print area Endif Local $rng = $oExcel.Activesheet.UsedRange Local $lastrow = $rng.Rows($rng.rows.Count).Row Local $lastcol = $rng.Columns($rng.Columns.count).Column For $i = $lastrow To 1 Step -1 Local $rw = $oExcel.rows($i) $oIntersect=$oExcel.Application.intersect($rw, $oExcel.Range($oPrintarea)) If Not isobj($oIntersect) Then $rw.EntireRow.Delete ; change the 'delete' to hide if you want to hide instead of delete EndIf Next For $i2 = $lastcol To 1 Step -1 Local $col = $oExcel.columns($i2) $oIntersect1=$oExcel.Application.intersect($col, $oExcel.Range($oPrintarea)) If not isobj($oIntersect1) Then $col.EntireColumn.Delete ; change the 'delete' to hide if you want to hide instead of delete EndIf Next EndFunc -
soonyee91's post in Data sorting in array was marked as the answer
I think I found the problem. The problem is because default listbox style will sort the element in it but it will not change the arrangement inside the variable. So the solution is to cancel sorting in listbox.
-
soonyee91's post in Send text to specific window control was marked as the answer
Haha, looks like the respond is good...
I manage to figure out...
Instead of using this vba line "expression.Printout". I change it to "expression.Select".
After this I choose to use either send/controlsend function for automate the task.
To avoid user input from disturbing the operation. I implement "Blockinput()".
That's all for this problem. Solved!
-
soonyee91's post in Remove Excel Modules was marked as the answer
Good news:
I think I've solved it using this:
Func _ClearVBA() $oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents For $oModule In $oModules If $oModule.Type = 1 Then $oModules.Remove($oModule) EndIf Next