Need help converting the MS Visual Basic to something will work in EXCEL 365

[SteveyDan]

Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "C:\DefectMgmt"
.Filename = prod_rel & "_*_" & curdate & ".xls"
If .Execute = 0 Then
MsgBox "Could not find input file - ABORT"
Exit Sub
End If
End With

 

[rlv01]

Something like this might work (not tested)

    Dim sPath As String, fname As String
    
    sPath = "C:\DefectMgmt\"
    fname = sPath & prod_rel & "_*_" & curdate & ".xls"
    
    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(sPath) Then
            MsgBox "Could not find file folder '" & sPath & "'  - ABORT", vbCritical
            Exit Sub
        End If
        
        If Not .FileExists(fname) Then
            MsgBox "Could not find input file '" & fname & "'  - ABORT", vbCritical
            Exit Sub
        End If
    End With

 

[Scott Huish]

If Dir("C:\DefectMgmt\" & prod_rel & "_*_" & curdate & ".xls") = "" Then MsgBox "Could not find file - ABORT": Exit Sub

 

[SteveyDan]

If Dir("C:\DefectMgmt\" & prod_rel & "_*_" & curdate & ".xls") = "" Then MsgBox "Could not find file - ABORT": Exit Sub

Thank you, very much this code worked.

 

[SteveyDan]

Something like this might work (not tested)

    Dim sPath As String, fname As String
   
    sPath = "C:\DefectMgmt\"
    fname = sPath & prod_rel & "_*_" & curdate & ".xls"
   
    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(sPath) Then
            MsgBox "Could not find file folder '" & sPath & "'  - ABORT", vbCritical
            Exit Sub
        End If
       
        If Not .FileExists(fname) Then
            MsgBox "Could not find input file '" & fname & "'  - ABORT", vbCritical
            Exit Sub
        End If
    End With

Thanks, I tested this out and the file existed but was not being read in to the sheet. I appreciate the effort.

 

[rlv01]

but was not being read in to the sheet

If you want to open the file, just add Workbooks.Open Filename:=fname to the end of the code I posted.

 

[smozgur]

Thank you, very much this code worked.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.