|
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
|
|
|
|
|
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
|
|
|
|
|
i want to get a figure from a spreadsheet
|
|
|
|
|
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
|
|
|
|
|
i want to run a script outside the sheet using which the contents of the sheet get updated
|
|
|
|
|
OK, This gets data from a closed book and puts it into datagrid view
Imports System.Data<br />
Imports System.Data.OleDb<br />
Imports ADODB<br />
Imports System.IO<br />
<br />
<br />
Public Class Form1<br />
<br />
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click<br />
'I made this to collect all data from a sheet, you can modify as per need<br />
'by declaring a range like [Sheet1$A1:B20"]<br />
Dim sqlData As String = "Select Data * From [Sheet1$]"<br />
<br />
'To put data in a table with fieldnames then HDR should Yes and if not then No.<br />
Dim xlCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br />
"Data Source=c:\Source.xls;" & _<br />
"Extended Properties=""Excel 8.0;HDR=YES"""<br />
<br />
Dim conn As New OleDbConnection(xlCon)<br />
Dim da As New OleDbDataAdapter(sqlData, conn)<br />
Dim dsXLData As New DataSet<br />
<br />
da.Fill(dsXLData, "Source")<br />
Me.DataGridView1.DataSource = dsXLData.Tables("Source")<br />
<br />
dsXLData.Dispose()<br />
da.Dispose()<br />
conn.Dispose()<br />
<br />
End Sub<br />
End Class<br />
------------------------------------
"When Belly Full, Chin Hit Chest"
Confucius 502BC
|
|
|
|
|
this doesn't seem to be a .vbs syntax...
|
|
|
|
|
My Mistake, Try this...
<br />
Imports System.Data<br />
Imports System.Data.OleDb<br />
Imports System.IO<br />
<br />
Public Class Form1<br />
<br />
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click<br />
'I made this to collect all data from a sheet, you can modify as per need<br />
'by declaring a range like [Sheet1$A1:B20"]<br />
Dim sqlData As String = "SELECT * FROM [Sheet1$]"<br />
<br />
'To put data in a table with fieldnames then HDR should Yes and if not then No.<br />
Dim xlCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _<br />
& "Data Source= PATH\SHEETNAME.XLS
" _<br />
& "Extended Properties=""Excel 8.0;HDR=NO"""<br />
<br />
Dim conn As New OleDbConnection(xlCon)<br />
Dim da As New OleDbDataAdapter(sqlData, conn)<br />
Dim dsXLData As New DataSet<br />
da.Fill(dsXLData, "Source")<br />
Me.DataGridView1.DataSource = dsXLData.Tables("Source")<br />
<br />
dsXLData.Dispose()<br />
da.Dispose()<br />
conn.Dispose()<br />
<br />
End Sub<br />
End Class<br />
------------------------------------
"When Belly Full, Chin Hit Chest"
Confucius 502BC
|
|
|
|
|
|
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
|
|
|
|
|
how to get VB form data in to a text file in a http server
here is my code but its not working
Dim FStream1 As IO.FileStream
Dim oIE As Object
oIE = CreateObject("internetexplorer.application")
oIE.Navigate("http://116.75.111.202/") = New IO.FileStream("c:\Documents\details", IO.FileMode.Append, IO.FileAccess.Write)
Dim SWriter1 As IO.StreamWriter = New IO.StreamWriter(FStream1)
SWriter1.Write(Text & vbNewLine)
SWriter1.Write(Label1.Text & vbTab)
SWriter1.Write(TextBox1.Text & vbNewLine)
SWriter1.Write(Label2.Text & vbTab)
SWriter1.Write(TextBox2.Text & vbNewLine)
SWriter1.Write(Label3.Text & vbTab)
SWriter1.Write(TextBox3.Text & vbNewLine)
SWriter1.Write(Label4.Text & vbTab)
SWriter1.Write(TextBox4.Text & vbNewLine)
SWriter1.Close()
FStream1.Close()
could anyone help me out with a correct code
|
|
|
|
|
Based on your subject line, I think you need to read the article linked to in my sig. It will solve your problems.
Christian Graus
Driven to the arms of OSX by Vista.
Please read this[ ^] if you don't like the answer I gave to your question.
|
|
|
|
|
Dear me. Please tell me that's just a proof of concept. There are so many things wrong with it. You assign a value to a method invocation. You use what appears to be an uninitialised variable. You take input from the user and send it with no verification, leaving you open to injection attacks. And your methodology is pretty much completely wrong. You've even posted a real IP address (which appears to need credentials to be accessed; I hope you provide them)
Your basic idea is to create a text file from your atrociously named controls, and upload it to a server somewhere in the Great Aether. So drop that code snippet and look into the WebClient class
What you need to do is to write a file to a byte array (no need to save it, the UploadData method accepts a byte array and saving just creates a new bottleneck) and call the UploadData method. So create a MemoryStream and StreamWriter. The StreamWriter should be constructed using the MemoryStream. Now, write the data to the StreamWriter in the normal way.
Next, create the WebClient instance, and declare a byte array. The byte array should be equal to the result from the invocation of the MemoryStream's ToArray method. You now have the 'file' as an array of bytes. Then, simply invoke the UploadData of the WebClient instance, passing the web address and the byte array you declared earlier. This will block execution until the upload finishes; if you fancy a challenge, dig into the documentation - there's a method which performs the same operation asynchronously and raises an event when it's done. The return value of the UploadData method is simply the response - it's useful for debugging to see where something went wrong
If, as I suspect, your HTTP server needs credentials, then set the Credentials property of the WebClient instance you declared accordingly
Between the idea
And the reality
Between the motion
And the act
Falls the Shadow
|
|
|
|
|
harieshkumar.n wrote: oIE.Navigate("http://116.75.111.202/") = New IO.FileStream("c:\Documents\details", IO.FileMode.Append, IO.FileAccess.Write)
What on earth is this??
Considering the FileStream class will not work with the HTTP protocol, you're concept won't work at all. Also, your web directory is probably going to be read only to every client, if this code is running on the client side, it wont be able to write to a file on the web server. Unless you do something very stupid and relax that security restriction.
Is this code in an ASP page?? Is it in some component running on the server?? What kind of app is it??
Remember, the quality of the answer you get is directly dictated by the quality of the question.
And don't use "urgent" anywhere in your post. It only makes people want to ignore you and you're not going to get an answer any faster.
|
|
|
|
|
Dear All,
I have some problem in loading an image in crystal report. I am using VS 2005 with CR 9.
I have a table adapter in which i used an unbound column "Image" of type System.Byte.
And I used the following code to set image.
....
Dim s As String = dt.Rows(0).Item("ImagePath").ToString
Dim fs As FileStream = New FileStream(s, FileMode.Open, FileAccess.Read)
Dim image(fs.Length) As Byte
fs.Read(image, 0, Convert.ToInt32(fs.Length))
dt.Rows(0).Item("Image") = image ................................(1)
fs.Close()
....
But it throws an exception at (1) [Unable to cast type of System.IConvertible to type System.Byte]
I used to debug the code, and when i reached at (1), i found the datatype of dt.Rows(0).item("Image") = System.DBNull
Although it is set to type System.Byte at design time in Table Adapter.
Please help me out
Regards,
Ovais
|
|
|
|
|
I haven't used a Table Adapter like you are talking about, but one thought that came to me when reading this is that a System.Byte is not the same data type as your image variable. You need a Byte array. This code: Dim image(fs.Length) As Byte declares a Byte array, not a Byte.
Hope this helps.
|
|
|
|
|
I am adding values to a Access table (with around 25 values). A date conversion error occurs and I do not know in which line it occurs. It is practically very tedious to go line by line. How do I track this. Someone please help. Thank you in advance.
|
|
|
|
|
If your running thing under the debugger, it'll stop on the exact line the problem occurs.
Also, if you're using string concatenation to "build" the SQL statement, it's very easy to get an error like your describing:
sql = "SELECT something FROM table WHERE field1 = #" & datevalue & "#"
Use parameterized queries instead to have the code automatically convert the date to the appropriate format for the database engine.
|
|
|
|
|
Thank you Dave. I was using parameterized query and it was driving me mad. As you have instructed
i used straight query like this
sql = "SELECT something FROM table WHERE field1 = #" &amp; datevalue &amp; "#"
and solved the problem within no time.
Thanks also to Rajdeep and Christian for your replies.
|
|
|
|
|
Uhhh...no. I said DO NOT do that. Use the parameterized query instead.
|
|
|
|
|
Ideally this should not happen. Are you not doing any sort of validations?
|
|
|
|
|
If you don't want to step through the debugger, do something like this:
Dim sb as StringBuilder = new StringBuilder()
// first line of insert
sb.AppendLine("Inserted one")
// second insert
sb.AppendLine("Inserted two")
etc
Put this in a try/catch and put a breakpoint in the catch. Then, you can look at your string builder to see which line blew up. Then you can focus on that one line ( and repeat, I would not be surprised if you fix one line and then find another has the same issue, if the code does the same thing, you're likely to have repeated your bug more than once.
Christian Graus
Driven to the arms of OSX by Vista.
Please read this[ ^] if you don't like the answer I gave to your question.
|
|
|
|
|
I am using VB 2005 and have connected to an Access database.
Rather than do the usual for i = 0 to total, can you populate an array with the dataset some how.
|
|
|
|
|
Do you want an array filled with the tables the DataSet contains? If so, and if you have included the System.Linq namespace, then you can use ds.Tables.ToArray(), where ds is your DataSet. The result of that method call will be the Tables property as an array
|
|
|
|
|
Or if you want to enter the values of a certain column from the dataset into the array you can use the For Each loop:
For Each rw As DataRow In YourDataSet.Table("WhatEverTable").Rows
Or considering that a dataset, in a sense, is a sophisticated array, you could consider forgetting about copying values to an array first, and just get them straight from the dataset.
My advice is free, and you may get what you paid for.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
