select excel sheet using vb.net to add data into

Question

I'm trying to use excel as a data base for a WinFormsApp using vb.net in VS 2022

So I used: (part of the code)

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click  
    Dim app As New Excel.Application
    Dim wk As Excel.Workbook = app.Workbooks.Open("file directory", [ReadOnly]:=False)
    Dim wt As Excel.Worksheet = app.Worksheets("Sheet2") 'i also used 2 instead of "sheet2"
        app.Range(TextBox2.Text).Activate()
        app.ActiveCell.Value = TextBox1.Text

the code add the data in the desired cell (textbox2) but for some reason it doesn't register it in the provided sheet

like it always adds the data in the sheet that was used the last time i edited the excel file. it basically ignores the sheet I provide and it doesn't show any errors on run time.

ps: I import these upon running (the very top of the coding page)

Imports System.IO
Imports System.Runtime.CompilerServices
Imports System.Runtime.ConstrainedExecution
Imports System.Text
Imports System.Threading
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.VisualBasic.ApplicationServices

If you still can't understand the issue its simply that the program confuses what sheet to obtain/add the data from\to. but it always gets the cell right

_I'm trying to use excel as a data base_: Excel isn't a database. — Tu deschizi eu inchid, Jan 01 '23 at 18:28
The following may be helpful: https://stackoverflow.com/questions/71754355/after-parameter-of-sheets-add-method/71755749#71755749 and https://stackoverflow.com/questions/72828875/get-excel-active-worksheet-with-c-sharp/72832357#72832357 — Tu deschizi eu inchid, Jan 01 '23 at 18:35

score 0 · Answer 1 · answered Jan 02 '23 at 08:16

Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

xlApp.Workbooks.Open("D:\excel.xlsx")
xlWorkBook = xlApp.ActiveWorkbook
xlWorkSheet = xlWorkBook.ActiveSheet

'This is to fill excel from the source you want
xlWorkSheet.Cells(1, 1).Value = TextBox1.Text

xlWorkBook.RefreshAll()
xlWorkBook.Save()
xlApp.Workbooks.Close()
xlApp.Quit()

Try that one

select excel sheet using vb.net to add data into

1 Answers1