Hi All,

I am trying to produce a spreadsheet that will be accessed by remote users,
save itself under a individual's name and then when the remote file is opened
again it searches the individual's computer for a that file, if it is there
then it is opened and the original file is opened and the user continues to
work on it, blissfully unaware that anything has happened. When the user has
finished making all their changes they then email the sheet back to me via a
send button.

Now...I have this working perfectly on my computer but the users computers
are less straightforward. I have a facility to test it on identical sytems
as those that will be used, some of which are NT, some XP and they have
thrown up some problems which I will outline below (This should possibly be
split into more than one post?)

1. Searching for files:

I have used the following code:

Private Sub Search_Saved_Proforma()
Dim Store As String
Dim wkbk As Workbook

Let User = Range("User_Name")
Let Template_File = "Example Template File"

With Application.FileSearch
.NewSearch
'.LookIn = "C:\temp\practice\new practice\"
.SearchSubFolders = False
.Filename = User & ".xls"
' .FileType = msoFileTypeAllFiles
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(.FoundFiles(i))

Workbooks(Template_File & ".xls").Close savechanges:=False

wkbk.Activate

Next i

Else

End If

End With

Template file is the file that the users will open while user is the file
that is saved on their computer. This works fine on NT and my computer which
is XP, but the for users computers with XP this doesn't work, producing a run
time error 1004 (Method File Search of Object).

2.Running a Workbook_Open Event (Not sure if I worded that properly.

I have a macro that runs when the workbook opens. Private Sub
Workbook_Open()

This activates the front sheet, and then brings up userforms to determine
who the user is and then runs the search. In XP, this is fine (except for
the search problem as detailed above). In NT however, the userforms come up
before the sheet has appeared and then the search part of the routine doesn't
run/doesn't appear to run. Any ideas? Code is below.

Private Sub Workbook_Open()

Sheet3.Activate

If Range("User_Name") = "" Then
'If Range("User_Name") = 0 Then

'''If the sheet name is blank ie. if the template has been opened for the
first time
'''then choose a user from userform

Range("STRNUMrange").Value = Environ("STRNUM") 'nb. strnum is an equivalent
of "username"
Confirm_user.show 'confirm that this is the correct user


If Range("user_Name") = "" Then
'If Range("user_Name") = 0 Then

'''If the person cancels out of the select user dialogue then
'''the book will close

MsgBox "value equals 0 or "", would now shut sheet"

'Workbooks("Peak 2007 Pro Forma Practice.xls").Close
savechanges:=False


Else

'''Search for sheets already saved on the c:\ drive providing that
'''a store has been selected
Application.ScreenUpdating = False

Search_Saved_Proforma 'searches for files with the user name on
the user computer

Application.ScreenUpdating = True

Show_Scope_Details 'ignore

End If

Else

'''If user name doesn't equal 0 as in the case of a form that has already been
'''filled in to some extent, then just continue filling it in. This will only
'''apply if the form has been opened from within the search saved proforma vb.

Let user_scope = Range("Scope").Value

End If

End Sub


Sorry for the excessively long message, just need a bit of help finishing
this off and would appreciate any help.