As a suggestion you could try linking to the workbook(s) using Formula.
Something like following may give you something to work with:

Sub GetData()
   Dim ViewData As String
   'data location & range to view
   ViewData = "='C:\[WhatBook.xls]Sheet1'!$B$2:F12"  '<< change as required

   'link to worksheet
   'change sheet name & range as required
    Worksheets("MySheet").Range("B2:F12").Formula = ViewData

End Sub

Above should work even if target workbook is closed.

Hope helpful