I want to know how to link between two workbook in microsoft excel using vlookup via vba method

Question

I've got 2 file: vlookup.xlsx and test.xlsx

This is what I typed in VBA:

Sub Loop8()

    Dim filename1 As String
    filename1 = "C:\Users\tom\Desktop[vlookup.xlsx]"

    Do

    ActiveWorkbook.Worksheets("Sheet4"),Range("AB2")=vlookup(Y2,"& filename1 & "Sheet1!$Y:$AB),4,false)"

    ActiveCell.Offset(0, 1).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -1))

End Sub

PS: Y2 is being reference to the product code.

score 1 · Accepted Answer · edited May 23 '17 at 12:01

Below code should work for you.

Sub Loop8()
    Dim fileName As String, filePath As String
    Dim lookupWB As Workbook
    Dim lookupWS As Worksheet, currWS As Worksheet

    Dim wb As Workbook, ws As Worksheet
    Dim book2Name As String

    Set currWS = ThisWorkbook.Sheets("Sheet4")
    fileName = "vlookup.xlsx"
    filePath = "C:\Users\tom\Desktop\" & fileName
    If IsBookOpen(fileName) = False Then Workbooks.Open (filePath)
    Set lookupWB = Workbooks(fileName)
    Set lookupWS = lookupWB.Sheets("Sheet1")
    currWS.Range("AB2") = Application.WorksheetFunction.VLookup(currWS.Range("Y2").Value, lookupWS.Range("$Y:$AB"), 4,False)        
End Sub

IsBookOpen Function:

Function IsBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsBookOpen = False
    Case 70:   IsBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

IsBookOpen function is taken from here written by @SiddharthRout

Thanks for helping :D I have another solution for the problem — Ky TySon, Jun 06 '16 at 00:03

I want to know how to link between two workbook in microsoft excel using vlookup via vba method

1 Answers1