Jump to content
Sign in to follow this  
ajit

Total from columns (Excel sheet)

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 by Das Ami

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by Das Ami

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×