How to reference external workbooks using only a string

Hi,

I need to have cell “A1” in my current worksheet linking to a particular cell in an external workbook, called for example Sample_workbook1.xlsx.


Cell “B1” contains the location of this cell in string format. Eg ‘x:\Main_Folder\File_Storage\[Sample_workbook1.xlsx]Sheet1’!A1



Suppose [Sample_workbook1.xlsx]Sheet1’!A1 contains the figure €20,000,

I want to let cell “A1” in my current worksheet to equal the value in [Sample_workbook1.xlsx]Sheet1’!A1, i.e. €20,000

If I use the formula =b1, inside cell A1 it obviously just returns the location in string form and not 20,000.

Also,
I can’t use the Indirect formula like such because it doesn’t work for external folders


=Indirect(“‘x:\Main_Folder\File_Storage\[Sample_workbook1.xlsx]Sheet1’!A1”,1)


Does anyone know of a way in VBA to convert strings to formulae? (I tried using Evaluate but it only appears to work for numerical calculations)

Alternatively Is there any possible work-arounds?

I'd be greatly appreciative of any assistance,

Thanks very much,
Eoghan

Originally Posted by oeldere

I believe that is incorrect. Indirect will not work from closed workbooks, so maybe you have to open that workbook first.

Thanks for your reply, given the tasks I'll be utilising it for it will be impractical to have the referenced workbooks open, so with that in mind, do you know of any work around for referencing a closed workbook? Thanks very much

ok thanks all the same