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