I am trying to merge several excel workbooks but before running my code
I want to check if all required workbooks exist in a directory.

The master document consists of 3 sheets. Each sheet has employee names
in the first row, starting at column D. The number of employees can
vary (the last employee will be the one just before the column with the
name "TOTAL". Each name followed by .xls is a workbook that should be
present in the directory.

The code checks if these files exist in the directory. If they all
exist and there are no other files present, the code will execute; if
they are not all present or other files exist, code should stop
running, displaying a message that xxxx is missing or an unknown xxxx
files has been found in the directory.

Thanks in advance.

Mark

Mark,

I THINK I understand what you're after. I'm assuming that all your data is
in row 1, and is laid out like the following, starting in column D:

User1 File1 User2 File2 User3 Files3 TOTAL NNN

In the example data above, the values are across columns D:K

Run the following code:

Sub AAA()

Dim FilesInDir As New Collection
Dim FilesOnSheet As New Collection

Const C_FOLDER = "C:\Temp" '<<< CHANGE

Dim Rng As Range
Dim LastCol As Long
Dim WS As Worksheet
Dim Ndx As Long
Dim SaveDir As String
Dim FName As String
Dim S As String


SaveDir = CurDir
ChDrive C_FOLDER
ChDir C_FOLDER

FName = Dir("*.xls", vbNormal + vbSystem + vbHidden)

''''''''''''''''''''''''''''''''''''''''
' Loop through each worksheet.
''''''''''''''''''''''''''''''''''''''''
For Each WS In ActiveWorkbook.Worksheets
'''''''''''''''''''''''''''''''''''''
' Reset the collections.
'''''''''''''''''''''''''''''''''''''
Set FilesOnSheet = Nothing
Set FilesInDir = Nothing

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Load up all the file names in the folder
' C_FOLDER into the FilesInDir collection.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
Do Until FName = vbNullString
FilesInDir.Add FName, CurDir & "\" & FName
FName = Dir()
Loop

''''''''''''''''''''''''''''''''''''''''
' Find the last cell in row 1
''''''''''''''''''''''''''''''''''''''''
LastCol = WS.Cells(1, Columns.Count).End(xlToLeft).Column
If LastCol 1 Then
'''''''''''''''''''''''''''''''''''''''''''''
' If LastCol 1, there is data in row 1.
' Loop through the cells in row 1, starting
' at LastCol - 2, right-to-left, stepping
' in incrments of -2. This loop will
' get each filename on row 1.
'''''''''''''''''''''''''''''''''''''''''''''
For Ndx = LastCol - 2 To 4 Step -2
''''''''''''''''''''''''''''''''''''''''''''''
' Loop through row 1, looking at the files.
''''''''''''''''''''''''''''''''''''''''''''''
Err.Clear
''''''''''''''''''''''''''''''''''
' Get the filename from the sheet.
''''''''''''''''''''''''''''''''''
FName = WS.Cells(1, Ndx)
'''''''''''''''''''''''''''''''''
' Add FName to the FilesOnSheet
' collection.
'''''''''''''''''''''''''''''''''
FilesOnSheet.Add FName, FName
If Err.Number Then
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
' This condition indicates that FName appears more than
' once in row 1.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
MsgBox "The filename: '" & FName & "' is already in use."
Exit Sub
End If
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''
' Remove FName from FilesInDir. If all is right with the world
' this collection will be emptied out by the end of the loop.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''
On Error Resume Next
Err.Clear
FilesInDir.Remove FName
If Err.Number Then
MsgBox "There is a file on the sheet that is not in" &
vbCrLf & _
"the folder: " & C_FOLDER
Exit Sub
End If


Next Ndx

If FilesInDir.Count = 0 Then
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''
' If FilesInDir.Count = 0, we have deleted from the
' FilesInDir collection all the files that are in
' Row 1. This means that every user in row 1
' has his own assigned file name, and that there
' are no files in C_FOLDER that were not named
' in rows 1. This condition indicates success.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''

''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''
' YOUR CODE HERE
MsgBox "Your Code Here"
''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''
Else
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
' If FilesInDir.Count = 0, there were files in the folder
' C_FOLDER that were not used on the worksheet. List these.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
For Ndx = 1 To FilesInDir.Count
S = S & FilesInDir(Ndx) & vbCrLf
Next Ndx
MsgBox "The following files are in the directory" & vbCrLf & _
"but not used on the sheet." & vbCrLf & S
Exit Sub
End If
End If
Next WS

ChDrive SaveDir
ChDir SaveDir

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Ixtreme" wrote in message
ups.com...
I am trying to merge several excel workbooks but before running my code
I want to check if all required workbooks exist in a directory.

The master document consists of 3 sheets. Each sheet has employee names
in the first row, starting at column D. The number of employees can
vary (the last employee will be the one just before the column with the
name "TOTAL". Each name followed by .xls is a workbook that should be
present in the directory.

The code checks if these files exist in the directory. If they all
exist and there are no other files present, the code will execute; if
they are not all present or other files exist, code should stop
running, displaying a message that xxxx is missing or an unknown xxxx
files has been found in the directory.

Thanks in advance.

Mark

Something along the lines of this untested pseudo code:

Dim list() as String
Dim sPath as string, sName as String
Redim list(1 to 1)
Dim bFound as Boolean, i as Long, j as Long
Dim sMsg as String, bMissing as Boolean
Dim bAdditional as Boolean, sh as Object
bMissing = False
sMsg = "Missing: " & vbCrLF
sPath = "C:\Temp\"
sname = dir(sPath & "*.xls")
do while sName < ""
list(ubound(list)) = lcase(sName)
Redim Preserve list(1 to Ubound(list) + 1)
sName = dir
Loop
redim preserved List(1 to Ubound(list)-1)
for each sh in Workbooks("Master.xls").Worksheets
i = 4
do while instr(1,sh.cells(1,i),"total",vbTextcompare) = 0
bFound = False
for j = 1 to ubound(list)
if lcase(sh.cells(1,j).Value) & ".xls" = list(j) then
bFound = True
list(j) = ""
exit for
end if
Next j
Loop
if not bFound then
smsg = sMsg & sh.cells(1,i) & vbCrLf
bMissing = True
end if
i = i + 1
Next sh
sMsg1 = "Other files: " & vbCrLf
for j = 1 to Ubound(list)
if len(trim(list)) 0 then
sMsg1 = sMsg1 & List(j) & vbCrLf
bAdditional = True
end if
Next
if bAddtional then
sMsg = sMsg & vbCr & sMsg1
end if
if bAdditional or bMissing then
msgbox sMsg
exit sub
End if

--
Regards,
Tom Ogilvy


"Ixtreme" wrote:

I am trying to merge several excel workbooks but before running my code
I want to check if all required workbooks exist in a directory.

The master document consists of 3 sheets. Each sheet has employee names
in the first row, starting at column D. The number of employees can
vary (the last employee will be the one just before the column with the
name "TOTAL". Each name followed by .xls is a workbook that should be
present in the directory.

The code checks if these files exist in the directory. If they all
exist and there are no other files present, the code will execute; if
they are not all present or other files exist, code should stop
running, displaying a message that xxxx is missing or an unknown xxxx
files has been found in the directory.

Thanks in advance.

Mark

Many thanks for your reply. Somehow I still can't get it to work.

Master contains 3 sheets
Each sheet has row 1 like:
Col_A Col_B Col_ C Col_D Col_E Col_X COL_Z
bla bla bla ABC DEF GHI TOTAL

ABC, DEF etc are employee names.
The number of employees can vary (so on sheet 1 I have 3 employees,
while sheet 2 has 7 employees and sheet 3 has 2 employees.

While running my code, I want to make sure that 3 + 7 + 2 = 12 sheets
are present in the specified directory. The names of the sheets must be
the same eg ABC.xls, DEF.xls etc.

Tom,

this one did the trick!

Many, many thanks again.

Mark