EXCEL-MACRO link between 2 documents

Hello,
I am working on a table(in document A) which needs to be referred to another document(document B, not sheet 2 of document A). I used macro to link the data and it works perfectly well when both documents are opened. However, once I closed document B, the data on the table cannot be shown with a message " subscript out of range".
What can I do to avoid this so that I only have the open the main sheet as to get all my data on the table?
Thanks.