0

I want to create an external reference to a closed workbook from a text string. This question is different from "Referencing value in a closed Excel workbook using INDIRECT?" as I need to generate a text string first. The purpose is that I can write a file name in a cell and get the content from a range of cells from a certain sheet of that workbook. I added the Eval function in VBA. It works fine while the workbook is open, also with the entire file path, but it fails as soon as I close it. The same happens using the INDEX or INDIRECT function. This is only when I use a text string, using the external reference or INDEX directly it works. What am I missing?

When workbook is open: =[Book1.xlsx]Sheet1!$A$1 works; [Book1.xlsx]Sheet1!$A$1together with=Eval(C3) works; generating a text string from two cells containing [Book1.xlsx] Sheet1!$A$1 by =A5&B5 followed by =Eval(C5) works as well.

When workbook is closed: ='E:\Excel\[Book1.xlsx]Sheet1'!$A$1 works but E:\Excel\[Book1.xlsx]Sheet1'!$A$1 with =Eval("'"&I3) or =INDIRECT("'"&I3)fails. Then I open Book1 and voila! it works again, so the file path is correct, right? Is there a specific character I am missing?

Open workbook Closed workbook

Ilja Kusters
  • 1
  • 1
  • "*What am I missing?*" You can't do this with INDIRECT or INDEX. As the linked thread says, "there is definitively no way to do this with standard formulas." Reconsider your approach. – BigBen Sep 08 '21 at 19:03
  • OK, got it, thanks. Any links to non-standard approaches? I can't find any hint that anyone solved this issue by any means. – Ilja Kusters Sep 09 '21 at 14:19
  • I've never tried [this approach](https://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook). Maybe worth researching or trying. It does rely on quite old Excel functionality. – BigBen Sep 09 '21 at 14:25

0 Answers0