Search folder then file for specific cells

**MikeNZ** · 04-09-2010, 04:42 PM

Can excel search through a folder to find a specific workbook then take values from different cells in that workbook and display them in another workbook? If so, how? I'm using excel 2007.

I have a folder named 2010 and folders for each month within. In the monthly folder is a workbook for each day of the month and in each workbook there are three sheets. I need to get info from cells on all three sheets and display them in a totally different workbook when I enter in a specific date.

Hope this makes sense, if not let me know and I'll try to explain further.

**tony h** · 04-09-2010, 05:04 PM

it is reasonably straight forward as a vba macro.

you use the date to format the folder path name eg C:\data\2010\Jan\ and aalso the filename eg day31.xls
Then use the openworkbook method to open the spreadsheet

Then it is a simple matter of copying the data. :
- for large fixed areas a series of copy and paste
- for single values a range=range

If you want more help it is worth being more specific about how your naming conventions work and what cells on what sheets need to be copied to where.

**shg** · 04-09-2010, 05:05 PM

Mike, you need to take a few minutes to read the forum rules and then change your thread title.

**Marcol** · 04-09-2010, 05:47 PM

As an example of tonys' post

Sub OpenReadCloseExcel()
    Dim strPath As String, strFilename As String
    Dim wbMaster As Workbook, wbCopy As Workbook
    
    strPath = "C:\Users\Alistair\Excel Forum\Test\"
    strFilename = "TestBook2.xls"
    Application.Workbooks.Open strPath & strFilename
    
    Application.DisplayAlerts = False
    
    Set wbCopy = Workbooks(strFilename)
    Set wbMaster = Workbooks("MasterBook1")  'The workbook to copy to that usually but not always contains the macro.
    
    ' your code here
    ' For example
    wbCopy.Sheets("Sheet1").Range("A2").Copy wbMaster.Sheets("Sheet1").Range("A2")
    '...............etc

    wbCopy.Close
    
    Set wbCopy = Nothing
    Set wbMaster = Nothing
    
    Application.DisplayAlerts = True
    
End Sub

Application.DisplayAlerts = False

Avoids prompt to save changes to the book you are copying from on closing.

Remember to set back to True when done.

Hope this helps

**MikeNZ** · 04-09-2010, 06:30 PM

Ok. I have a workbook called "Test". I want to be able to enter a date into A1 and for excel to look in C:\data\2010\ to find the workbook corresponding to that date. Then for it to display the values in cells B1:B5 of the dated workbook into cells B1:B5 of the "Test" workbook.

**broro183** · 04-09-2010, 07:31 PM

hi all,

I drafted the below before the thread title was amended &, although it looks like solutions are pretty close now, I thought I would post it anyway as the links may provide some background/alternatives...

**************
Giday Mike,
Yes, you can do this in a macro in Excel.
Are you familar with & able to use macros?
If so, here are a couple of links that may guide you in the right direction (let us know if you have any specific questions & good luck!)...
http://www.erlandsendata.no/english/index.php?d=envbawbreadfromclosedwb
http://www.rondebruin.nl/copy7.htm
http://www.exceltip.com/st/Getting_Values_From_A_Closed_Workbook_using_VBA_in_Microsoft_Excel/357.html
http://www.mrexcel.com/forum/showthread.php?t=444810
All sourced from: http://www.google.co.uk/search?q=%22values+from+a+closed+workbook%22+excel+sub&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-GB:official&client=firefox-a

If not familar with macros, can you please upload an example file with explanation of where you want the values inserted, the input date cell etc?
Also can you please provide all the extra necessary details?
For example: an example of a full file path plus the naming convention of the lookup files, the name of the three sheets, the cells on the sheets, the address of the input date cell & any other details you can think of.

I don't know the reason for having different files & the reason for your question may even be to actually consolidate the files (?), but if you are likely to try analysing from these files, I suggest consolidating them all into a single file. This allows you to analyse more effectively (autofilter, pivot tables, sorting, subtotals etc) using a single (?) sheet in a ""database style layout"" with extra columns to identify the separate days.

hth
Rob

**Marcol** · 04-09-2010, 08:16 PM

Hi Mike

This should do what you asked in Post #5

Sub OpenReadCloseExcel()
    Dim strPath As String, strFilename As String
    Dim wbResult As Workbook, wbCopy As Workbook
    
    Set wbResult = Workbooks("Test")
    
    On Error GoTo ErrHandler
    strPath = "C:\data\2010\"
    strFilename = wbResult.Range("A1") & ".xls"     'This will depend on your file extention .xlsx, .xlsm
    
    Application.Workbooks.Open strPath & strFilename
    
    Application.DisplayAlerts = False
    
    Set wbCopy = Workbooks(strFilename)
    
    wbCopy.Sheets("Sheet1").Range("B1:B5").Copy Destination:=wbResult.Sheets("Sheet1").Range("B1")
    
    wbCopy.Close
    
    Set wbCopy = Nothing
    Set wbResult = Nothing
    
    Application.DisplayAlerts = True

    Exit Sub

ErrHandler:
    Err.Clear
    MsgBox "File:- " & strPath & strFilename & "does not exist."
End Sub

There is the possibility of error if file is not found hence the error handler and msgBox

Hope this helps

**JBeaucaire** · 04-09-2010, 08:36 PM

Just an another thought. Since we're using VBA, perhaps having a macro construct and enter standard formulas into those cells would be less plumbing? You can't INDIRECT() reference closed workbooks, but you can let a macro construct direct formulas from pieces of information and stick those formulas in the cells, then they would read in the data without having to open the files.

**Marcol** · 04-09-2010, 11:23 PM

On JBs' prompting one posslbility could be

Sub ReadFileInFolder()
    Dim i As Integer
    Dim strPath As String, strFilename As String
    Dim strSheetName As String, strReadCell As String
    Dim wbResult As Workbook, shtResult As Worksheet
    
    Set wbResult = Workbooks("Test")
    Set shtResult = wbResult.Sheets("Sheet1")
    
    strPath = "C:\data\2010\"
    strFilename = shtResult.Range("A1") & ".xls"
    strSheetName = "Sheet1"

    ' Check if file exists
    If Dir(strPath & strFilename) = "" Then
        MsgBox "File:- " & strPath & strFilename & "does not exist."
        Exit Sub
    End If
    
    For i = 1 To 5
        strReadCell = "'" & strPath & "[" & strFilename & "]" & strSheetName
        strReadCell = strReadCell & "'!R" & i & "C2"
        shtResult.Range("B" & i) = ExecuteExcel4Macro(strReadCell)
    Next
    
    Set wbResult = Nothing
    Set shtResult = Nothing
End Sub