Problems with file to be used on different systems.

[Joseph Fletcher]

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.

 

[NickHK]

Joseph,
Do you mean that the file will be in that folder ("C:\temp\practice\new
practice\") or that you may need to search all drives to find the file ?
Given the size of modern disks (100GB+) this may be a very time consuming
process.

It would be easier to ask the user if they have saved the file before and if
so, provide a dialog for them to select it. e.g.
Application.GetOpenFilename

NickHK

 

[Joseph Fletcher]

The file will only be in the folder C:\temp\practice\new\practice\ , the
excel sheet has a save function which creates the folder and saves it there
so no need to search the whole computer.

I'm also assuming, with good reason, that I will be dealing with users who
are unable to successfully find and open a file for themselves so i'm trying
to simplify it and remove human error.

Cheers Nick

 

[Dave Peterson]

#1. I couldn't reproduce the 1004 error. But wouldn't you want to give the
code an initial .lookin value. You may not be where you want to be.

And lots of people have written about how application.filesearch seems flakey.
In fact, MS dropped it in xl2007.

Ron de Bruin has converted many (all?) of his sample code to use FSO instead of
..filesearch.
http://www.rondebruin.nl/fso.htm

#2. I only use XP, so I can't even try it.

 

[NickHK]

Joseph,
If you know the path and can build the filename, just try to open it with
error trapping:
dim WB as workbook

User = Range("User_Name")
on error resume next
set wb=workbooks.open("C:\temp\practice\new practice\" &
Range("User_Name").value & ".xls")
on error goto 0
if wb is nothing then
'Not found
else
'Yes, found it
end if

NickHK