[SOLVED] VBA facing issue with FIND function using Word

VBA facing issue with FIND function using Word

I am currently struggling with below requirements:

a. Read the excel file placed on sharepoint from Word using VBA. b. Find the value in a specific column and identify that row c. Based on that row read the value from same row from different columns.

Code is as Below :

Sub Return_a_Value_from_Excel()
     Dim mySpreadsheet As Excel.workbook
     Dim strSalesTotal As String, i As Object
     
     Set mySpreadsheet = GetObject("Sharepoint/xlsx") ' Code breaks here
        
     Set i = mySpreadsheet.Worksheets("Sheet1").Range("AO:AO").Find(What:=2272, LookIn:=xlValues).Row
     
     'mySpreadsheet.Application.ActiveSheet.Range("A1:AO1000").Find(2272).Row ' Code breaks here
     
     strSalesTotal = mySpreadsheet.Cells(i, 42)
     Set mySpreadsheet = Nothing
     Selection.TypeText strSalesTotal
     
     Selection.TypeParagraph
     
End Sub

Solution

Something like this should work:

Sub Return_a_Value_from_Excel()
     
    Dim xlApp As Excel.Application, xlWb As Excel.Workbook
    Dim res, srch
    
    Set xlApp = New Excel.Application
    xlApp.Visible = True 'for troubleshooting...
    Set xlWb = xlApp.Workbooks.Open("pathtosharepointfile.xlsx")
    
    srch = 2272 'value to be searched
    'assuming you want an exact match...
    res = xlApp.VLookup(srch, xlWb.Worksheets("Sheet1").Range("AO:AP"), 2, False)
    
    If Not IsError(res) Then 'got a match?
        Selection.TypeText res
        Selection.TypeParagraph
    Else
        MsgBox "No match found for value '" & srch & "'", vbExclamation
    End If
    
    xlWb.Close False
    xlApp.Quit
     
End Sub