MaCgyver Posted July 27, 2006 Posted July 27, 2006 (edited) I made the following for a CSV file. Now I need to make one very similar for an Excel file. ;Select file to calculate $message = "Select" $var = FileOpenDialog($message, "C:\", "(*.csv)", 1 ) $file1 = FileOpen($var, 0) $file2 = FileOpen('C:\au3\The Average.csv',2) ; Check if file opened for reading OK If $file1 = -1 Then MsgBox(0, "Error", "Unable to READ file.") Exit EndIf FileClose($file1) FileClose($file2) Global $A_Percent = 0, $A_Count = 0 Global $B_Percent = 0, $B_Count = 0 $file1 = FileOpen($var, 0) $file2 = FileOpen('C:\au3\The Average.csv',1) ; Read in lines of text until the EOF is reached While 1 $line = FileReadLine($file1) If @error = -1 Then ExitLoop $data = StringSplit($line, ",") If $data[1]= "A" AND $data[7] >= "0" Then $A_Percent = $A_Percent + $data[7] $A_Count = $A_Count + 1 FileWriteLine($file2,$line) $APercentage = $A_Percent / $A_Count EndIf Wend FileWriteLine($file2,$A_Count) FileWriteLine($file2,$A_Percent) FileWriteLine($file2,$APercentage) FileClose($file1) FileClose($file2) $file1 = FileOpen($var, 0) $file2 = FileOpen('C:\au3\The Average.csv',1) ; Read in lines of text until the EOF is reached While 1 $line = FileReadLine($file1) If @error = -1 Then ExitLoop $data = StringSplit($line, ",") If $data[1] = "B" AND $data[7] >= "0" Then $B_Percent = $B_Percent + $data[7] $B_Count = $B_Count + 1 FileWriteLine($file2,$line) $BPercentage = $B_Percent / $B_Count EndIf Wend FileWriteLine($file2,$B_Count) FileWriteLine($file2,$B_Percent) FileWriteLine($file2,$BPercentage) FileClose($file1) FileClose($file2) Edited July 28, 2006 by MaCgyver
Xenobiologist Posted July 28, 2006 Posted July 28, 2006 HI, I haven't looked closer to your script, but maybe the excel udf in Scripts & Scraps helps out. Have a look! So long, Mega Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times
MaCgyver Posted July 28, 2006 Author Posted July 28, 2006 HI, I haven't looked closer to your script, but maybe the excel udf in Scripts & Scraps helps out. Have a look! So long, Mega Ok I will....I was running into different problems but I got the major one solved. This is basically the part I would like to see code for excel. While 1 $line = FileReadLine($file1) If @error = -1 Then ExitLoop $data = StringSplit($line, ",") If $data[1]= "A" AND $data[7] >= "0" Then $A_Percent = $A_Percent + $data[7] $A_Count = $A_Count + 1 FileWriteLine($file2,$line) $APercentage = $A_Percent / $A_Count EndIf Wend FileWriteLine($file2,$A_Count) FileWriteLine($file2,$A_Percent) FileWriteLine($file2,$APercentage)
randallc Posted July 28, 2006 Posted July 28, 2006 Hi, something like this? expandcollapse popup#include"ExcelCom.au3" #include "Array2D.au3" #include "_ArrayView2D1D.au3" ; $FilePath2=@ScriptDir&"\Blank6.xls" $FilePath1=@ScriptDir&"\Blank5.xls" ;~ FileDelete($FilePath1) ;~ FileDelete($FilePath2) _XLCreateBlank($FilePath1) _XLCreateBlank($FilePath2) $DataString="A,7,A,9,23,45,A,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"&@CRLF&"11,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18" $DataString&=@crlf&$DataString $DataString=StringReplace($DataString,",",@TAB) $XLRange=_XLpaste($FilePath1,1,"A",1,$DataString,0) ;================================================ ;START HERE WITH FILES KNOWN ;================================================ ;~ $FilePath2=@ScriptDir&"\Blank6.xls"; input ;~ $FilePath1=@ScriptDir&"\The Average.csv";'C:\au3\The Average.csv' local $XLArray2[1][1] Global $A_Percent = 0, $A_Count = 0 Global $B_Percent = 0, $B_Count = 0 $XLArray1=_XLArrayRead($FilePath1,1,"usedrange") _XLClose($FilePath1,1) ;========================================= for $i=0 to UBound($XLArray1)-1 $Array2DTo1String=_Array2DTo1String( $XLArray1, $i, "Array contents",0 , 1) if $XLArray1[$i][0] = "A" AND $XLArray1[$i][6] >= "0" Then $A_Percent = $A_Percent + $XLArray1[$i][6] $A_Count = $A_Count + 1 _ArrayInsert2Dst( $XLArray2, $Array2DTo1String) $APercentage = $A_Percent / $A_Count EndIf Next _ArrayInsert2Dst( $XLArray2, $A_Count) _ArrayInsert2Dst( $XLArray2, $A_Percent) _ArrayInsert2Dst( $XLArray2, $APercentage) _XLArrayWrite($XLArray2, $FilePath2,1,1,1,0 ,1,0) _XLShow($FilePath2,1)Best, Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
MaCgyver Posted July 28, 2006 Author Posted July 28, 2006 (edited) Thanks Randall...I am going to work on this in a few minutes. I just wanted to know why is this line structured this way?Like why is certain numbers listed and others not?$DataString="A,7,A,9,23,45,A,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"&@CRLF&"11,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18" Edited July 28, 2006 by MaCgyver
randallc Posted July 28, 2006 Posted July 28, 2006 (edited) Hi, Just made a random file to work with some data... you wouldn't need this a s you already have your file... Best, Randall Edited July 28, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
MaCgyver Posted July 28, 2006 Author Posted July 28, 2006 Hi,Just made a random file to work with some data...you wouldn't need this a s you already have your file...Best, RandallLooks like I don't have this file "_ArrayView2D1D.au3". Can anyone point me to the link? I did a search with no results. :">
MaCgyver Posted July 28, 2006 Author Posted July 28, 2006 Getting a message... " A file named '2.xls' already exists in this location. Do you want to replace it?" It doesn't matter if I choose yet or no I eventually get this error "Error: Could not open 2 as an Excel Object"Then I get autoit errors in regards to newname. This happens when I use the following code: #include"ExcelCom.au3" #include "Array2D.au3" ;#include "_ArrayView2D1D.au3" ;<-----Do I need? I don't have, so I don't know if this is the problem. ;Select file to calculate $message = "Select" $var = FileOpenDialog($message, "C:\", "(*.xls)", 1 ) $FilePath2 = FileOpen($var, 0) $FilePath1 = FileOpen('C:\au3\The Average.xls',2 + 8 + 16) FileClose($FilePath1) FileClose($FilePath2) local $XLArray2[1][1] Global $A_Percent = 0, $A_Count = 0 Global $B_Percent = 0, $B_Count = 0 Global $APercentage = $A_Percent / $A_Count $XLArray1=_XLArrayRead($FilePath1,1,"usedrange") _XLClose($FilePath1,1) ;========================================= for $i=0 to UBound($XLArray1)-1 $Array2DTo1String=_Array2DTo1String( $XLArray1, $i, "Array contents",0 , 1) if $XLArray1[$i][0] = "A" AND $XLArray1[$i][6] >= "0" Then $A_Percent = $A_Percent + $XLArray1[$i][6] $A_Count = $A_Count + 1 _ArrayInsert2Dst( $XLArray2, $Array2DTo1String) EndIf Next _ArrayInsert2Dst( $XLArray2, $A_Count) _ArrayInsert2Dst( $XLArray2, $A_Percent) _ArrayInsert2Dst( $XLArray2, $APercentage) _XLArrayWrite($XLArray2, $File2,1,1,1,0 ,1,0) _XLShow($File2,1) And when I use the following code I don't get the desired results in the file. I get 0 0 -1.#INDThe code is this: #include"ExcelCom.au3" #include "Array2D.au3" ;#include "_ArrayView2D1D.au3" ;<-----Do I need? $FilePath1=@ScriptDir&"\The Average.xls";'C:\au3\The Average.csv' $FilePath2=@ScriptDir&"\Blank6.xls"; input local $XLArray2[1][1] Global $A_Percent = 0, $A_Count = 0 Global $B_Percent = 0, $B_Count = 0 Global $APercentage = $A_Percent / $A_Count $XLArray1=_XLArrayRead($FilePath1,1,"usedrange") _XLClose($FilePath1,1) ;========================================= for $i=0 to UBound($XLArray1)-1 $Array2DTo1String=_Array2DTo1String( $XLArray1, $i, "Array contents",0 , 1) if $XLArray1[$i][0] = "A" AND $XLArray1[$i][6] >= "0" Then $A_Percent = $A_Percent + $XLArray1[$i][6] $A_Count = $A_Count + 1 _ArrayInsert2Dst( $XLArray2, $Array2DTo1String) EndIf Next _ArrayInsert2Dst( $XLArray2, $A_Count) _ArrayInsert2Dst( $XLArray2, $A_Percent) _ArrayInsert2Dst( $XLArray2, $APercentage) _XLArrayWrite($XLArray2, $FilePath2,1,1,1,0 ,1,0) _XLShow($FilePath2,1)
MaCgyver Posted July 28, 2006 Author Posted July 28, 2006 This script works when working with .csv files. I would like something that works with .xls files. I don't want .csv involved at all. It would be lovely if I could just substitute everything that says ".csv" with ".xls" and my script would be done. Unfortunately it is not that easy. A bit frustrated at this point, so I will paypal someone 10 bux who can put togther a fully working script for me. I guess replace whatever in this script that will allow me to select the excel file that has the data and to save the new file as an excel file with the calculation(averages). ;Select file to calculate $message = "Select" $var = FileOpenDialog($message, "C:\", "(*.csv)", 1 ) $file1 = FileOpen($var, 0) $file2 = FileOpen('C:\au3\The Average.csv',2) ; Check if file opened for reading OK If $file1 = -1 Then MsgBox(0, "Error", "Unable to READ file.") Exit EndIf FileClose($file1) FileClose($file2) Global $A_Percent = 0, $A_Count = 0 Global $B_Percent = 0, $B_Count = 0 $file1 = FileOpen($var, 0) $file2 = FileOpen('C:\au3\The Average.csv',1) ; Read in lines of text until the EOF is reached While 1 $line = FileReadLine($file1) If @error = -1 Then ExitLoop $data = StringSplit($line, ",") If $data[1]= "A" AND $data[7] >= "0" Then $A_Percent = $A_Percent + $data[7] $A_Count = $A_Count + 1 FileWriteLine($file2,$line) $APercentage = $A_Percent / $A_Count EndIf Wend FileWriteLine($file2,$A_Count) FileWriteLine($file2,$A_Percent) FileWriteLine($file2,$APercentage) FileClose($file1) FileClose($file2) $file1 = FileOpen($var, 0) $file2 = FileOpen('C:\au3\The Average.csv',1) ; Read in lines of text until the EOF is reached While 1 $line = FileReadLine($file1) If @error = -1 Then ExitLoop $data = StringSplit($line, ",") If $data[1] = "B" AND $data[7] >= "0" Then $B_Percent = $B_Percent + $data[7] $B_Count = $B_Count + 1 FileWriteLine($file2,$line) $BPercentage = $B_Percent / $B_Count EndIf Wend FileWriteLine($file2,$B_Count) FileWriteLine($file2,$B_Percent) FileWriteLine($file2,$BPercentage) FileClose($file1) FileClose($file2)
randallc Posted July 28, 2006 Posted July 28, 2006 (edited) Hi, *** I think I could get this going if you posted an example xls file with the data you are using.. **** Best, randall PS for some reason you have left out $APercentage = $A_Percent / $A_Count? Randall QUESTIONS: [1.Does your real data have blank lines? 2. Did you get decent output when you used my test script and data anyway?] Edited July 28, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
randallc Posted July 28, 2006 Posted July 28, 2006 Hi, Sorry; my script was bugged; The Array transpose as Excel arrays around the other way. expandcollapse popup#include"ExcelCom.au3" #include "Array2D.au3" #include "_ArrayView2D1D.au3" ; $FilePath2=@ScriptDir&"\Blank6.xls" $FilePath1=@ScriptDir&"\Blank5.xls" ;~ FileDelete($FilePath1) ;~ FileDelete($FilePath2) _XLCreateBlank($FilePath1) _XLCreateBlank($FilePath2) $DataString="A,7,2,9,23,45,2,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"&@CRLF&"A,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18" $DataString&=@crlf&$DataString $DataString=StringReplace($DataString,",",@TAB) $XLRange=_XLpaste($FilePath1,1,"A",1,$DataString,0) _XLSave($FilePath1,1) ;================================================ ;START HERE WITH FILES KNOWN ;================================================ ;~ $FilePath1=@ScriptDir&"\The Average.xls";input ;~ $FilePath2=@ScriptDir&"\Blank6.xls"; blank local $XLArray2[1][1] Global $A_Percent = 0, $A_Count = 0 Global $B_Percent = 0, $B_Count = 0 $XLArray1=_XLArrayRead($FilePath1,1,"usedrange") _Array2DTranspose( $XLArray1) ;~ _ArrayView2D1D($XLArray1,"") _XLClose($FilePath1,1) ;========================================= for $i=0 to UBound($XLArray1)-1 if $XLArray1[$i][0] = "A" AND $XLArray1[$i][6] >= "0" Then $A_Percent = $A_Percent + $XLArray1[$i][6] $A_Count = $A_Count + 1 $Array2DTo1String=_Array2DTo1String( $XLArray1, $i, "Array contents",0 , 1) _ArrayInsert2Dst( $XLArray2, $Array2DTo1String) $APercentage = $A_Percent / $A_Count EndIf Next _ArrayInsert2Dst( $XLArray2, $A_Count) _ArrayInsert2Dst( $XLArray2, $A_Percent) _ArrayInsert2Dst( $XLArray2, $APercentage) _XLArrayWrite($XLArray2, $FilePath2,1,1,1,0 ,1,0) _XLShow($FilePath2,1)Best, randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
MaCgyver Posted July 29, 2006 Author Posted July 29, 2006 Hi, *** I think I could get this going if you posted an example xls file with the data you are using.. **** Best, randall PS for some reason you have left out $APercentage = $A_Percent / $A_Count? Randall QUESTIONS: [1.Does your real data have blank lines? 2. Did you get decent output when you used my test script and data anyway?] the data is calculated was two different cells with "0" and the third with something weird. I was getting an error with $APercentage not being declared, so I put it as a global variable and I didn't get the error anymore. I will try again with what you posted up to see if I get better results. Yes some of the fields could be blank and that is why I have to check that field to make sure it = or > "0", so it can be included in the averages. Thanks for you attention in this matter.
MaCgyver Posted July 29, 2006 Author Posted July 29, 2006 (edited) It worked. If you PM me or POST your email you use with paypal I can send you the 10 bux right now. Or if you want it in the mail just PM your address and your name and I will send it first thing Monday. You earned dude. Now I just need to test to see if I am going to need a sort included before it calculates. Thanks again. Edited July 29, 2006 by MaCgyver
Moderators SmOke_N Posted July 29, 2006 Moderators Posted July 29, 2006 It worked. If you PM me or POST your email you use with paypal I can send you the 10 bux right now. Or if you want it in the mail just PM your address and your name and I will send it first thing Monday.You earned dude. Now I just need to test to see if I am going to need a sort included before it calculates. Thanks again.Whoa!! First I've seen someone live up to it. Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.
MaCgyver Posted July 29, 2006 Author Posted July 29, 2006 I have to iron out the script a little. It worked fine on the test file, but on the big file had a save problem. I think I can figure it out. When I tried this using the big file when its a csv file I got an exceeded range error. However when I sorted that same file and ran the script it worked fine. I will see if this is the case this time in this incident. You still earned the 10 bux so I'm just waiting for you to PM me or POST your info.
MaCgyver Posted July 29, 2006 Author Posted July 29, 2006 Whoa!! First I've seen someone live up to it.I am a man of my word.
MaCgyver Posted July 29, 2006 Author Posted July 29, 2006 Well it is working with the main xls file now. What happened was the first sheet was wrong, so I deleted it. The info it was supposed to work off of was in sheet 2 and now that I've made sheet 2 to sheet 1 it worked. Now I have to do some more testing and then finish the script by adding C_Count/C_Percentage through Z_Count/Z_Percentage.
randallc Posted July 29, 2006 Posted July 29, 2006 Hi, Thanks for the donatio to AutoIt donations.. I have put a new func in "Array2d.au3" which will be 5x quicker on large files; _ArrayInsert2DFrom2Dline( $XLArray3, $XLArray1, $i) There is also sort function for Execelcom; see examples at the zip of examples on the link. _XLSort(ByRef $s_FilePath,$Columns=1,$Direction1=1,$Columns2=1,$Direction2=1,$SortRange="UsedRange",$s_i_Sheet=1,$s_i_Visible=0 ,$i_Suppress=0)oÝ÷ Ù«¢+Øí}a1ÉÉå MXµ±¥¹ÔÌÁ|Ì(¥¹±ÕÅÕ½Ðíá± ½´¹ÔÌÅÕ½Ðì(¥¹±ÕÅÕ½ÐíÉÉäɹÔÌÅÕ½Ðì(¥¹±ÕÅÕ½Ðí}ÉÉåY¥ÜÉŹÔÌÅÕ½Ðìì(ÀÌØí¥±AÑ ÈõMÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí ±¹¬Ø¹á±ÌÅÕ½Ðì(ÀÌØí¥±AÑ ÄõMÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí ±¹¬Ô¹á±ÌÅÕ½Ðì(íø¥±±Ñ ÀÌØí¥±AÑ Ä¤(íø¥±±Ñ ÀÌØí¥±AÑ È¤)}a1 ÉÑ ±¹¬ ÀÌØí¥±AÑ Ä¤)}a1 ÉÑ ±¹¬ ÀÌØí¥±AÑ È¤(ÀÌØíÑMÑÉ¥¹ôÅÕ½Ðí°Ü°È°ä°Ḛ̀Ð԰ȰÄܰÄà°äÅÕ½ÐìµÀí I1µÀìÅÕ½Ðḭ̀ÄȰܰذä°Ḛ̀Ð԰̰ÄܰÄàÅÕ½ÐìµÀí I1µÀìÅÕ½Ðí°ÄÈ°à°Ø°ä°Ḛ̀Ð԰̰ÄܰÄàÅÕ½ÐìµÀí I1µÀìÅÕ½Ðḭ̀ÄÈ°ä°Ø°ä°Ḛ̀Ð԰̰ÄܰÄàÅÕ½Ðì(ÀÌØíÑMÑÉ¥¹õMÑÉ¥¹IÁ± ÀÌØíÑMÑÉ¥¹°ÅÕ½Ðì°ÅÕ½Ðì±Q¤)½ÈÀÌØíôÄѼà(ÀÌØíÑMÑÉ¥¹µÀìõ I1µÀìÀÌØíÑMÑÉ¥¹)¹áÐ(ÀÌØía1I¹õ}a1ÁÍÑ ÀÌØí¥±AÑ Ä°Ä°ÅÕ½ÐíÅÕ½Ðì°Ä°ÀÌØíÑMÑÉ¥¹°À¤)}a1MÙ ÀÌØí¥±AÑ Ä°Ä¤(ìôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôô(íMQIP!I]%Q %1L-9=]8(ìôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôô(íøÀÌØí¥±AÑ ÄõMÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈíQ¡Ùɹá±ÌÅÕ½Ðìí¥¹ÁÕÐ(íøÀÌØí¥±AÑ ÈõMÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí ±¹¬Ø¹á±ÌÅÕ½Ðìì±¹¬)±½°ÀÌØía1ÉÉäÉlÅulÅt°ÀÌØía1ÉÉäÍlÅulÅt°ÀÌØíAɹѰÀÌØí}AɹаÀÌØí} ½Õ¹Ð)±½°ÀÌØí}AɹÐôÀ°ÀÌØí} ½Õ¹ÐôÀ)±½°ÀÌØí }AɹÐôÀ°ÀÌØí } ½Õ¹ÐôÀ(ÀÌØía1ÉÉäÄõ}a1ÉÉåI ÀÌØí¥±AÑ Ä°Ä°ÅÕ½ÐíÕÍɹÅÕ½Ðì¤)}ÉÉäÉQɹÍÁ½Í ÀÌØía1ÉÉäĤ(íø}ÉÉåY¥ÜÉÅ ÀÌØía1ÉÉäİÅÕ½ÐìÅÕ½Ðì¤)}a1 ±½Í ÀÌØí¥±AÑ Ä°Ä¤(ìôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôô(ÀÌØíÑ¥µÉÍѵÀÌõQ¥µÉ%¹¥Ð ¤)½ÈÀÌØí¤ôÀѼU ½Õ¹ ÀÌØía1ÉÉäĤ´Ä(%¥ÀÌØía1ÉÉäÅlÀÌØí¥ulÁtôÅÕ½ÐíÅÕ½Ðì9ÀÌØía1ÉÉäÅlÀÌØí¥ulÙtÐìôÅÕ½ÐìÀÅÕ½ÐìQ¡¸($$ÀÌØí}AɹÐôÀÌØí}AɹЬÀÌØía1ÉÉäÅlÀÌØí¥ulÙt($$ÀÌØí} ½Õ¹ÐôÀÌØí} ½Õ¹Ð¬Ä($%}ÉÉå%¹ÍÉÐÉɽ´É±¥¹ ÀÌØía1ÉÉä̰ÀÌØía1ÉÉäİÀÌØí¤¤($$ÀÌØíAɹÑôÀÌØí}AɹмÀÌØí} ½Õ¹Ð(%¹%)9áÐ) ½¹Í½±]É¥Ñ ÅÕ½ÐíÉÑèÅÕ½ÐìµÀíɽչ¡Q¥µÉ¥ ÀÌØíÑ¥µÉÍѵÀ̤¤µÀìÅÕ½ÐìµÍ½¹ÌÅÕ½ÐìµÀí±¤)}ÉÉå%¹ÍÉÐÉÍÐ ÀÌØía1ÉÉä̰ÀÌØí} ½Õ¹Ð¤)}ÉÉå%¹ÍÉÐÉÍÐ ÀÌØía1ÉÉä̰ÀÌØí}AɹФ)}ÉÉå%¹ÍÉÐÉÍÐ ÀÌØía1ÉÉä̰ÀÌØíAɹѤ)}a1ÉÉå]É¥Ñ ÀÌØía1ÉÉä̰ÀÌØí¥±AÑ È°Ä°Ä°Ä°À°Ä°À¤)}a1M¡½Ü ÀÌØí¥±AÑ È°Ä¤(Best, Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
randallc Posted July 29, 2006 Posted July 29, 2006 (edited) Hi, Here it is with sort; but why would you need that? PS - EDIT - changed this post to next ; ver 0_9 Edited July 29, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
randallc Posted July 29, 2006 Posted July 29, 2006 Hi, Changed too often; expandcollapse popup;_XLArrayCSV-like.au3 0_9 #include"ExcelCom.au3" #include "Array2D.au3" $xlAscending = 1 $xlDescending = 2 $FilePath2=@ScriptDir&"\Blank6.xls" $FilePath1=@ScriptDir&"\Blank5.xls" _XLCreateBlank($FilePath1) _XLCreateBlank($FilePath2) $DataString="A,7,2,9,23,45,2,17,18,9"&@CRLF&"B,12,7,6,9,23,45,3,17,18"&@CRLF&"A,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18" $DataString=StringReplace($DataString,",",@TAB) for $a=1 to 8 $DataString&=@CRLF&$DataString next $XLRange=_XLpaste($FilePath1,1,"A",1,$DataString,0) _XLSave($FilePath1,1) ;====================================== $timerstampTOTAL=TimerInit() local $XLArray2[1][1],$XLArray3[1][1],$APercentage,$A_Percent,$A_Count,$B_Percent , $B_Count _XLsort($FilePath1,"A1",$xlDescending,"G1",$xlDescending,"usedrange",1) $XLArray1=_XLArrayRead($FilePath1,1,"usedrange") _Array2DTranspose( $XLArray1) for $i=0 to UBound($XLArray1)-1 if $XLArray1[$i][0] == "A" AND $XLArray1[$i][6] >= "0" Then $A_Percent += $XLArray1[$i][6] _ArrayInsert2DFrom2Dline( $XLArray3, $XLArray1, $i) EndIf Next _ArrayInsert2Dst( $XLArray3, UBound($XLArray3)) _ArrayInsert2Dst( $XLArray3, $A_Percent) _ArrayInsert2Dst( $XLArray3, $A_Percent / UBound($XLArray3)) _XLArrayWrite($XLArray3, $FilePath2,1,1,1,0 ,1,0) $XLLastRow=UBound($XLArray3)+1 ;~ $XLLastRow=_XLLastRow( $FilePath2,1) ;====================================== redim $XLArray3[1][1] for $i=0 to UBound($XLArray1)-1 if $XLArray1[$i][0] == "B" AND $XLArray1[$i][6] >= "0" Then $B_Percent += $XLArray1[$i][6] _ArrayInsert2DFrom2Dline( $XLArray3, $XLArray1, $i) EndIf Next _ArrayInsert2Dst( $XLArray3, UBound($XLArray3)) _ArrayInsert2Dst( $XLArray3, $B_Percent) _ArrayInsert2Dst( $XLArray3, $B_Percent / UBound($XLArray3)) _XLArrayWrite($XLArray3, $FilePath2,1,1,$XLLastRow,0 ,1,0) ConsoleWrite("$timerstampTOTAL :"&round(TimerDiff($timerstampTOTAL)) & " mseconds "&@lf) _XLClose($FilePath1,1) _XLShow($FilePath2,1) ;~ _XLexit($FilePath2)Best, randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now