How to get in a vbs script the sum of values of a Excel cells in a certain range?

  • Thread starter Thread starter Claudia d'Amato
  • Start date Start date
C

Claudia d'Amato

Assume I want to get the sum of values of all cells from the range "H6:H18" from an Excel Worksheet.

How can I do this within vbs script?

Claudia
 
This works, but I'm sure someone will offer a more elegant solution:

Sub Test()
Dim Tot As Double
Dim K As Byte

For K = 6 To 18
Tot = Tot + Range("h" & K).Value
Next K

MsgBox Tot
End Sub
 
This will definitely work for you.

Sub SumRange()

Dim myRange As Range
Dim cell As Range
Dim rngtotal As Double

Set myRange = Sheets("Sheet1").Range("H6:H18")

For Each cell In myRange
rngtotal = rngtotal + cell
Next cell

MsgBox "The Sum of Range(''H6:H18'') = " & rngtotal

End Sub

Hope it helps!
 
Assume I want to get the sum of values of all cells from the range "H6:H18" from an Excel Worksheet.

How can I do this within vbs script?

Claudia
Click to expand...

Maybe something like this ...

' Assumes workbook is in folder with this script.
' Adjust to suit
sPath = replace(wsh.scriptfullname, wsh.scriptname, "")

sXLWB = sPath & "testing.xls"

set oXL = CreateObject("Excel.Application")
with oXL.Workbooks.Open(sXLWB).Sheets(1)
SumRange = oXL.WorksheetFunction.Sum(.Range("H8:H16"))
end with
wsh.echo "Sum:",SumRange

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/
 
Assume I want to get the sum of values of all cells from the range "H6:H18" from an Excel Worksheet.

How can I do this within vbs script?

Claudia
Click to expand...

Maybe something like this ...

'Assumes workbook in folder with script - adjust as needed
sPath = replace(wsh.scriptfullname, wsh.scriptname, "")

sXLWB = sPath & "testing.xls"
set oXL = CreateObject("Excel.Application")
with oXL.workbooks.open(sXLWB)
SumRange = oXL.WorksheetFunction.Sum(.sheets(1).Range("A1:A10"))
end with
wsh.echo "Sum:",SumRange

I thought I posted this earlier, but google sees to have lost it.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/
 
Il giorno 26 Jun 2008 17:36:21 GMT, (e-mail address removed) (Claudia d'Amato) ha scritto:
Assume I want to get the sum of values of all cells from the range "H6:H18" from an Excel Worksheet.
How can I do this within vbs script?
Click to expand...

Not tested...

Set xl=CreateObject("excel.application")
xl.Visible=True

xl.Workbooks.Open Filename
somma=0

for riga=6 to 18
somma=somma + xl.cells(riga,8)
next

msgbox somma
 
Back
Top