In excel, which function is similar to INDIRECT?

Question

I have a large number of excel files that I need to use for getting data. I am looking for a function similar to INDIRECT, in order not to be necessary to have the files open to get the values. Can you help?

Thank you

Answer 1

Consider using ADO..............here is a reference:

Microsoft ADO Reference

Answer 2

In your VBA, you need to first open the workbook: Workbooks.Open() Then you can use Workbooks("xxx").Worksheets("").Cells() or Range() [depending on what you want to do]. When you are done taking out the info/data, then you close that worksheet.

Answer 3

You don't really need =INDIRECT() or ADO or even VBA. Just reference the closed worksheet like this in a cell: ='S:\Temp\[T.xls]Sheet1'!$C$4

It will update the values upon opening the workbook; you don't need to have the source workbook open at all. Or you can update them manually by choosing Edit Links from the Data tab in Excel.