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

[MaCgyver]

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]

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]

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]

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

[MaCgyver]

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]

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]

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]

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]

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]

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]

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

[MaCgyver]

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]

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]

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]

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]

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

I am a man of my word.

[MaCgyver]

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]

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]

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]

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