user3094480 user3094480 - 14 days ago 4
Vb.net Question

How to search textbox value in Excel sheet and return data to other textboxes

I'm new to VB.Net and I have learnt how to get data from Excel to a

DataGridView
. Now can anyone help me learn how to get Excel data into textboxes?

I have a form with 6 textboxes and a search button. When I enter a value in
textbox1
and click on the search button, I want to search an Excel file which is selected by an
OpenFileDialog
and saved in the
destination1
string

destination1 = destipath & Filename.Text & "_" & dtedate.ToShortDateString & ".xlsx" '& Format(TimeOfDay, "h.mm.ss tt")


The Excel sheet1 has ID, Name,Father, Mother, Address and Phone. The VB.Net form has 6 textboxes. When I enter, for example, ID as 30 in textbox1, the code must search for ID 30 in column A. If found then textboxes 2-6 must display Name, Father, Mother, Address, Phone.

The sheet rows are dynamic but the columns are fixed. I have written code to do this in Excel VBA but I am trouble writing code to do it in VB.Net?

I have tried this in VB.Net but does not identify the
LookIn
and
LookAt
like in Excel.

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
Dim xlappFile As Excel.Application = Nothing
Dim xlFile_WB As Excel.Workbook = Nothing
Dim xlFile_WS As Excel.Worksheet = Nothing
Dim xlFirstfile_WS1 As Excel.Worksheet = Nothing
Dim FoundRange As Excel.Range
Dim searchID As String
searchID = Textbox1.Text

xlFile_WB = xlappFile.Workbooks(destination1) ' destination1 is selected by 'user
MessageBox.Show(destination1)
xlFile_WS = xlappFile.Worksheets(1)
FoundRange = xlFile_WS.Cells.Find(What:=searchID, LookIn:=Excel.XlFindLookIn.xlFormulas, LookAt:=Excel.XlLookAt.xlWhole)

If FoundRange Is Nothing Then
textbox2.Text = "not found"
textbox3.Text = "not found"
textbox4.Text = "not found"
textbox5.Text = "not found"
textbox6.Text = "not found"
Else
textbox2.Text = FoundRange.Offset(0, 2).Value
textbox3.Text = FoundRange.Offset(0, 3).Value
textbox4.Text = FoundRange.Offset(0, 4).Value
textbox5.Text = FoundRange.Offset(0, 5).Value
textbox6.Text = FoundRange.Offset(0, 6).Value
End If
End Sub

Answer

Unlike under Excel/VBA, In VB.net you will need to scope the Excel constants by their namespace, like this:

FoundRange = xlFile_WS.Cells.Find(What:=searchID, LookIn:=Excel.XlFindLookIn.xlFormulas, LookAt:=Excel.XlLookAt.xlWhole)