Thread: Find data in a closed workbook

Hi all,

Would it be possible to find data from 2 cells in multiple closed excel workbooks in a specified folder.

The cells are the same in all the spreadsheets (J17 & L40), but they all have different file names.

JonesZoid

Hi,

To retrieve data from closed files, you need to look into the PULL() function from Harlan Grove, or download the add-in designed by Laurent Longre called morefunc ...

HTH

Adding to NBVC's comment:

Example: ='[IT Logs.xls]Requests'!$J$17

Syntax: ='[Filename]Worksheet'!Range

I currently have a folder where all the workbooks are held. D:/Surveys
This folder holds all of the UK's information on a weekly basis.

So - i need to extract 2 cells of information (Site Name and ID) so i can accertain which workbooks are the ones i require for MY Region via a vlookup. If i can delete workbooks that dont match my vlookup as well, that would be a big bonus.

Hope this clears up - exactly what my requirements are.

JonesZoid

OK - Basically, all of the closed workbooks, all with different names hold alot of information - i only require info from 2 cells, say for ease: A1 - Site Name and A2 - Site ID.

In my open workbook - i have a vlookup that checks the Site ID to see if it is in my region. (This only works within this workbook, at the moment)

Can this vlookup be incorporated into the pull or indirect functions to tell me which of the closed workbooks are for my region. This output can be put onto a new sheet if required.

JonesZoid

Hi All - The closed workbooks can range from 20 to 200 or more depending on the work load.

My vlookup currently looks at the Site ID and matches it with the sites in my region - but i only perform this currently on a one to one workbook report. But this report is no longer being used - hence all the workbooks....

This is my current vlookup -

Code:

=VLOOKUP(E6,[Workload.xls]Mids!$A:$N,14,FALSE)

All it does is check to see if the Site ID is in my region...

JonesZoid

HI
Save the codes in a workbook inside the folder and run the macro.

Code:

Sub zonesjoid()
Dim e As Long
Dim f As String, b As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(3, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
    Do While Len(f) > 0
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
    For e = 3 To Range("A65536").End(xlUp).Row
        If Cells(e, 1) <> ActiveWorkbook.Name Then
            Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!J17"
            Cells(e, 2) = Cells(1, 3)
            Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!L40"
            Cells(e, 3) = Cells(1, 3)
        End If
    Next e
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "collating is complete."
End Sub

this code lists all files in the folder and pulls J17 & L40 of sheet1 from closed workbook.
ravi