how to insert data from textbox to excel using vb.net?

Question

I'm stuck here, I want to insert the data from 5 textbox to existing excel file in columns. I found code but its inserting by rows. I have below a code that finds the last non empty row and from that row I want to move to the next row and insert data there, for example last row is A2, I want to insert new data to A3, B3, C3, D3, E3.

I can't get the right loop for this.

Dim lRow As Long = 0
    Call OpenExcelFile("C:\Users\PB\Desktop\BookRecords.xlsx", 1)

    With xlWorkSheet
        If EXL.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A2"), _
                          LookAt:=Excel.XlLookAt.xlPart, _
                          LookIn:=Excel.XlFindLookIn.xlFormulas, _
                          SearchOrder:=Excel.XlSearchOrder.xlByRows, _
                          SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1


        End If
        lRow += 1

    End With
    MessageBox.Show("The last row in Sheet1 which has data is " & lRow)
    Dim j As Integer
    j = 3
    For i As Integer = j To 8
        xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtTitle.Text
        xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtAuthor.Text
        xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtEdition.Text
        xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtPublisher.Text
        xlWorkSheet.Range(xlWorkSheet.Cells(lRow + 1, j).item).Value = txtISBN.Text
    Next
    j += 1

    Call SaveAndCloseExcelSub()

Could you please elaborate, simply include a Excel Mockup screenshot in your question of how you want it to look. I'm not sure what you mean by "column changing depending on the last row retrieved" — Jeremy Thompson
– Jeremy Thompson, Commented Mar 9, 2013 at 6:10
@JeremyThompson i mean the column is the one incrementing and the row is as is. Like for example, A3 is the next row then B3 then C3 then D3 and E3 Not A3, A4, A5, A6, A7 — user2107624
– user2107624, Commented Mar 9, 2013 at 6:18
What are you trying to achieve with your for i loop? Why do you increment j after the loop? Note - when Excel says "A2", it uses "column, row" order. But when you index cells, it is in "row, column" order. This is REALLY confusing - and may be why we seem to be talking past each other. — Floris
– Floris, Commented Mar 9, 2013 at 6:27
I think I get you now, your asking for the Code to increment the columns. In your for loop to get the column letter use the ColumnNumberToName(i) function so it will be "B" + 3, then "C" + 3 — Jeremy Thompson
– Jeremy Thompson, Commented Mar 9, 2013 at 6:30
@Floris sorry my bad i thought when i index cells its "column, row" also. but anyway thanks for your help.. :) — user2107624
– user2107624, Commented Mar 9, 2013 at 6:36

Jeremy Thompson · Accepted Answer · 2013-03-09 06:33:46Z

2

Does this help:

Dim j As Integer
j = 3
For i As Integer = j To 8
MsgBox("Cell column: " & ColumnNumberToName(i))
Next

Code to convert numbers to Excel Column Names (A-Z, AA, etc):

Public Shared Function ColumnNumberToName(columnNumber As Int32) As String
Dim dividend As Int32 = columnNumber
Dim columnName As [String] = [String].Empty
Dim modulo As Int32
While dividend > 0
    modulo = (dividend - 1) Mod 26
    columnName = Convert.ToChar(65 + modulo).ToString() + columnName
    dividend = DirectCast(((dividend - modulo) / 26), Int32)
End While

Return columnName
End Function

Public Shared Function ColumnNameToNumber(columnName As [String]) As Int32
If [String].IsNullOrEmpty(columnName) Then
    Throw New ArgumentNullException("columnName")
End If
Dim characters As Char() = columnName.ToUpperInvariant().ToCharArray()
Dim sum As Int32 = 0
Dim i As Int32 = 0
While i < characters.Length
    sum *= 26
    sum += (characters(i) - "A"C + 1)
    System.Math.Max(System.Threading.Interlocked.Increment(i),i - 1)
End While
Return sum
End Function

edited Mar 9, 2013 at 6:33

answered Mar 9, 2013 at 6:00

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2107624 Over a year ago

that will only write on cells with specified column what i want to do is the column is changing depending on the last row retrieved.

Floris Over a year ago

Jeremy - these functions you just added reminded me of an answer I gave earlier today: stackoverflow.com/questions/15260075/…

Floris · Accepted Answer · 2013-03-09 06:39:41Z

2

As it is, all your values are being written to the same cell; then you move down a row and do it again. Cells are addressed as (row, column) - so you need to change your code like this:

Dim j As Integer
j = 3
For i As Integer = j To 8
    xlWorkSheet.Cells(lRow + i, 1).Value = txtTitle.Text
    xlWorkSheet.Cells(lRow + i, 2).Value = txtAuthor.Text
    xlWorkSheet.Cells(lRow + i, 3).Value = txtEdition.Text
    xlWorkSheet.Cells(lRow + i, 4).Value = txtPublisher.Text
    xlWorkSheet.Cells(lRow + i, 5).Value = txtISBN.Text
Next

Not sure if you need to start with i = 3 if you add lRow to it but I'm sure you can work that out.

If you want the order reversed, do this (this example copies only one lot of text... I am beginning to suspect there is no need for the loop)

dim i as Integer
i = 1; ' if you want column A
With xlWorkSheet
    .Cells(lRow    , i).Value = txtTitle.Text
    .Cells(lRow + 1, i).Value = txtAuthor.Text
    .Cells(lRow + 2, i).Value = txtEdition.Text
    .Cells(lRow + 3, i).value = txtPublisher.Text
    .Cells(lRow + 4, i).Value = txtISBN.Text
End With

edited Mar 9, 2013 at 6:39

answered Mar 9, 2013 at 6:03

Floris

46.6k7 gold badges73 silver badges128 bronze badges

3 Comments

Floris Over a year ago

@JeremyThompson - definitely crossed in cyberspace... Thanks though.

user2107624 Over a year ago

same with @JeremyThompson i dont want to specify the columns because its always changing depending in the last row retrieved. how to code that the column is the one incrementing and the row is as is? thank you

Floris Over a year ago

Note - in your question you stated "I want to insert new data to A3, B3, C3, D3, E3." Thus for a given value of i, the example above leaves the ROW alone (lRow + i), and changes the COLUMN (2,3,4,5,6). For the next item, you go down one row. If you want it the other way around, just swap the indices. But maybe you need to clarify your question... Just noticed that @JeremyThompson already asked you to do that. He's one step ahead of me.

Collectives™ on Stack Overflow

how to insert data from textbox to excel using vb.net?

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related