V-lookup of file names?

[jeff]

I don't know if this is possible, but I thought it would
be worth asking.

Is there a way to do a vlookup of a file name in order to
open the file and then return a specific cell value? If
this sounds possible, I have given a more detailed
description below of what I'd like to do.

I'm filling out a spreadsheet that lists multiple product
characteristics (i.e. cost, size, description, etc.) for
hundreds of products. The information to populate this
spreadsheet is found in other spreadsheets that are
specific to each item. In other words there is a folder
that contains hundreds of Excel files, each containing
information about one product. These files have the same
name as the product ID they contain information about
(i.e. 14344C2B.xls or 23433D3C.xls). All these
spreadsheet have the same data in the same cells (i.e.
Cell C10 is always 'COST'). Is there a way I can do some
kind of vlookup to first look in a specified folder, then
find the correct file to open, and return cell value C10?

 

[Ron de Bruin]

Try this

With in A1 of Sheets("Sheet1") a file name like
14344C2B

It will check if the file exist in the folder C"\Data
If it exist it will open it and copy the cell C10 of Sheet1 to
Sheet1 in the cell A2 in your workbook

Sub test()
Dim str As String
Dim wb1 As Workbook
Dim wb2 As Workbook

Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
str = "C:\Data\" & Sheets("Sheet1").Range("A1").Value & ".xls"
If Dir(str) <> "" Then
Set wb2 = Workbooks.Open(str)
wb1.Sheets("Sheet1").Range("A2").Value = _
wb2.Sheets("Sheet1").Range("C10").Value
wb2.Close False
End If
Application.ScreenUpdating = True
End Sub