How to search textbox value in excel sheet col a and return col B to col F to other textboxes

Question

0.00/5 (No votes)

See more:

Hi,

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 give textbox1 value and click on search button, I want to search an excel file which is selected by opendialogfile and saved in destination1 string

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

The excel sheet1 has ID,Name,Father,Mother,Address,Phone and VB.net form has 6
textboxes respectively. Example when I enter i.e ID as 30 in textbox1, the code must search ID 30 in col A if found then textbox 2,3,4,5,6 must display Name,Father,Mother,Address,Phone.

The sheet row are dynamic but col are fixed. I have written the following code in excel vba but how can I implement similarly in VB.net?

What I have tried:

vba

 Dim FoundRange As Range
 Hide   Expand    Copy Code
Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
 
Public class Test
 
    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
End Class

Posted 22-Nov-16 22:44pm

arpita aradhya

Updated 21-Sep-20 6:28am

v3

Add a Solution

Comments

arpita aradhya 23-Nov-16 6:27am

I have improved the code and edit the code in the post. Now I have error at line xlFile_WB = xlappFile.Workbooks(destination1). system nullreferenceexception object reference not set

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2016-11-22T23:40:00

Solution 1

In VB.NET you would need to use the Microsoft.Office.Interop.Excel namespace ()[^].

Posted 22-Nov-16 23:40pm

Richard MacCutchan

Member 14944934 · Answer 2 · 2020-09-21T06:28:00

Hello Friends, Please find full working code. It's working !!!

Option Explicit On
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Public Class Form1
Dim xlapp As Application
Dim xlworkbook As Workbook
Dim xlworksheet As Worksheet
Dim xlworksheet2 As Worksheet
Dim FoundRange As Excel.Range
Dim searchID As String
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

xlapp = New Application With {.ScreenUpdating = False}

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Try
xlworkbook = xlapp.Workbooks.Open("C:\DATA\Packaging\Packaging.xlsx")
xlworksheet = xlworkbook.Worksheets("Packaging")
searchID = TextBox1.Text
FoundRange = xlworksheet.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"
TextBox7.Text = "not found"
Else
TextBox2.Text = FoundRange.Offset(0, 1).Value
TextBox3.Text = FoundRange.Offset(0, 2).Value
TextBox4.Text = FoundRange.Offset(0, 3).Value
TextBox5.Text = FoundRange.Offset(0, 4).Value
TextBox6.Text = FoundRange.Offset(0, 5).Value
TextBox7.Text = FoundRange.Offset(0, 6).Value
End If

xlapp.ScreenUpdating = True
xlworkbook.Save()
xlworkbook.Close()
Catch ex As Exception
MessageBox.Show("Excel File is missing from its location C:\DATA\Packaging folder or renamed !!!")
End Try
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
TextBox1.Clear()
TextBox1.Text = Nothing
TextBox2.Clear()
TextBox2.Text = Nothing
TextBox3.Clear()
TextBox3.Text = Nothing
TextBox4.Clear()
TextBox4.Text = Nothing
TextBox5.Clear()
TextBox5.Text = Nothing
TextBox6.Clear()
TextBox6.Text = Nothing
TextBox7.Clear()
TextBox7.Text = Nothing
TextBox1.Select()
End Sub
End Class