[UPGRADE CODE] Find and open specific file based on a reference value that the user fill in

[eddorena]

Hi All,

I have some VBA code below that functioned as lookup value based on user fill in in specific cell (bapref), and now I need to modify the code search the lookup file with a specific name on same folder based on the (bapref) and add new formulas below.

=FILTER(XLOOKUP(ISGPN,'[FILEBAPREF.xlsx]Sheet1'!$B:$B,'[FILEBAPREF.xlsx]Sheet1'!$G:$G,"",0),XLOOKUP(A:A,'[FILEBAPREF.xlsx]Sheet1'!$B:$B,'[FILEBAPREF.xlsx]Sheet1'!$G:$G,"",0)<>"")

This is the VBA Code

Sub detail_sync()

Dim detaillist As Workbook
Dim bapref As Range
Dim sfdcnum As Range
Dim euname As Range
'Plan New Added
Dim isgpn As Range


With Worksheets("Deatil Info")

     Set bapref = .Range("oi_bapref")
     Set sfdcnum = .Range("oi_sfdcnum")
     Set euname = .Range("oi_euname")
     'Plan New Added
     Set isgpn = .Range ("ISGPN")
        
If bapref.Value = 0 Then

    MsgBox "CAUTION....BAP Number can't empty", , _
    "      !!!!!.....ERROR WARNING.....!!!!!"
        
Exit Sub
    
    End If
 
        End With

                On Error Resume Next
 
                    Set detaillist = Workbooks("DataInfo.xlsx")
 
                On Error GoTo 0
 
If detaillist Is Nothing Then
    
    [B]'I need modify from here by search specific file name that same with (bapref) with extension .xlsx (with same folder below)[/B]
    Set detaillist = Workbooks.Open("hhttps://workme.sharepoint.com/Shared Documents/General/08. formID/02. ID/DataInfo.xlsx")
      
        If Application.WorksheetFunction.XLookup(bapref.Value, detaillist.Worksheets("DetailInfo1").Range("D:D"), detaillist.Worksheets("DetailInfo1").Range("D:D"), "E", 0) = "E" Then
    
            detaillist.Close False
    
                MsgBox "BAP number not found" & vbNewLine & "Please check the BAP number or contact admin for update the database", , "                                        !!!!!.....ERROR WARNING.....!!!!!"
 
Exit Sub
    
        End If
    
             End If
              
                  
                On Error Resume Next
 
                    Set detaillist = Workbooks("DataInfo.xlsx")
 
                On Error GoTo 0
                  
                    If detaillist Is Nothing Then
    
                        Set detaillist = Workbooks.Open("hhttps://workme.sharepoint.com/Shared Documents/General/08. formID/02. ID/DataInfo.xlsx")
                
                    End If
            
                    [B]'Add the optional formula in here[/B]
                    sfdcnum.Value = Application.WorksheetFunction.XLookup(bapref.Value, detaillist.Worksheets("DetailInfo1").Range("D:D"), detaillist.Worksheets("DetailInfo1").Range("N:N"), """", 0)
                    euname.Value = Application.WorksheetFunction.XLookup(bapref.Value, detaillist.Worksheets("DetailInfo1").Range("D:D"), detaillist.Worksheets("DetailInfo1").Range("F:F"), """", 0)
            
                    detaillist.Close False
                

End Sub

The example of the bapref is BAP-123 then the file will open is BAP-123.xlsx then running the same lookup process (including new formula)

I really appreciate for the support

 

[Joe4]

Re-posted scaled down version here: Convert formula into vba macro