Help - Code not always working

[twperplex]

Have a macro unsed by many people to append information their worksheet to
another worksheet called 'Master.xls'. At the end of the day, the Master.xls
is renamed for batch process. So at the start of each day the the first
person who tries to update the Master.xls creates it.

Been using the below code being used to check if the master.xls worksheet
already exist. Problem is it does not always work. Sometimes the result
for the code '.Execute > 1' will be true, others times result is false, the
Master file exist in both instances. Any suggestion on how to correct?

'Test to see if a Workbook exists
''''''''''''''''''''''''''''''''
Dim i As Integer

With Application.FileSearch
.LookIn = "\\s1racft1\public\PreventativeExpedite"
.Filename = "Master.xls"
If .Execute > 1 Then 'Workbook exists, open the workbook
Workbooks.Open
Filename:="\\server\public\PreventativeExpedite\Master.xls"
Else 'There is not a Workbook, create a new workbook
Set NewBook = Workbooks.Add
With NewBook
.Title = "Master"
.SaveAs
Filename:="\\server\public\PreventativeExpedite\Master.xls"
End With
End If
End With
End Sub


Thank you.

[Jim Thomlinson]

Here is some un-tested code but it might give you some ideas...

Public Function ReturnMaster() as workbook
dim wbkReturn as workbook
on error resume next
set wbkReturn = workbooks("master.xls")
if wbkReturn is nothing then
set wbkReturn = workbooks.open("\\s1racft1" & _
"\public\PreventativeExpedite\master.xls")
end if
if wbkReturn is nothing then
set wbkReturn = workbooks.add
wbkreturn.saveas("\\s1racft1\public\PreventativeExpedite\master.xls")
end if
set ReturnMaster = wbkReturn
end function
--
HTH...

Jim Thomlinson


"twperplex" wrote:

> Have a macro unsed by many people to append information their worksheet to
> another worksheet called 'Master.xls'. At the end of the day, the Master.xls
> is renamed for batch process. So at the start of each day the the first
> person who tries to update the Master.xls creates it.
>
> Been using the below code being used to check if the master.xls worksheet
> already exist. Problem is it does not always work. Sometimes the result
> for the code '.Execute > 1' will be true, others times result is false, the
> Master file exist in both instances. Any suggestion on how to correct?
>
> 'Test to see if a Workbook exists
> ''''''''''''''''''''''''''''''''
> Dim i As Integer
>
> With Application.FileSearch
> .LookIn = "\\s1racft1\public\PreventativeExpedite"
> .Filename = "Master.xls"
> If .Execute > 1 Then 'Workbook exists, open the workbook
> Workbooks.Open
> Filename:="\\server\public\PreventativeExpedite\Master.xls"
> Else 'There is not a Workbook, create a new workbook
> Set NewBook = Workbooks.Add
> With NewBook
> .Title = "Master"
> .SaveAs
> Filename:="\\server\public\PreventativeExpedite\Master.xls"
> End With
> End If
> End With
> End Sub
>
>
> Thank you.