Excel - External file linking without opening file

[tsoccer63]

Is it possible?

I have 160 files with different names in a folder "Results" that I am trying to summarize with graphs etc...

I am using a pivot-table with certain parameters that outputs the names of the files I want to look at. I want to be able to take this outputed names and use them to create a dynamic table of data.

Ex: I input my parameter values and the file name 1000B pops out. I want to reference the value of B5 in that file without opening the file, and with the knowledge that it is in the "Results" folder one step below the current folder I am in. How do i do that?

I'm trying =INDIRECT("\Results\[1000B.xls]Sheet1!$B$5")
but it doesn't like that. My biggest concern is that there is no way of specifying the file without the entire PATH NAME. If someone could tell me how to do that I could solve the rest of the problem myself.

 

[SkipVought]

Hi,

I do this often using Data>Get External Data...

Once you connect to one workbook and query a sheet(s), you can turn on your macro recorder and record editing your query. The generated code can then be manipulated in a loop, concatenating the workbook name in the Connect string.

IIf you decide to go this route, please post your generated code and questions in VBA Visual Basic for Applications (Microsoft) forum707.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}