the123punch

ExcelCombine.au3 I have attached my entire code. Hopefully that helps better. You can compile and see what happens. the123punch

Hi all, Any help with this will be greatly appreciated, since I am spending way more time than I originally had planned for this. I am creating a script which is supposed to create a GUI and allow user input to select multiple Excel files, provide columns numbers to copy, and extract all the contents from these Excel files into 1 final Excel destination file. The GUI creation has already been taken care of and works great. However, in my manipulation of multiple Excel documents, I am failing at the writing process of the final Excel document (the line which writes is in Bold and Italic so you can find it in the below code). I always obtain the following error message (which is attached). Can you please help!? Thanks! the123punch Func Process() $destRow = 1 $curSrcCells = "" Dim $allSrcCells[1][1] $curDestCell = "" $destSheetName = "" $destFileName = "" If validateData() Then $array = StringSplit(GUICtrlRead ($txtFiles2Extract), "|") $destFileName = GUICtrlRead ($txtFile2Create) $destExcelFile = _ExcelBookOpen($destFileName, 1) ;_ArrayDisplay($array) $destSheetName = StringMid($array[2],1,30) _ExcelSheetDelete($destExcelFile, $destSheetName) _ExcelSheetAddNew($destExcelFile, $destSheetName) ;MsgBox(0,"TEST1234124", "TESTING");GUICtrlRead ($txtColumns2Extract)) $colsArray = StringSplit(GUICtrlRead ($txtColumns2Extract), ",") ;_ArrayDisplay($colsArray) ;;looping through array variable to open source file names one by one For $i=2 to $array[0] Step 1 $srcRow = 1 ;MsgBox(0, "TEST", $array[1] & "\" & $array[$i]) $srcExcelFile = _ExcelBookOpen($array[1] & "\" & $array[$i], 0) ;_ExcelSheetActivate($srcExcelFile, 1) $curSrcCells = _ExcelReadSheetToArray($srcExcelFile) ;_ArrayDisplay($curSrcCells) _ExcelBookClose($srcExcelFile, 0) ;;;;;looping through file to get information from specified cells;;;; For $j=1 to $curSrcCells[0][0] Step 1 ;verifying whether the current row has data at the specified columns For $k = 1 to $colsArray[0] Step 1 ;writing from the source document to the destination document ;MsgBox(0, "Variables", "$j=" & $j & " $curSrcCells[0][0]=" & $curSrcCells[0][0] & " $k=" & $k & " $colsArray[0]=" & $colsArray[0] & " $srcRow= " & $srcRow & " $destRow= " & $destRow) ;MsgBox(0, "Variables", "$curSrcCells[$j][$colsArray[$k]]=" & $curSrcCells[$j][$colsArray[$k]]) $destExcelFileAttached = _ExcelBookAttach($destFileName) ;_ExcelSheetActivate($destExcelFileAttached, $destSheetName) MsgBox(0, "Writing", "Writing cell") [i][b]_ExcelWriteCell($destExcelFileAttached, $curSrcCells[$j][$colsArray[$k]], $destRow, $colsArray[$k]) [/b] [/i] Next ;incrementing row variables $srcRow = $srcRow + 1 $destRow = $destRow + 1 Next ;;;;;end loop;;;; Next ;_ArrayDisplay($array) _ExcelBookClose($destExcelFile, 1) ;saving the file before closing _ExcelBookOpen(GUICtrlRead ($txtFile2Create), 1) ;re-opening the saved file (and making it visible) ;MsgBox (0, "YES!", "TEST YES!") EndIf EndFunc

Yes I know, and I modified it to accept multiple spaces after a comma. Thanks a bunch. the123punch

Hi czardas, I tried your regular expression and it seemed to work well on all stress tests that I gave it. Even the previous one which did not work with the other regular expression ("1,2,3f,5"). It solves my problem! Thanks a lot! the123punch

Thanks for your quick answer. I tried your regular expression and I think that we are almost there. However, I tried many strings to test it and it wrongly accepted the following string: "1,2,3f,5" What can possibly cause that? the123punch

Hi all, Excuse my newbiness with regular expressions since I have never used them with AutoIt. I would like to validate some user input using a regular expression. The user is supposed to input a string enumerating numbers (ex.: 1,5,78,46,35,7) There is no limit to the amount of numbers they input, but they have to be separated by commas. Also, we should accept spaces after commas. The last number should not be followed by a comma. Also, we should accept it if a user input only 1 number (ex.: 1) This is what I tried using but it is not working, and it is probably totally due to my newbiness with Regular expressions in AutoIt. If (IsInt ($txtTemp2) = False And StringRegExp($txtTemp2,"[\d+,]+[\d+]") =0) Then MsgBox(0, "Columns not in correct format", "Please enumerate column(s) in between commas! ex.(1,3,5)") End IfI would appreciate any help! the123punch

Hi Melba, I am not using any other scripts to lock the files or anything like that. I tried to undo the changes but it does not work. For some reason, it gives me the following error (attached). It seems that it tries to undo a DELETE action... Maybe I am doing something wrong in my script? the123punch

I have written a script in AutoIt that would take files or folders from the command line and rename them according to user input (adding a prefix or a suffix to the name). The script is intended to be used to rename several files in a bulk by adding a prefix or a suffix to the name. The script works well so far. I have one problem though. If the user makes a mistake in the renaming process, they cannot undo the renaming in Windows Explorer. I tried manually renaming a file in Windows Explorer and undoing works usually. However, when the renaming is done from my script, we cannot undo it. I would like to add the possibility to undo the changes when requested. It seems to me (and I could be wrong) that the action of renaming must be regsitered in Windows as part of the queue of the last actions that were made in order for it to be elligible to be undone. Am I correct? Is there anyone who has experience with that who could help me out? I am adding my script in a code snippet, hopefully this could help understand the problem. Any help will be really appreciated. the123punch FileRename.au3

Thank you both. I tried both and they work just fine! the123punch

Thanks picaxe! This works great! The only and last thing that does bother me with that, is that if the file exists, it will keep asking me whether I want to overwrite it. Since this is a script that I am developing to run automatically, I don't want to have to click on "Yes" to overwrite the file everytime. Is there a flag that I can put to overwrite the file when saving it in the line that says: .ActiveWorkBook.SaveAs (@ScriptDir & "\" & $aSheets[$i]) ?? Many thanks! the123punch

Hi again, I tried doing it on my own and I did the following inside the looping through worksheets of the Excel document: _ExcelSheetActivate($oExcel, $i) ;Using the Index of the Array $Name = _ExcelSheetNameGet($oExcel) _ExcelCopy($oExcel, 1, 1, 100, 20) $nBook = _ExcelBookNew(0);- 1 or Blank If visible _ExcelSheetActivate($nBook, 1) ;Using the Index of the Array _ExcelPaste($nBook, 1) This seems that it should work and keep the formatting as it is a usual copy/paste of the Excel cells. However, I keep getting the following error message: "Unable to get the Paste property of the Worksheet class" Anyone knows how to solve this? Thanks. the123punch

Hi lordicast, I just tried your code and it works like a charm! Thank you. However, the only thing is that it doesn't keep the formatting. The sheet is copied with no formatting whatsoever. Do you know any way where we can keep the formatting including fonts, borders, and cell height-width? Thanks. the123punch

Hi all, I am using the Excel UDF to write a report to an Excel document. My report consists of several sheets (~15 sheets). I am able to generate the report programatically without any problem, and save the entire workbook as well. However, I want to be able to save each worksheet separately. Is that possible with the UDF? I tried to manipulate code from the Excel UDF but I couldn't get with any result and kept getting errors. Ideally, I would like to loop through the Workbook object and save each worksheet individually as an Excel Workbook document. Each file would be named according to the worksheet name. Thanks. the123punch

Hi, How would I go to search for one particular file to see if it is open? In the case it is open, I would want to close it. Instead of $element.Path can we use $element.FullPath?? Thanks. the123punch

That is great. It worked a charm. Thanks a lot PsaltyDS for the demos. the123punch

While I'm at it, I am wondering another thing. On the same train of thought, if I wanted to select the value of the <location> tag from the 2nd <item> which has itemtype as "emp".. How do I do that? I tried this: $location= _XMLValue("/list/item[itemtype='emp'][" & $i & "]/location") It returned an empty string... I am baffled at how to do it cuz I tried so many things... Thanks a lot. the123punch

Worked awesome!! Many thanks. the123punch

I'm sorry. The file I sent you is in French. It works on my end but maybe not on yours. I removed all the accented characters in this file so you can test it. Thanks. the123punch List.xml

Please find attached a test sample of the XML file in question. The <item> are not directly at the root level. But I tried the following: $empCount = _XMLGetNodeCount("/list/item[itemtype=emp]") which should return the count of all the items that have "emp" as itemtype but it returns -1. Thanks. the123punch List.xml

I tried what you are suggesting, but the function returns -1. Did you test it? the123punch

Hi all, I am wondering if we can use the _XMLGetNodeCount() function to get the node count for a node based on a value. For example, I have an XML file that lists different computers and their parameters. These computers were created for different purposes. I am trying to get the number of computers that were created for "computation" purpose. Here is a snippet of my XML document: <item> <id>1</id> <Name>pc-instrumentcomp</Name> <Dept2>IT</Dept2> <lat>45.5747478365114</lat> <lng>-73.7595677375793</lng> <location>B104</location> <floor>2</floor> <purpose>computation</purpose> </item> Basically I would like to get the node count for all items that have purpose "computation". $iProbClones = _XMLGetNodeCount("/item/purpose") will get me a count of all items in the XML file. Can anyone help? Thanks. the123punch

Hi, I looked at the code for arrays and binary search only seems to work on single dimension arrays. I am wondering, can we get a 1D-array from a 2D array? For example I have a 2D array: Dim $probes[2][20] How can I get the array corresponding to the first column of my 2D arrays?? In most programming languages I know, we can do that by referring to $probes[1] or $probes[0], but it doesn't seem to work in Autoit.. My goal would be to have a binary search on the first column of my array. Thanks. the123punch

Hi water, Thanks for the efficient reply. Using this function, I got all the OU's of the user and I simply had to use a StringInStr() function to detect whether or not the user polled is in the specific OU. Thanks a lot, it solved my issue the123punch

Hi all, I have a certain manipulation that I must do in the Active directory. I have a script that does a couple of things using the AD (testing for attributes etc...) and then creates a user into another system using the attributes in the AD. The way our AD is implemented, different types of employees (example: PERMANENT vs CONTRACTUAL) are part of different OUs, and so to know what type of employee the user is, I need to know from which OU he belongs to.. I am using the UDF adfunctions.au3 but I could not find a straight forward way to retrieve the user's OU given the username.. Is there such a way with that UDF or with any other technique?? thanks. the123punch

Hi ofLight, Thank you for your prompt response. It all worked out really fine. Thanks a lot for your suggestion. The only thing that did not work though, is that this script will have to run periodically (every day at a specific hour), where it will search for text files on the server, and replace some text in it with other text. Now, when I run my script with a Windows session open, it works. It can go open files, change them, rewrite them, etc... When I add it in the planned task scheduler, it does not work. As if the file I/O does not work when I am not logged in a session. Here is the code that works when logged in: ;getting parameters from command line $txtFile = $CmdLine[1] $str2Find = $CmdLine[2] $str2Replace = $CmdLine[3] ;opening file for reading $file = FileOpen($txtFile, 0) If $file <> -1 Then ;getting file contents $fc = FileRead($file) ;replacing the string to find with the string to replace $fc = StringReplace($fc, $str2Find, $str2Replace) FileClose($file) ;re-writing everything back to the file $file = FileOpen($txtFile, 2) FileWrite($file, $fc) FileClose($file) Else Exit EndIf Anyone has any idea? the123punch