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
>
>
|
Similar Threads
