Total from columns (Excel sheet)

[ajit]

Hi

I have made a script to read lines from Excel sheet and get the total of the colums through a message box.

The script becomes too long as i read more lines.

Please guide me on how to make it short.

Thanking in anticipation

Ajit

;;Script:

$file = FileOpen("1.xls", 0)

If $file = -1 Then

MsgBox(0, "Error", "Unable to open file.")

Exit

EndIf

$val1 = FileReadLine($file, 1)

$val2 = FileReadLine($file, 2)

$val3 = FileReadLine($file, 3)

$val4 = FileReadLine($file, 4)

$val5 = FileReadLine($file, 5)

$val6 = FileReadLine($file, 6)

$val7 = FileReadLine($file, 7)

$val8 = FileReadLine($file, 8)

$val9 = FileReadLine($file, 9)

$val10 = FileReadLine($file, 10)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

$valn = FileReadLine($file, "line N")

$total = $val1 + $val2 + $val3 + $val4 + $val5 + $val6 + $val7 + $val8 + $val9 + $val10

MsgBox(1, "Total Value", $total)

;; Thanks

[Das Ami]

Hello there, I suggest you take a look at loops and arrays.

I didn't test this code but it should work.

#Include <Array.au3>
$file = FileOpen("1.xls", 0)
$n=1
$total=0
Dim $val

If $file = -1 Then
MsgBox(0, "Error", "Unable to open file.")
Exit
EndIf

Do
    _ArrayAdd($val,FileReadLine($file, $n))
    $n+=1
    Until @error= -1

For $x=0 to UBound($val)-1
    $total+=$val[x]
Next


MsgBox(1, "Total Value", $total)

Edited January 14, 2009 by Das Ami

[ajit]

Hello there, I suggest you take a look at loops and arrays.

I didn't test this code but it should work.

#Include <Array.au3>
$file = FileOpen("1.xls", 0)
$n=1
$total=0
Dim $val

If $file = -1 Then
MsgBox(0, "Error", "Unable to open file.")
Exit
EndIf

Do
    _ArrayAdd($val,FileReadLine($file, $n))
    $n+=1
    Until @error= -1

For $x=0 to UBound($val)-1
    $total+=$val[x]
Next


MsgBox(1, "Total Value", $total)

@Das Ami

Thanks very much for your reply. I tried your script and it gave no answer.

It showed an error $total+=$val[x]

Thanks again

Ajit

[Das Ami]

It showed an error $total+=$val[x]

That's probably because the line you read from the excel file doesn't contain numbers but most likely "useless" data (among them characters which produce the error).

I am almost certain that excel saves it's data in some kind of XML format inside your xls file. This means that FileReadLine won't work.

Take a look at the Excel functions in the helpfile, especially _ExcelReadCell().

Edited January 14, 2009 by Das Ami