Question Using Vlookup in Excel automation

[Jonnyk]

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

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]

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]

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

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