CODEWITHSUNDEEP
vbaexcel
Pourya Ashena
Pourya Ashena

Reputation: 49

vlookup extract info from different excel workbook

I want to make a user-form with Vlookup. The information is stored in a different workbook. How can I extract the information I need from different excel workbook into my text fields using the following VBA code?

Private Sub Textan_AfterUpdate()

'check to see if value exists        
If WorksheetFunction.CountIf(C:\Users\poury\Desktop\ADDON Order Tool\AL010.xlsx.Sheet2.Range("B:B"), Me.Textan.Value) = 0 Then
    MsgBox "This is an incorrect Article Number"
    Me.Textan.Value = ""    
    Exit Sub
End If

With Me    
    Textan1 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 2, 0)
    Textan2 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 3, 0)
    Textan3 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 4, 0)
    Textan4 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 5, 0)
    Textan5 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 6, 0)
    Textan6 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 7, 0)
    Textan7 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 8, 0)
    Textan8 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 9, 0)    
End With

End Sub

Upvotes: 0

Views: 383

Answers (3)

Tharindu Rathnauyaka
Tharindu Rathnauyaka

Reputation: 3

Application.ScreenUpdating = False

Dim wb As Workbook
Dim rowIndex As Variant
Set wb = Workbooks.Open("F:\My DAFM Project_v1\Employee Data Base File_2019.xlsx").Worksheets("EmpData").Range("A:E")
rowIndex = Application.Match(Me.TextBox9.Value.Cells(y, 11), 0)

Dim EmpData As Worksheet
Set EmpData = wb.Worksheets("EmpData") 'changed file name as needed

If IsError(rowIndex) Then
Me.TextBox9.Value = ""

'check to see if value exists
If WorksheetFunction.CountIf(EmpData.Range("A:e"), Me.TextBox9.Value) = 0 Then
    MsgBox "This is an Incorrect SAP ID"
    Me.TextBox9.Value = ""

    Exit Sub
End If

With Me
    TextBox10.Text = Application.WorksheetFunction.VLookup(CLng(Me.TextBox9), EmpData.Range("b:E"), 3, 0)
    TextBox11.Text = Application.WorksheetFunction.VLookup(CLng(Me.TextBox9), EmpData.Range("b:E"), 4, 0)
    TextBox12.Text = Application.WorksheetFunction.VLookup(CLng(Me.TextBox9), EmpData.Range("b:E"), 5, 0)

End With

wb.Close False

End Sub

Upvotes: 0

user3598756
user3598756

Reputation: 29421

You could refactor your code and take advantage of sort of a relationship between your textbox names and "Lookup" range columns to retrieve

Private Sub Textan_AfterUpdate()
    Dim rowIndex as Variant

    Application.Screenupdating = False

    With Workbooks.Open("C:\Users\poury\Desktop\ADDON Order Tool\AL010.xlsx").Worksheets("Sheet2").Range("Lookup") '<--| open needed workbook and reference its "Sheet2" "Lookup" range (change "Sheet2" to your actual sheet name)
        rowIndex = Application.Match(Me.Textan.Value, .Columns(1), 0) '<--| try searching "Lookup" range first column for 'Textan' value 
        If IsError(rowIndex) Then 'check to see if value exists   
            MsgBox "This is an incorrect Article Number"
            Me.Textan.Value = ""                  
        Else
            For iText = 1 to 8
                Me.Controls("Textan" & iText) = .Cells(rowIndex, iText+ 1)
            Next
        End If
    End With
    ActiveWorkbook.Close False '<--| close opened workbook
    Application.Screenupdating = True
End Sub

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27249

Just open the workbook behind the scenes:

Private Sub Textan_AfterUpdate()

Application.Screenupdating = false

Dim wb as Workbook
Set wb = Workbooks.Open("C:\Users\poury\Desktop\ADDON Order Tool\AL010.xlsx")

Dim Sheet2 as Worksheet
Set Sheet2 = wb.Worksheets("Sheet2") 'change name as needed

'check to see if value exists        
If WorksheetFunction.CountIf(Sheet2.Range("B:B"), Me.Textan.Value) = 0 Then
    MsgBox "This is an incorrect Article Number"
    Me.Textan.Value = ""    
    Exit Sub
End If

With Me    
    Textan1 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 2, 0)
    Textan2 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 3, 0)
    Textan3 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 4, 0)
    Textan4 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 5, 0)
    Textan5 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 6, 0)
    Textan6 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 7, 0)
    Textan7 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 8, 0)
    Textan8 = Application.WorksheetFunction.VLookup(CLng(Me.Textan), Sheet2.Range("Lookup"), 9, 0)    
End With

wb.Close false

End Sub

Upvotes: 1

Related Questions