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, Mar 09 '13 at 06: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, Mar 09 '13 at 06: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, Mar 09 '13 at 06: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, Mar 09 '13 at 06:30
@Floris sorry my bad i thought when i index cells its "column, row" also. but anyway thanks for your help.. :) — user2107624, Mar 09 '13 at 06:36
No problem - I suddenly realized that we were in violent agreement but you didn't know it... it **is** really confusing. Did you try to run my code (or Jeremy's, before he changed everything?) — Floris, Mar 09 '13 at 06:38

Jeremy Thompson · Answer 1 · 2013-03-09T06:33:46.047

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

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. — user2107624, Mar 09 '13 at 06:04
Jeremy - these functions you just added reminded me of an answer I gave earlier today: http://stackoverflow.com/questions/15260075/converting-excel-column-letters-to-corresponding-column-numbers/15260136#15260136 — Floris, Mar 09 '13 at 06:32

Floris · Answer 2 · 2013-03-09T06:39:41.297

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 09 '13 at 06:39

answered Mar 09 '13 at 06:03

Floris

45,857
6
70
122

@JeremyThompson - definitely crossed in cyberspace... Thanks though. – Floris Mar 09 '13 at 06:06
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 – user2107624 Mar 09 '13 at 06:11
1

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. – Floris Mar 09 '13 at 06:15

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

2 Answers2