About Us

rdblatch

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.

Jake Marx[_3_]

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.

Rob Blatchley

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?

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 
     
    FolderName = Sheet6.Range("B5") 
     
     
     ' create list of workbooks in foldername
    wbCount = 0 
    wbName = Dir(FolderName & "\" & "*.xls") 
     
    While wbName < "" 
        wbCount = wbCount + 1 
        ReDim Preserve wbList(1 To wbCount) 
        wbList(wbCount) = wbName 
        wbName = Dir 
         
    Wend 
     ' confirm that correct files exist within selected folder
    If wbCount = 0 Then MsgBox Prompt:="No Excel files exist in this
Directory.  Please select a different folder.", Buttons:=vbOKOnly 
    If wbCount = 0 Then Exit Sub 
     
    r = 0 
    For i = 1 To wbCount 
         
         ' confirm that files are using correct version number
         
        rsVersion = GetInfoFromClosedFile(FolderName, wbList(i),
"sheet1", "A2") 
        If rsVersion < "Version 1.0" Then 
            MsgBox Prompt:="One or more of the files in the selected
folders was not created using Version 1.0", Buttons:=vbOKOnly 
            If rsVersion < "Version 1.0" Then Exit Sub 
        End If 
         
    Next i 
     
End Sub 
 
Public Function GetInfoFromClosedFile(ByVal wbPath As String, _ 
    wbName As String, wsName As String, cellRef As String) As Variant 
    Dim arg As String 
    GetInfoFromClosedFile = "" 
    If Right(wbPath, 1) < "\" Then wbPath = wbPath & "\" 
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function 
    arg = "'" & wbPath & "[" & wbName & "]" & _ 
    wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1) 
    On Error Resume Next 
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg) 
     
End Function

Thanks a lot.

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

Rob Blatchley

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 
     
    FolderName = Sheet6.Range("B5") 
     
     
     ' create list of workbooks in foldername
    wbCount = 0 
    wbName = Dir(FolderName & "\" & "*.xls") 
     
    While wbName < "" 
        wbCount = wbCount + 1 
        ReDim Preserve wbList(1 To wbCount) 
        wbList(wbCount) = wbName 
        wbName = Dir 
         
    Wend 
     ' confirm that correct files exist within selected folder
    If wbCount = 0 Then MsgBox Prompt:="No Excel files exist in this
Directory.  Please select a different folder.", Buttons:=vbOKOnly 
    If wbCount = 0 Then Exit Sub 
     
    r = 0 
    For i = 1 To wbCount 
         
         ' confirm that files are using correct version number
         
        rsVersion = GetInfoFromClosedFile(FolderName, wbList(i),
"sheet1", "A2") 
        If rsVersion < "Version 1.0" Then 
            MsgBox Prompt:="One or more of the files in the selected
folders was not created using Version 1.0", Buttons:=vbOKOnly 
            If rsVersion < "Version 1.0" Then Exit Sub 
        End If 
         
    Next i 
     
End Sub 
 
Public Function GetInfoFromClosedFile(ByVal wbPath As String, _ 
    wbName As String, wsName As String, cellRef As String) As Variant 
    Dim arg As String 
    GetInfoFromClosedFile = "" 
    If Right(wbPath, 1) < "\" Then wbPath = wbPath & "\" 
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function 
    arg = "'" & wbPath & "[" & wbName & "]" & _ 
    wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1) 
    On Error Resume Next 
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg) 
     
End Function

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

Jake Marx[_3_]

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

Code:

    Dim sMsg As String

    FolderName = Sheet6.Range("B5")


     ' create list of workbooks in foldername
    wbCount = 0
    wbName = Dir(FolderName & "\" & "*.xls")

    While wbName < ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = wbName
        wbName = Dir

    Wend
     ' confirm that correct files exist within selected folder
    If wbCount = 0 Then MsgBox Prompt:="No Excel files exist in this
 Directory.  Please select a different folder.", Buttons:=vbOKOnly
    If wbCount = 0 Then Exit Sub

    r = 0
    For i = 1 To wbCount

         ' confirm that files are using correct version number

        rsVersion = GetInfoFromClosedFile(FolderName, wbList(i),
 "sheet1", "A2")
        If rsVersion < "Version 1.0" Then

                sMsg = sMsg & vblf & "'" & wb.FullName & "'"

        End If

    Next i

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

 End Sub

 Public Function GetInfoFromClosedFile(ByVal wbPath As String, _
    wbName As String, wsName As String, cellRef As String) As Variant
    Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) < "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & _
    wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)

 End Function

--
Regards,

Jake Marx
www.longhead.com

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

Rob[_27_]

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 ***

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
EXCEL - Meet 2 criteria, then find next case of third criteria	Elaine	Excel Worksheet Functions	3	December 1st 08 10:43 PM
Add text to meet criteria	Keyrookie	Excel Worksheet Functions	3	October 9th 07 10:55 PM
add numbers if they meet criteria...?	Dan B	Excel Worksheet Functions	2	January 31st 07 11:47 PM
Counting Cells that meet criteria across entire workbook	ExcelMonkey[_177_]	Excel Programming	1	October 11th 04 06:37 PM
VLookup error message while accessing range in closed workbook.	Peter McNaughton	Excel Programming	1	September 10th 03 06:11 AM

Menu

About Us