473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VB.Net MS Excel Question

I have a question...I am attempting to write the values of multiple textboxes to an MS Excel spreadsheet. I would like to be able to write the values to let say a1,b1,c1,d1...ect and then click on a button to write new values to the next row a2,b2,c2,d1...ect...This is what I came up with but it does not do what I need it to do. here is what I am missing.

a.. I would rather write values to a prexisting MS Excell sheet ex "C:/temp/textexcell.xls"
b.. when I click on the next button it would use either the code I have or something to prepare the user to write values to the next row.
Thanks in advance for your help!!!
Public Class Form1

Inherits System.Windows.Forms.Form

Dim alpha As Char

Dim beta As Char

Dim charlie As Char

Dim num As Integer

Dim a As String

Dim b As String

Dim c As String

Dim exsell As New Excel.Application

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

num = 1

alpha = "a"

beta = "b"

Charlie ="c"

a = Convert.ToString(alpha & num)

b = Convert.ToString(beta & num)

c = Convert.ToString(Charlie & num)

Dim exsell As New Excel.Application
exsell.Visible = True

exsell.Workbooks.Add()

exsell.Range(a).Select()

exsell.ActiveCell.FormulaR1C1 = TextBox1.Text

exsell.Range(b).Select()

exsell.ActiveCell.FormulaR1C1 = TextBox2.Text

textbox1.text =""

textbox2.text =""

textbox3.text =""



End Sub

'Private Sub btnNextRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

' num = num + 1

' alpha = "a"

' beta = "b"

' Charlie ="c"

' a = Convert.ToString(alpha & num)

' b = Convert.ToString(beta & num)

' c = Convert.ToString(charlie & num)

exsell.Range(a).Select()

exsell.ActiveCell.FormulaR1C1 = TextBox1.Text

exsell.Range(b).Select()

exsell.ActiveCell.FormulaR1C1 = TextBox2.Text

' exsell.Range(c).Select()

' exsell.ActiveCell.FormulaR1C1 = TextBox3.Text

'End Sub
--
SHANE CLARK

Dec 14 '05 #1
1 3082
First, you'll be spamming your users with Excel apps everytime they click
button1 :). Fix that first. Next change exsell.Workbooks.Add() to
exsell.Workbooks.Open("C:/temp/textexcell.xls") Without testing it, I'd say
that calling it again when it's open should just pop it to the front, but
check that to be sure.

Do you have a definate number of colums? because plotting by letter & number
is really only useful for very few cells, after that you might want to locate
cells by addressing Cells(row,column) This should act just like a range
object and allow you to select individual cells. You can either keep track
of the rows in particular to a single user, or search down for the next empty
row and keeping a RowIndex in the scope of your class. Everytime a row is
filled: RowIndex =+1 and call your columns.
cells(RowIndex,1),cells(RowIndex,2) for each colum you want. I'm guessing to
the number of text boxes you have.

Randy

"SHANE CLARK" wrote:
I have a question...I am attempting to write the values of multiple textboxes to an MS Excel spreadsheet. I would like to be able to write the values to let say a1,b1,c1,d1...ect and then click on a button to write new values to the next row a2,b2,c2,d1...ect...This is what I came up with but it does not do what I need it to do. here is what I am missing.

a.. I would rather write values to a prexisting MS Excell sheet ex "C:/temp/textexcell.xls"
b.. when I click on the next button it would use either the code I have or something to prepare the user to write values to the next row.
Thanks in advance for your help!!!
Public Class Form1

Inherits System.Windows.Forms.Form

Dim alpha As Char

Dim beta As Char

Dim charlie As Char

Dim num As Integer

Dim a As String

Dim b As String

Dim c As String

Dim exsell As New Excel.Application

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

num = 1

alpha = "a"

beta = "b"

Charlie ="c"

a = Convert.ToString(alpha & num)

b = Convert.ToString(beta & num)

c = Convert.ToString(Charlie & num)

Dim exsell As New Excel.Application
exsell.Visible = True

exsell.Workbooks.Add()

exsell.Range(a).Select()

exsell.ActiveCell.FormulaR1C1 = TextBox1.Text

exsell.Range(b).Select()

exsell.ActiveCell.FormulaR1C1 = TextBox2.Text

textbox1.text =""

textbox2.text =""

textbox3.text =""



End Sub

'Private Sub btnNextRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

' num = num + 1

' alpha = "a"

' beta = "b"

' Charlie ="c"

' a = Convert.ToString(alpha & num)

' b = Convert.ToString(beta & num)

' c = Convert.ToString(charlie & num)

exsell.Range(a).Select()

exsell.ActiveCell.FormulaR1C1 = TextBox1.Text

exsell.Range(b).Select()

exsell.ActiveCell.FormulaR1C1 = TextBox2.Text

' exsell.Range(c).Select()

' exsell.ActiveCell.FormulaR1C1 = TextBox3.Text

'End Sub
--
SHANE CLARK

Dec 14 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: jimserac | last post by:
I had previously posted this in an Access forum with negative results so will try here. Although this question specifies an Access database, I also wish to accomplish this with a large MS SQL...
6
by: Sam Johnson | last post by:
HI I tried to send the following SQL string to an open databse, to export a table into excel format: g.Connection = conn 'valid OleDBConnection and Command objects g.CommandText = "SELECT *...
3
by: George | last post by:
Sub ExcelToListBox() Dim xRange As Object Dim ary Dim xValue As String xRange = oXL.Range("A1:A9") 'has letters A-H ary = xRange.value xValue = ary(3, 1) 'xValue = C...
19
by: wreckingcru | last post by:
I'm trying to output a SQL query that is constructed thru my VB.net GUI into an excel file. Here is the code I'm using: 'Sqlstmt is the SQL query statement 'Conn is the SQL Connection object...
2
by: Mad Scientist Jr | last post by:
>From an asp.net web page I want the user to open the results of a SQL query in Excel, as automatically as possible (ie not having to loop through columns, rows, in code). For this,...
6
by: Gunawan | last post by:
Dear All, I have create an excel (COM Object) using this code Excel.Application xls = new Excel.Application(); but I can not remove it from memory although I have using close and quit ...
9
by: Doug Glancy | last post by:
I got the following code from Francesco Balena's site, for disposing of Com objects: Sub SetNothing(Of T)(ByRef obj As T) ' Dispose of the object if possible If obj IsNot Nothing AndAlso...
7
by: ddecoste | last post by:
I have a need to add a visual representation to some data in Access. I need to draw a matix of squares inside another square. I have all the data that I need in a record in Access. The data...
3
by: pleaseexplaintome_2 | last post by:
using the code below (some parts not included), I create a new excel workbook with spreadheets. I then want to delete a spreadsheet, but a reference remains open and excel stays in task manager...
3
by: akristensen | last post by:
I am new to this site, so be patient if I do not ask the question correctly. Current Target Platform: Browser: MS IE, script language: Javascript (will use VBScript, but JS is preferred), External...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.