Add closed workbook filenames that meet certain criteria to a message box
 

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.

Add closed workbook filenames that meet certain criteria to a message box
 

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.

Add closed workbook filenames that meet certain criteria to a message box
 

Thanks Jake......I'm definitely a noobie when it comes to VBA. Here's
the code I've been using. Will the below work with this?

Thanks a lot.



*** Sent via Developersdex http://www.developersdex.com ***

Add closed workbook filenames that meet certain criteria to a message box
 

Thanks Jake. Here's the code I've been using. Can your suggestion
below easily be implemented? Thanks.



*** Sent via Developersdex http://www.developersdex.com ***

Add closed workbook filenames that meet certain criteria to a message box
 

Hi Rob,

Code edits inline:

Rob Blatchley wrote:
Thanks Jake. Here's the code I've been using. Can your suggestion
below easily be implemented? Thanks.

Code:

---

Public Sub RunAll()


    'First check to make sure folder has files

    Dim FolderName As String, wbName As String, r As Long, cValue As
Variant, cValue2 As Variant
    Dim wbList() As String, wbCount As Integer, i As Integer, rsVersion
As Variant

---

--
Regards,

Jake Marx
www.longhead.com


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

Add closed workbook filenames that meet certain criteria to a message box
 

Hi Jake,

Thanks again for your reply. I'm having a little trouble. I've
implemented the additions to your code, however, I continue to get an
object required error. I think it has something to do with wb.FullName.
When I remove this, it works fine. Also, if I type set wb =
ActiveWorkbook, the message only shows the name of the workbook I'm
using. How do I make it show all the workbooks that do not meet the
version requirements?

Thanks again.



*** Sent via Developersdex http://www.developersdex.com ***