Application.ExecuteExcel4Macro Searching Closed Workbook, Return Row num

Question

Problem: I need to search a closed workbook for a specific value and either return the row number of the matching string or more preferably return a value with the row number of the matched string.

I am trying to get this to work with the ExecuteExcel4Macro function, my code can be found below.

Private Function GetInfoFromClosedFile2(ByVal wbPath As String, ByVal wbName As String, ByVal wsName As String, ByVal colNum As Integer, ByVal searchString As String) As Variant
Dim arg As String
    GetInfoFromClosedFile2 = vbNullString
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & wbName) = vbNullString Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Worksheets(wsName).Columns(colNum).Find(What:=searchString).Address(True, True, xlR1C1)
    'On Error Resume Next
    GetInfoFromClosedFile2 = ExecuteExcel4Macro(arg)
End Function

I get a runtime error '9' on the arg = line

Answer 1

I believe what you are asking for can actually be done without VBA. You can write an Index(Match) function in the formula bar that will search closed workbooks, as long as the workbook is referenced properly.

You can achieve this by having both workbooks open at once when you write the formula, then close the workbook that you are referencing to. Excel should retain the file location in your formula, at that point it becomes a data connection.