copy value from unopened workbook

[formularye]

Hi,

I am new to this but I haven't found a thread to match what I am trying to do.

For work, I have several excel files: "(Date) Time and Expense.xls
I am constantly adding new files to this folder: C:\Expense Reports.

What I am trying to do is copy the same cells (E5:K5) from each old and new files to a summary excel workbook. I want to be able to enter in the previous mentioned (Date) in a cell or text box, possibly click a created button and gather the information from that excel file without having to open each file individually. I know Indirect function requires to open each file to gather the information.
Ive done programming in the past, but I am new to VBA.

Thanks in advance,
Ryan

 

[Smitty]

Welcome to the Board!

You can use INDIRECT.EXT, which is part of the MoreFunc add in, or check out Ole Erlandsen's ADO/DAO page for referencing closed workbooks, although that's a VBA approach, but he lays it out pretty well.

HTH,

 

[formularye]

The cell I have and am using has the following:
=INDIRECT.EXT("'C:\Expense Reports\["&$B11&" Time & Expense.xls]Expense Report'!E5")

Where B11 is the changing date of each file. This cell is a text format.
When I added the .ext, instead of the #REF! error when the file is closed i get #NAME? error even when the file is open.

 

[formularye]

Ah Ha, Thanks. Makes more sense now that the function shows up on the list when I type it in. I couldn't find where to download at that website you had, but I searched for it and found it. Thanks Again.