If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
V-lookup of file names?
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? |
#2
|
|||
|
|||
V-lookup of file names?
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 -- Regards Ron de Bruin http://www.rondebruin.nl "jeff" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|