Macro in Outlook to look up data in Closed Excel File

I'd like to make a macro that essentially does a VLookUp from Outlook into an Excel file. Instead of actually using the VLOOKUP function I just searched for the data in one column, and then got the data from the same row one column over.

I'm not sure if this works or not, yet, because I don't know how to get Outlook to access the Excel file itself. Any help?

Code:

---

Sub LookUpIndexNumber()


Dim objExcel As Excel.Application
Dim exWb As Excel.Workbook
Dim ExcelFileName As String
Dim ColumnA As String
Dim ColumnB As String


ExcelFileName = ("U:\Project Support\Local Database\TortMattersLitigation.xlsx")


Set exWb = objExcel.Workbooks.Add(ExcelFileName)


ColumnA = InputBox("Enter LM#")


ColumnB = exWb.Worksheets("Sheet1").Range("A:A").Find(ColumnA).Offset(0, 1).Value


MsgBox = ColumnB


End Sub

---

Something like this?

Code:

---

Option Explicit

Public Function FindLM() As String
Dim objExcel As Object ' Excel.Application
Dim exWb As Object    ' Excel.Workbook
Dim ExcelFileName As String
Dim LM As String
Dim Result As String

    ExcelFileName = "U:\Project Support\Local Database\TortMattersLitigation.xlsx"
    LM = InputBox("Enter LM#")
   
    On Error Resume Next
    Set objExcel = GetObject(, "excel.application")
    On Error GoTo 0
    If objExcel Is Nothing Then Set objExcel = New Excel.Application
   
    Set exWb = objExcel.Workbooks.Open(ExcelFileName)
   
    Result = exWb.Worksheets("Sheet1").Range("A:A").Find(LM).Offset(, 1).Value
    MsgBox = Result
   
FindLM = Result
End Function

Private Sub Test_FindLM()
Dim X
X = FindLM
End Sub

---

OR, Something like this:

Code:

---

Option Explicit

Public Function FindLM(Optional LM As String) As String
  If LM = "" Then
      MsgBox "Must use proper LM Number"
      Exit Function
  End If

Dim objExcel As Object ' Excel.Application
Dim exWb As Object    ' Excel.Workbook
Dim ExcelFileName As String
Dim Result As String

    ExcelFileName = "U:\Project Support\Local Database\TortMattersLitigation.objExcelsx"
   
    On Error Resume Next
    Set objExcel = GetObject(, "excel.application")
    On Error GoTo 0
    If objExcel Is Nothing Then Set objExcel = New Excel.Application
   
    Set exWb = objExcel.Workbooks.Open(ExcelFileName)
   
    Result = exWb.Worksheets("Sheet1").Range("A:A").Find(LM).Offset(0, 1).Value
    MsgBox = Result
   
FindLM = Result
End Function

Private Function WhichLM() As String
Dim LM As String
SartOver:
    LM = InputBox("Enter LM#")
   
    If Len(LM) <> nn Then GotTo StartOver
    If CLng(LM) < nnnn Or CLng(LM) > nnnn Then GoTo StartOver
  'More Tests As desired
   
'If all tests pass then...
WhichLM = LM
End Function

Private Sub Test_WhichLM()
Dim X
X = WhichLM
End Sub
Private Sub Test_FindLM()
Dim X
X = FindLM(WhichLM)
End Sub

---

Thanks I appreciate the response. This didn't quite do what I was looking for.

The scenario is I'm in Outlook, the Excel Workbook that is the database is not open. I want to run a macro in Outlook that accesses that Workbook without opening it, pulls out a piece of information, and sets a string to that value.

If the actual Workbook were open, and I wrote a macro in THAT workbook, this works perfectly.

Code:

---

Sub FindIndexNumber()


Dim strLM As String
Dim strIndex As String
Dim myRange As Range


strLM = InputBox("Enter LawMan#")


Set myRange = Range("a1:j18858")
strIndex = Application.WorksheetFunction.VLookup(strLM, myRange, 7, False)


Clipboard strIndex




End Sub

---

That code could, obviously, could use a lot of work up to make it user friendly but it works for now.

Basically, how do I have Outlook identify this Excel Workbook, which is not open, and run a code that serves the same function ... look up data in one column, get data from another column in the same row.

Any thoughts?