reading a cell from multiple files that are not currently open.

[Guest]

I fear that I'm in trouble here. I have hundreds of xls sheets in different directories, however they all used the same template when they were created. So I need to read one cell from all these files, then count the instances of the contents of that cell, then finally graph it all out. well the graph is optional, but would be nice

now how do I go about doing this? I know you can read cells from open files, but how about unopened ones in different directories and completly different file names

I tried writeing a program for it with Qbasic (small buisness ) but the xls format is strange, you change one value and exel changes 75% of the file. I can read text useing this method but my target cell is numbers only. despite my efforts I can't even find where exel stores the numbers.

 

[JE McGimpsey]

You could put your paths and filenames, etc. in a sheet and use John
Walkenbach's GetValue function:

http://j-walk.com/ss/excel/tips/tip82.htm

or my cross-platform modification:

http://mcgimpsey.com/macoffice/excel/getcellvalue.html

 

[Bernie Deitrick]

Ouch,

If you need to read all the files in one folder and its subfolders,
try the macro below.

Copy the code into the module of a clean workbook. Change the path as
needed where shown, and the cell address of the cell you want to
extract. It will read your directory and create links to each file's
cell on "Sheet1". If "Sheet1" doesn't exist, Excel will ask which
sheet to use. Note that this requires Excel 2000 or later due to the
InStrRev, but can be modified to work with earlier versions. The file
path and name will be stored in column A, and the links will be in
column B. If you don't want to keep the links, then copy column B and
paste special values to remove them but leave the values.

HTH,
Bernie
MS Excel MVP

Sub MakeMultipleLinks()
Dim strPath As String
Dim strFName As String
Dim strShtName As String
Dim strCellAddress As String
Dim i As Integer

strShtName = "Sheet1"
strCellAddress = "A1"

With Application.FileSearch
.NewSearch
'Change the folder here
.LookIn = "C:\Excel\"
'Change this to False is you don't want to search subfolders
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
strPath = retPath(.FoundFiles(i))
strFName = retName(.FoundFiles(i))
Cells(i, 2).Formula = _
"='" & strPath & "[" & strFName & "]" & strShtName & "'!" &
strCellAddress
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Function retPath(strFullName As String) As String
retPath = Left(strFullName, InStrRev(strFullName, "\"))
End Function

Function retName(strFullName As String) As String
retName = Mid(strFullName, InStrRev(strFullName, "\") + 1,
Len(strFullName))
End Function

I fear that I'm in trouble here. I have hundreds of xls sheets in

different directories, however they all used the same template when
they were created. So I need to read one cell from all these files,
then count the instances of the contents of that cell, then finally
graph it all out. well the graph is optional, but would be nice.

now how do I go about doing this? I know you can read cells from

open files, but how about unopened ones in different directories and
completly different file names.

I tried writeing a program for it with Qbasic (small buisness )

but the xls format is strange, you change one value and exel changes
75% of the file. I can read text useing this method but my target cell
is numbers only. despite my efforts I can't even find where exel
stores the numbers.