Hello monhw,
This version of your macro should do the job. Since the macro is being run from an Excel workbook, there is no reason to create a separate instance of Excel. You can open the other workbook in the current instance.
Sub GetData1()
Dim Data As Range
Dim DstRng As Range
Dim ID As Variant
Dim SrcRng As Range
Dim SrcWkb As Workbook
Set SrcWkb = Excel.Workbooks.Open("C:\Users\e156476\Documents\Data\System Queries\ExcelForm_data.xlsm")
Set SrcRng = SrcWkb.Worksheets("Data").Range("A1").CurrentRegion
Set DstRng = ThisWorkbook.Worksheets("Result").Range("B3:B9")
ID = ThisWorkbook.Worksheets("Result").Range("B1").Value
Set Data = SrcRng.Columns(1).Cells.Find(ID, , xlValues, xlWhole, xlByRows, xlNext, False)
If Not Data Is Nothing Then
DstRng.Value = Application.Transpose(Data.Resize(1, 7))
End If
End Sub
Bookmarks