soonyee91

I think you forget to put "Endif"

You'll have to choose which variable you would like to use. (Since the function is the same) *Looks like you haven't fully understand why _ExcelbookAttach is needed in this situation. (This function will return the object pointing to your active workbook). The returned variable is needed for other function such as _excelwritecell, & etc You can look at the help file for more clarification

Oh, I think I get your points now. So, let me summarize it: 1. You have already opened the excel files and then click on the macro to perform something. 2. Then you have autoit to perform your other function. 3. Autoit fails because it open in read only mode. (Am I right?) The reason that autoit is open the excel in read mode is because of the way you code it. (Put your code here for me to know the specific cause) The solution is you obtain com object reference from your open excel then only perform your code. The function you should look into is ObjGet (more details can be found in helpfile).

Can you explain in details? What do you want to achieve? If you can do it in vba, definitely you can convert it to autoit language( vice versa) or can you describe the process that you are going to perform? it will be easier for me to understand

My suggestion: If you're going to use a button in excel, why not just use vba to do the automation and write back to the cell if got any changes to the files? Anyhow, autoit is possible to perform on your described issue.

As far as I know, DirRemove can't remove some dir attributes (which make the removal impossible). Based on my knowledge, I think runwait is the best to solve your problem...

Try using Runwait function. Remarks from help file: Paths with spaces need to be enclosed in quotation marks. To run DOS commands, try RunWait(@ComSpec & " /c " & "commandName") ; don't forget " " before "/c"

I have tried using DirRemove to delete folder and it works. Post your code here to have a look?

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

drbyte, Its good to know you can understand both solution. Personally I would prefer 2nd solution because of shorter structure. XD

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.

I think I can't really get what you want to do. if you obtain the file names using fileopendialog/any similar function. The files that stored in the array will be according to "window's sorting condition". You may sort them to your own prefrence using arraysort function. If you use arraydelete to remove certain array position it will reorganise the index hence not the fixed position you want. Or you can try set that array position to nothing. For example: array[0] ="" ; means for the first term set it to nothing. By the way, there are alot of ways to do it. Can't really give you example as I can't understand your criteria well, perhaps kylomas can...XD)

kylomas, Lol, you did not consider as plagarized as you have quoted my name... by the way, your code teach me how to deal with 2nd dimension array(learning array part in process)...

I think this line can be converted to autoit code. You may need to include this line before your objcreate code: Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") Func _ErrFunc() ;put in the code you want to convert here EndFunc ;==>_ErrFunc Between, I tested both water and your code. Both scripts are running quite fast (within 1-2sec)

Ok, this is one of the way to write the prefix "material-1a, material-1b & etc" to excel range A1 in column order: #include <Array.au3> #include <Excel.au3> Local $result[8],$result1 ; below is just some example for you to start ;you can use fileopendialog to retrieve multiple pdf files from certain directory ; then you convert the return value(obtained from fileopendialog) into array ; use pathsplit to obtain filenames only $result[0] = "material-1a_124567.pdf" $result[1]="material-1b_12345_12345.pdf" $result[2]="material-1c_12345_nesting.pdf" $result[3]="material-1d_us_04030-2010-09-03.pdf" $result[4]="material-1e_z35_SL1020_01.pdf" $result[5]="material-1f_W1305050.pdf" $result[6]="material-1g_SL1025_02.pdf" $result[7]="material-1h_painting.pdf" Local $oExcel=_ExcelBookNew() For $i =0 to 7 $result1=stringsplit($result[$i],"_",2) _ExcelWriteCell($oExcel,$result1[0],$i+1,1) next Msgbox(0,"","Write to excel file complete! Excel will close without saving!") _ExcelBookClose($oExcel,0) $oExcel=""

drbyte, There are many ways to do it. 1. To allow user to choose multiple files from a directory("use fileopendialog or Melba's RecFileListToArray") 2. To obtain filenames, use pathsplit function or other similar function 3. stringsplit function can be use to split the strings into array by specify delimeters >>>show in example below #include <Array.au3> Local $result[8],$result1 ; below is just some example for you to start ;you can use fileopendialog to retrieve multiple pdf files from certain directory ; then you convert the return value(obtained from fileopendialog) into array ; use pathsplit to obtain filenames only $result[0] = "material-1a_124567.pdf" $result[1]="material-1b_12345_12345.pdf" $result[2]="material-1c_12345_nesting.pdf" $result[3]="material-1d_us_04030-2010-09-03.pdf" $result[4]="material-1e_z35_SL1020_01.pdf" $result[5]="material-1f_W1305050.pdf" $result[6]="material-1g_SL1025_02.pdf" $result[7]="material-1h_painting.pdf" For $i =0 to 7 $result1=stringsplit($result[$i],"_",2) _arraydisplay($result1) next Hope this can help you to start with your project!

Bearpocalypse, No problem. Its my pleasure to help you.

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!

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

Water, After trying with the beta version that support null, syntax error occur specifically on that line. So i think we still need to use ""

BrewManNH, After set it to "" instead of nothing. The code can runs however the result is not the same as the vba result. I think there is problem in the code.

This is what I have tried so far Original vba code: Sub DDD() Dim rng As Range, lastRow As Long Dim lastCol As Long, i As Long Dim rw As Range, col As Range Set rng = ActiveSheet.UsedRange lastRow = rng.Rows(rng.Rows.Count).Row lastCol = rng.Columns(rng.Columns.Count).Column For i = lastRow To 1 Step -1 Set rw = Rows(i) If Intersect(rw, Range("Print_Area")) Is Nothing Then rw.EntireRow.Delete End If Next For i = lastCol To 1 Step -1 Set col = Columns(i) If Intersect(col, Range("Print_Area")) Is Nothing Then col.EntireColumn.Delete End If Next ActiveSheet.UsedRange End Sub Autoit Code: (Conversion not completed) Syntax error on this line: "If $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area")) = nothing Then" Func _ClearOutsidePrintArea() 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) If $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area")) = nothing Then $rw.EntireRow.Delete EndIf Next For $i2 = $lastcol To 1 Step -1 Local $col = $oExcel.columns($i2) If $oExcel.Application.intersect($col, $oExcel.Range("Print_Area")) = nothing Then $col.EntireRow.Delete EndIf Next EndFunc So far no clue on how to fix the syntax error, any help is appreciated.

thx JohnOne & MilesAhead, Both of you give me a great idea to deal with my problem. Once again the problem is solved!

JohnOne, I'm actually understand your code well. But how should I use your code? What I mean is the array of file names return using Func _FileOpenDialogArray() include the number of files at the array[0]("first element"). How can I start to sort the array at array[1] instead at the position array[0]?

Can you explain more on how you want to automate the task? Are you dealing with names in excel files content or dealing with excel filenames? Basically autoit help file can help you or you can try google around?