Consulting

Results 1 to 3 of 3

Thread: Macro in Outlook to look up data in Closed Excel File

  1. #1
    VBAX Regular
    Joined
    Feb 2021
    Posts
    20
    Location

    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?

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    Last edited by SamT; 06-22-2021 at 12:41 PM. Reason: If LM = "" THen Exit sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Feb 2021
    Posts
    20
    Location
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •