Question #3: These 200 Textboxes / labels text or values to be populated into Excel sheet as headers.
ACE-- 0 Light Poster
ACE-- 0 Light Poster
By the below is the code I am using currently. But Every Time I can't open VS and do the programming right. I / we need to find a fix for this.
Ok. Like I said I am using Panel for creating this text boxes at runtime.
Code:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Panel1.Controls.Remove(l)
Panel1.Controls.Remove(t)
Dim R As Integer
Dim t As TextBox
Dim l As Label
R = TB.Text
For Row As Integer = 1 To R
For Col As Integer = 1 To 1
t = New TextBox
t.Size = New Drawing.Size(240, 20)
t.Location = New Point(220 * Col, 30 * Row)
t.Name = "Textbox" & Row
t.Text = "Textbox" & Row
Panel1.Controls.Add(t)
l = New Label
l.Size = New Drawing.Size(240, 20)
l.Location = New Point(20 * Col, 30 * Row)
l.Font = New Font(l.Font, FontStyle.Bold)
l.Name = "Label" & Row
l.Text = "LB" & Row
Panel1.Controls.Add(l)
Next
Next
End Sub
Ancient Dragon 5,243 Achieved Level 70 Team Colleague Featured Poster
:Question #3: These 200 Textboxes / labels text or values to be populated into Excel sheet as headers.
As I mentioned a week ago all you have to do is write the data in the text boxes to a comma deliminated text file, then Excel can easily import the file into a worksheet.
But Every Time I can't open VS and do the programming right.
Sorry, but I don't understand that -- I thought you said you had all that fixed, in this post.
Edited by Ancient Dragon
ACE-- 0 Light Poster
Can you share the code for that please.
For Example I have created 200 Text Boxes at runtime I want these text boxes content to populated in excel sheet. How I can do that.
As I said time to time # of boxes will be changing. Minimum 1 Max 256.
Can you help me with that.
Minimalist 96 Posting Pro
I am using vb express 13 and office 7.
I have a form and one button to open Excel and populate some cells.
The following code I have modified some and shortenend, but it works fine.
Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim oXL As Application
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
' Start Excel and get Application object.
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
xlApp.Visible = True
' Get a new workbook.
oWB = xlApp.Workbooks.Add
oSheet = oWB.ActiveSheet
' Add table headers going cell by cell.
oSheet.Cells(1, 1).Value = "First Name"
oSheet.Cells(1, 2).Value = "Last Name"
oSheet.Cells(1, 3).Value = "Full Name"
oSheet.Cells(1, 4).Value = "Salary"
' Format A1:D1 as bold, vertical alignment = center.
With oSheet.Range("A1", "D1")
.Font.Bold = True
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
' Create an array to set multiple values at once.
Dim saNames(5, 2) As String
saNames(0, 0) = "John"
saNames(0, 1) = "Smith"
saNames(1, 0) = "Tom"
saNames(1, 1) = "Brown"
saNames(2, 0) = "Sue"
saNames(2, 1) = "Thomas"
saNames(3, 0) = "Jane"
saNames(3, 1) = "Jones"
saNames(4, 0) = "Adam"
saNames(4, 1) = "Johnson"
' Fill A2:B6 with an array of values (First and Last Names).
oSheet.Range("A2", "B6").Value = saNames
' Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.Range("C2", "C6")
oRng.Formula = "=A2 & "" "" & B2"
' Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.Range("D2", "D6")
oRng.Formula = "=RAND()*100000"
oRng.NumberFormat = "$0.00"
' AutoFit columns A:D.
oRng = oSheet.Range("A1", "D1")
oRng.EntireColumn.AutoFit()
Exit Sub
End Class
The links I have used are:
http://support.microsoft.com/kb/301982/de
http://social.msdn.microsoft.com/Forums/en-US/cab76ead-2166-4394-8a4b-5b1d0cf5c826/open-and-write-to-an-excel-file-from-vbnet-form
http://social.msdn.microsoft.com/Forums/en-US/9a70f14f-2c95-43d2-905f-e09a8550c6e8/excelstart-with-vbnet-with-all-addins
ACE-- 0 Light Poster
@ Minimalist. I can understand you code but my requirement is completely different from what you have suggested.
@ Ancient Dragon: As you stated I using this writing this data different formats. But first I need to get this data into Excel.
Now Coming to my questions.
Question #1: If I enter 200 value in Form3 Texbox.1 And button1 click and then next I want to create 200 Textboxes in the form3. I already the code but it is not working. It is creating the text boxes but box to box distance is enlarging. How can I maintain same distance to each textbox.
Question #2: Whatever the input I am giving in Textbox.1 same number of lables should be created in form1. How can I do it.
Fixed by my self: I have created this textboxes and labels inside a panel. Now there no problem for now.
Question #3: These 200 Textboxes / labels text or values to be populated into Excel sheet as headers.
Still waiting for the resolution. If anyone need more detailed info pleae let me know.
Thanks to Reverend Jim. His code helped me a lot.
Reverend Jim 5,053 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster
Try creating a form with one button in the top left. Set the form AutoScroll to True and use this code
Public Class Form1
Const ROWS As Integer = 100 'number of rows
Const COLS As Integer = 3 'number of columns
Const BOXW As Integer = 100 'textbox width
Const BOXH As Integer = 20 'textbox height
Const BOXM As Integer = 3 'margin between boxes
Private TextBoxes(ROWS, COLS) As TextBox
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
For row As Integer = 1 To ROWS
For col As Integer = 1 To COLS
Dim xpos As Integer = 10 + (col - 1) * (BOXW + BOXM)
Dim ypos As Integer = 50 + (row - 1) * (BOXH + BOXM)
Dim newbox As New TextBox
newbox.Size = New Drawing.Size(BOXW, BOXH)
newbox.Location = New Point(xpos, ypos)
newbox.Tag = {row, col}
newbox.Text = "Textbox(" & row & ", " & col & ")"
AddHandler newbox.TextChanged, AddressOf TextBox_TextChanged
TextBoxes(row, col) = newbox
Me.Controls.Add(newbox)
Next
Next
End Sub
Private Sub TextBox_TextChanged(sender As System.Object, e As System.EventArgs)
Dim tbx As TextBox = sender
Me.Text = "Box(" & tbx.Tag(0) & ", " & tbx.Tag(1) & ") is " & tbx.Text
End Sub
End Class
By storing the textbox references in an array you get easier access later on. By storing the array {row, col}
in the tag you can easily back-reference the coordinates. Also, by creating the Consts for the size and number of boxes you can change the layout more easily. You might even create Consts for the start position of the upper left box as well.
ACE-- 0 Light Poster
Hi Reverend Jim,
Thanks for the code anyway. Can you please suggest code for updated this textbox values in Excel as .xls format.
Below is my code for creating textboxes. I will try your code by the way.
Now I want to populate this Label Text as Excel Header (1st Row)
Dim R is form1.textbox.text
Dim C As Integer
Dim R As Integer
Dim t As TextBox
Dim l As Label
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Panel1.Controls.Remove(l)
Panel1.Controls.Remove(t)
R = TB.Text
For Row As Integer = 1 To R
For Col As Integer = 1 To 1
t = New TextBox
t.Size = New Drawing.Size(240, 20)
t.Location = New Point(220 * Col, 30 * Row)
t.Name = "Textbox" & Row
t.Text = "Textbox" & Row
Panel1.Controls.Add(t)
l = New Label
l.Size = New Drawing.Size(240, 20)
l.Location = New Point(20 * Col, 30 * Row)
l.Font = New Font(l.Font, FontStyle.Bold)
l.Name = "Label" & Row
l.Text = "LB" & Row
Panel1.Controls.Add(l)
Next
Next
Try
Catch ex As InvalidCastException
Exit Sub
End Try
End Sub
Reverend Jim 5,053 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster
I don't understand what you are asking.
ACE-- 0 Light Poster
Label1.text = sheet1 A1
Label2.text = sheet1 B1
Label3.text = sheet1 C1
and so on..
At runtime I might create what ever the amount of Labeles I am creating those labeles text to be populated into above formats.
Is there code for that. I have tried but. Not able to find the correct loop for that.
Can you please help me out.
Ancient Dragon 5,243 Achieved Level 70 Team Colleague Featured Poster
textbox values in Excel as .xls format
I mentioned this before -- if you want to write directly into xls format you have to use Office Autimation. I know VB.NET 2012 and 2013 easily does that, and in that case there is no need for all those text boxes. I even posted a picture of that in this thread.
Reverend Jim 5,053 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.