Macro in Outlook to look up data in Closed Excel File

[pebcak]

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?


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

[SamT]

Something like this?

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:

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

[pebcak]

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.


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?