VLOOKUP on different Workbook

[dloskot]

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.

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.

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

 

[6StringJazzer]

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.

' 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