Question Using Vlookup in Excel automation

Jonnyk

New member
Joined
Dec 29, 2014
Messages
2
Programming Experience
Beginner
Hi. I'm trying to use Excels Vlookup function to populate a text box within a form. I want to enter a number, look up this number in a named range and return the corresponding data into the second text box. I've got so far but my knowledge has come to its limit, and the code below doesn't work :unhappy: Please could someone point me in the right direction?

The workbook is named 'Database2.xlsx' it lives on the 'C:' drive. The sheet with the data is 'Sheet1' and the named range is 'Table1'

Thanks,

Jon

VB.NET:
Imports System.Data
Imports Excel = Microsoft.Office.Interop.Excel


Public Class Form1
    Dim conn As System.Data.OleDb.OleDbConnection
    Dim da As System.Data.OleDb.OleDbDataAdapter
    Dim xlApp As Excel.Application
    Dim xlAddy As String = "C:\Database2.xlsx"
    Dim VlResult As VariantType
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlRange As Excel.Range


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetData.Click
        If txtId.Text = "" Then
            Return
        End If


        xlBook = xlApp.Workbooks("C:\Database2.xlsx")
        xlSheet = xlBook.Worksheets("Sheet1")
        xlRange.Name = ("Table1")


        Dim id As VariantType


        id = txtId.Text
      
        Try
            With xlRange


                VlResult = xlApp.VLOOKUP(id & ", Table1,  2 ,  0")


            End With


        Catch ex As Exception


        End Try


        txtName.Text = VlResult


    End Sub
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,439
Location
Norway
Programming Experience
10+
VlResult = xlApp.VLOOKUP(id & ", Table1, 2 , 0")
I can't see there is a Application.Vlookup method, but there is a Application.WorksheetFunction.VLookup method.
Further, you're passing a single string argument, the Vlookup function has three required arguments: WorksheetFunction.VLookup Method (Excel)
 

Jonnyk

New member
Joined
Dec 29, 2014
Messages
2
Programming Experience
Beginner
Using Vlookup in VB.Net Working

Thanks JohnH. I couldn't see that mistake for looking :blue: There were other schoolboy errors as well but I've got it working now. For anyone else interested in using Vlookup in future the code below works a treat. :) Thanks again John

VB.NET:
Imports Excel = Microsoft.Office.Interop.Excel


Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetData.Click


        Dim xlApp As Excel.Application
        Dim VlResult As String = ("")
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlRange As Excel.Range




        If txtId.Text = "" Then
            Return
        End If


        xlApp = New Excel.Application()
        xlBook = xlApp.Workbooks.Open("C:\Database2.xlsx")
        xlSheet = xlBook.Worksheets("Table1")
        xlRange = xlSheet.UsedRange




        Dim id As VariantType


        id = txtId.Text


        Try




            VlResult = xlApp.WorksheetFunction.VLookup(id, xlRange, 2, 0)






        Catch ex As Exception


        End Try




        txtName.Text = VlResult


        xlBook.Close()


    End Sub
 
Top Bottom