Script built for CSV file. How can I build for EXCEL?

MaCgyver · July 27, 2006

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 · 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

MaCgyver · 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 · July 28, 2006

Hi,

something like this?

#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

MaCgyver · July 28, 2006

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 · 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, Randall

Edited July 28, 2006 by randallc

MaCgyver · 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, Randall

Looks like I don't have this file "_ArrayView2D1D.au3". Can anyone point me to the link? I did a search with no results. :">

MaCgyver · 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.#IND

The 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 · 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 · July 28, 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?]

Edited July 28, 2006 by randallc

randallc · July 28, 2006

Hi,

Sorry; my script was bugged; The Array transpose as Excel arrays around the other way.

#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

MaCgyver · 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 · July 29, 2006

:whistle:

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

SmOke_N · 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.

MaCgyver · 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 · July 29, 2006

Whoa!! First I've seen someone live up to it.

I am a man of my word.

MaCgyver · 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 · 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

randallc · July 29, 2006

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

randallc · July 29, 2006

Hi,

Changed too often;

;_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

Sign In

Script built for CSV file. How can I build for EXCEL?

Recommended Posts

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Create an account or sign in to comment

Create an account

Sign in