VLOOKUP on different Workbook

dloskot

New Member
Joined
Oct 18, 2015
Messages
43
I have a macro that successfully does a VLOOKUP on a sheet within the same workbook. I would now like to do the same thing but do the VLOOKUP on a sheet in a different workbook. I have done a lot of searching and reading but I cannot seem to get code that has no errors and works. Below is code that is working on a sheet in the same workbook. I would like to do it without having to open the second workbook.

VBA Code:
Text_Last_Name.Value = Application.WorksheetFunction.Proper(Text_Last_Name.Value)
Dim LName As String
LName = Text_Last_Name.Value

On Error GoTo Skip
    Text_First_Name.Text = Application.WorksheetFunction.VLookup(LName, ThisWorkbook.Sheets("Recuring Emails").Range("A2:G23").Value, 2, False)
    Text_Address.Text = Application.WorksheetFunction.VLookup(LName, ThisWorkbook.Sheets("Recuring Emails").Range("A2:G23").Value, 3, False)
    Text_City.Text = Application.WorksheetFunction.VLookup(LName, ThisWorkbook.Sheets("Recuring Emails").Range("A2:G23").Value, 4, False)
    Text_State.Text = Application.WorksheetFunction.VLookup(LName, ThisWorkbook.Sheets("Recuring Emails").Range("A2:G23").Value, 5, False)
    Text_Zip.Text = Application.WorksheetFunction.VLookup(LName, ThisWorkbook.Sheets("Recuring Emails").Range("A2:G23").Value, 6, False)
    Text_Email.Text = Application.WorksheetFunction.VLookup(LName, ThisWorkbook.Sheets("Recuring Emails").Range("A2:G23").Value, 7, False)
    ComboBox_Donation_Type = "Recurring"

I tried the following but I keep getting Runtime Error code 9.
VBA Code:
Dim Table2 As Range
Set Table2 = Workbooks("C:\Users\Doug\Desktop\KAH Test Data\KAH Main Update Sheet-10-1-21.xlsm").Sheets("Recuring Emails").Columns("A:G")

On Error GoTo Skip
    fname = Application.WorksheetFunction.VLookup(lname, Table2, 2, False)
    MsgBox ("First Name = " & fname)

I would appreciate any advice you can give me.
Thanks Doug
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
The code you are trying to use requires the other file to be open first. In general, VBA requires a workbook to be open to be able to do anything at all with it.

It is possible to read data from a closed workbook using the ADO protocol, which treats the closed file like a database. Here is some code from Ron deBruin that retrieves the data. I think you will have write the lookup logic rather than using VLOOKUP.

VBA Code:
' http://www.rondebruin.nl/win/s3/win024.htm

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
' 30-Dec-2007, working in Excel 2000-2007
    Dim rsCon As Object
    Dim rsData As Object
    Dim szConnect As String
    Dim szSQL As String
    Dim lCount As Long

    ' Create the connection string.
    If Header = False Then
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No"";"
        End If
    Else
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
    End If

    If SourceSheet = "" Then
        ' workbook level name
        szSQL = "SELECT * FROM " & SourceRange$ & ";"
    Else
        ' worksheet level name or range
        szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
    End If

    On Error GoTo SomethingWrong

    Set rsCon = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")

    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1

    ' Check to make sure we received data and copy the data
    If Not rsData.EOF Then

        If Header = False Then
            TargetRange.Cells(1, 1).CopyFromRecordset rsData
        Else
            'Add the header cell in each column if the last argument is True
            If UseHeaderRow Then
                For lCount = 0 To rsData.Fields.Count - 1
                    TargetRange.Cells(1, 1 + lCount).Value = _
                    rsData.Fields(lCount).Name
                Next lCount
                TargetRange.Cells(2, 1).CopyFromRecordset rsData
            Else
                TargetRange.Cells(1, 1).CopyFromRecordset rsData
            End If
        End If

    Else
        MsgBox "No records returned from : " & SourceFile, vbCritical
    End If

    ' Clean up our Recordset object.
    rsData.Close
    Set rsData = Nothing
    rsCon.Close
    Set rsCon = Nothing
    Exit Sub

SomethingWrong:
    MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
           vbExclamation, "Error"
    On Error GoTo 0

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top