Call Macro from Workbook Variable

[samshut]

I have made a loop to open files in a directory but cannot figure out how to run the macro using the workbook variable I have created for the newly opened workbook. In the App.Run line it gives error because it can't find "DataWkbk.xls" but DataWkbk is workbook variable i've defined as the one I just opened not the actual filename. Maybe it should be something like DataWkbk"!Macro". Dunno. Thanks for the help.

Sub GetDataFromFiles()

Dim i As Integer
Dim Wkbk As Workbook
Dim DataWkbk As Workbook

Set Wkbk = ThisWorkbook

With Application.FileSearch
    .NewSearch
    .LookIn = "H:\temp\TEST FOLDER\"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
        For i = 1 To .FoundFiles.Count
            Set DataWkbk = Workbooks.Open(.FoundFiles(i))
            Application.Run "DataWkbk!FindToolInfo"
            ActiveWorkbook.Close savechanges:=False
        Next i
    End If
End With
End Sub

[Leith Ross]

Hello Samshut,

You made a typo. You meant to use the Data workbook's name as a string, not "DataWkBk!".

Sub GetDataFromFiles()

Dim i As Integer
Dim Wkbk As Workbook
Dim DataWkbk As Workbook

Set Wkbk = ThisWorkbook

With Application.FileSearch
    .NewSearch
    .LookIn = "H:\temp\TEST FOLDER\"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
        For i = 1 To .FoundFiles.Count
            Set DataWkbk = Workbooks.Open(.FoundFiles(i))
            Application.Run DataWkbk.Name & "!FindToolInfo"
            ActiveWorkbook.Close savechanges:=False
        Next i
    End If
End With
End Sub

Sincerely,
Leith Ross