sum up rows in excel

[priy]

Hi All, I am new to .net can some one help me in writing a vb script to add the values in rows in an excel sheet. say i have 5 rows with positive values.i want the result of addition of these 5 rows in the sixth row. Thanks in advance

[Dalek Dave]

Just to be sure here, IN VB.Net you want to get a figure from a spreadsheet? Or... Are you doing this with VBA within Excel? It is useful to know, either way the answer is fairly easy, but I would need to know which so as not to waste your time and mine. :)

------------------------------------ "When Belly Full, Chin Hit Chest" Confucius 502BC

[priy]

i want to get a figure from a spreadsheet

[Dalek Dave]

I know, but are you using VB.Net OUTSIDE of a sheet and just want to pick up the figure, Or are you using VBA Within the Spreadsheet?

------------------------------------ "When Belly Full, Chin Hit Chest" Confucius 502BC

[priy]

i want to run a script outside the sheet using which the contents of the sheet get updated

[Dalek Dave]

Gotcha! OK, This gets data from a closed book and puts it into datagrid view Imports System.Data Imports System.Data.OleDb Imports ADODB Imports System.IO Public Class Form1       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click             'I made this to collect all data from a sheet, you can modify as per need             'by declaring a range like [Sheet1$A1:B20"]             Dim sqlData As String = "Select Data * From [Sheet1$]"             'To put data in a table with fieldnames then HDR should Yes and if not then No.             Dim xlCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                                              "Data Source=c:\Source.xls;" & _                                              "Extended Properties=""Excel 8.0;HDR=YES"""             Dim conn As New OleDbConnection(xlCon)             Dim da As New OleDbDataAdapter(sqlData, conn)             Dim dsXLData As New DataSet             da.Fill(dsXLData, "Source")             Me.DataGridView1.DataSource = dsXLData.Tables("Source")             dsXLData.Dispose()             da.Dispose()             conn.Dispose()       End Sub End Class

------------------------------------ "When Belly Full, Chin Hit Chest" Confucius 502BC

[priy]

this doesn't seem to be a .vbs syntax...

[Dalek Dave]

My Mistake, Try this... Imports System.Data Imports System.Data.OleDb Imports System.IO Public Class Form1       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click             'I made this to collect all data from a sheet, you can modify as per need             'by declaring a range like [Sheet1$A1:B20"]             Dim sqlData As String = "SELECT * FROM [Sheet1$]"             'To put data in a table with fieldnames then HDR should Yes and if not then No.             Dim xlCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _                                     & "Data Source= PATH\SHEETNAME.XLS" _                                     & "Extended Properties=""Excel 8.0;HDR=NO"""             Dim conn As New OleDbConnection(xlCon)             Dim da As New OleDbDataAdapter(sqlData, conn)             Dim dsXLData As New DataSet             da.Fill(dsXLData, "Source")             Me.DataGridView1.DataSource = dsXLData.Tables("Source")             dsXLData.Dispose()             da.Dispose()             conn.Dispose()       End Sub End Class

------------------------------------ "When Belly Full, Chin Hit Chest" Confucius 502BC

[priy]

Thanks Dave

[Dalek Dave]

No Worries, you can alter the range as necessary and it reports into a datagrid view, but obviously you can report it to other things too. Sorry about the first code, don't know what happened there. Maybe my brain was running a little slow! :)

------------------------------------ "When Belly Full, Chin Hit Chest" Confucius 502BC