Hi rdblatch,

It's hard to say how to integrate this with your code since you didn't
provide it. But basically, you'll just have to store the filenames to a
variable as you go through your loop. Here's some code/pseudocode that may
help:

Dim wb As Excel.Workbook
Dim lRow As Long

lRow = 1
'for loop to iterate over files in dir
Set wb = Workbooks.Open(<filename)

If wb.Sheets(1).Cells(1,1) = "Version 1.0" Then
ThisWorkbook.Worksheets(1).Cells(lRow, 2).Value = _
wb.Sheets(1).Range("B13").Value
Else
sMsg = sMsg & vblf & "'" & wb.FullName & "'"
End If

Set wb = Nothing
'next

If Len(sMsg) Then
MsgBox "One or more of files in directory XYZ are from " & _
"a version other than Version 1.0. These files a" & vblf & _
sMsg
End If

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

rdblatch wrote:
Hi All,

I've created a tool in an excel workbook where a user can select a
directory and the code will then input the value of a specific cell
from each of the closed workbooks in that directory. For example,
user selects the directory c:\My Documents. The tool will look in
c:\My Documents for all excel workbooks and take the value of cell
"B13" and input it to cell "B1" first, then "B2" second, etc. in the
tool's worksheet #1.

My question: I want to add some criteria to this. Currently, the code
will show a message box to the user if any of the closed workbooks do
not say "Version 1.0" in cell "A1". However, there may be hundreds of
files in a single directory. Therefore, I want to be able to tell the
user which of these files do not have "Version 1.0" in cell "A1". I'd
like this information to given in the message box.

Example of necessary message box:

"One or more of files in directory XYZ are from a version other than
Version 1.0. These files a ABC.xls, 123.xls, 789.xls"

Is this possible?

Thanks a lot.